Replies

Thank you for your suggestions. I will try and work out how to redistribute my data as you describe. My difficulty was that I have inherited the data already entered into 3 excel spreadsheets (one for each village) with columns for Household, Index in household, Name, age(male), age(female), hamlet, as this is the format from the village health workers exercise books. I imported this data into access, after considerable cleaning (although there remain many mistakes, unfortunately) which I found easier to do in excel (autofiltering being much quicker than querying). There will be many corrections and additions to be made to this data as well, but I will try and do this through a form with windows on all 3 tables you describe.

Ah – just got it! I had to alias [Household Data].Name as well as [HoH List].Name, so the SQL statement reads:

SELECT [Household Data].[ID], [Household Data].[No], [Household Data].[Household], [Household Data].[Index in House], [Household Data].[Name] AS IndividualName, [Household Data].[Age (Male)], [Household Data].[Age (Female)], [Household Data].[Sex], [Household Data].[Age], [Household Data].[Group], [Household Data].[Thon], [Household Data].[Hamlet Code], [Household Data].[Village Code], [Household Data].[Correct], [HoH List].[Name] AS HoHName FROM [Household Data] INNER JOIN [HoH List] ON ([Household Data].[Village Code]=[HoH List].[Village Code]) AND ([Household Data].[Hamlet Code]=[HoH List].[Hamlet Code]) AND ([Household Data].[Household]=[HoH List].[Household]);

Thank you.

I would still like to find out where I can get an Access SQL reference guide. I have found several online tutorials, which I am following, but the details of the syntax are all slightly different. I also don’t understand why the record source SQL statement appears as above, but to use a SQL statement in VBA (eg to run an update query), lots of ampersands, underscores and quotation marks are involved. Any info gladly received (and might stop me pestering you busy people).

Cheers

Thank you very much Charlotte, I shall try this.

I have already run into problems with the name Name, but not as a field name, but as a control name, so I have alreay decided not to use it in future.

I have 2 more small questions:

1) If I alias HoHName, how do I then refer to it to display in a control?

2) I am afraid I am a trial and error/Help/newsgroup parasite self taught newbie, and have no idea of SQL syntax (i am also working in a bit of vacuum – techie books are difficult to get hold of here, and the ones that are available are rarely in english). I would be very happy to be pointed in the direction of some references for this, but to address this particular problem, which I would like to to by tomorrow , what should the syntax be for aliasing the HOH.Name. I have tried just adding AS HoHname after HoH.Name, as in:

SELECT [Household Data].ID, [Household Data].[No], [Household Data].Household, [Household Data].[Index in House], [Household Data].Name, [Household Data].[Age (Male)], [Household Data].[Age (Female)], [Household Data].Sex, [Household Data].Age, [Household Data].Group, [Household Data].Thon, [Household Data].[Hamlet Code], [Household Data].[Village Code], [HoH].[Name] AS HoHName
FROM [Household Data] INNER JOIN HoH ON ([Household Data].[Village Code] = HoH.[Village Code]) AND ([Household Data].[Hamlet Code] = HoH.[Hamlet Code]) AND ([Household Data].Household = HoH.Household);

and also tried putting brackets round it, but neither works (the former still gave me the same error, the latter a syntax error on leaving the SQL window).

Sorry to ask such small questions.

Thanks

Sean

Sorry here’s the SQL statement (as it appears in the Record Source property of the subform):

SELECT [Household Data].[ID], [Household Data].[No], [Household Data].[Household], [Household Data].[Index in House], [Household Data].[Name], [Household Data].[Age (Male)], [Household Data].[Age (Female)], [Household Data].[Sex], [Household Data].[Age], [Household Data].[Group], [Household Data].[Thon], [Household Data].[Hamlet Code], [Household Data].[Village Code], [Household Data].[Correct], [HoH List].[Name] FROM [Household Data] INNER JOIN [HoH List] ON ([Household Data].[Village Code]=[HoH List].[Village Code]) AND ([Household Data].[Hamlet Code]=[HoH List].[Hamlet Code]) AND ([Household Data].[Household]=[HoH List].[Household]);

I thought I’d found the problem when I realised that I hadn’t changed the child link field from Name to {Household Data].Name, but unfortunately after doing so I now receive the error “The specified field'[Household Data].Name’ could refer to more than one table listed in the from clause of your SQL statement. Now I really don’t understand.

Thanks again