Results 1 to 9 of 9

Thread: Subform problem

  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Subform problem

    I wonder if anyone here can help me. Sorry it's a bit of a long post, but I've tried a lot already!

    I am struggling to bind a control on a subform to the
    result of a query based on the values in that subform.

    The underlying table has the following fields:

    Household Number
    Index in Household
    Name
    village
    Hamlet

    The subform displays a list of individuals from the table where the name matches that entered on the form. I want to add a control which displays the name of the head of household for EACH of the matching records to aid identification of the correct individual. The head of
    household has Index in Household=1 and obviously the same Household Number, Hamlet and Village. I cannot seem to get this to work.

    I have tried creating a new query (HoH) based on the underlying table (Household Data) limited by Index in Household=1, and adding this query to the source query of the subform, linking on village, hamlet and household and reading the Name of the head of household from HoH, but although this initially seems permissible, when I open the form I am told that "The specified field Name could refer to more than one table listed in the from clause of your SQL statement". I confirmed that the SQL statement had explicit references to the tables for these fields and it did.

    The alternative is to add a Head of Household field to the underlying table. As there are over 32,000 records, I cannot do this manually, and my efforts at designing an update query to do this have failed.

    I can't believe that this is difficult!

    Thanks

    Mark

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Subform problem

    Why don't you post the SQL for the query. The error you describe suggests that somewhere in your query you referenced the field name without specifying the table and the field exists in more than one table in the query. I've never seen it where all fields had specific table references.

    It sounds like what you need is a subquery, so the whole thing would look something like this:

    SELECT [Houshold Data].*, (SELECT H.Name FROM [Household Data] As H WHERE H.[Household Number]=[Household Data].[Household Number] And H.[Index in Household]=1) As HOH FROM [Household Data]

    Just as a general observation, I'd suggest you get rid of the spaces in your table and field names. Access allows them, but it isn't a good idea and forces you to use brackets around the names in queries, etc. You can always use spaces in captions for the fields for display purposes. The actual names are really only there for programming.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Subform problem

    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

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Subform problem

    You may run into future problems because Name is a reserved word in Access/DAO/ADO. I would try changing the field name in each table to something meaningful like PersonName or HOHName.

    Your problem here is that you're including the Name field from both tables. If you alias one of them in the SQL, that should solve the problem. For example, instead of "[HoH List].[Name]", make it "[HoH List].[Name] As HOHName", and that will keep the SQL from confusing the two fields.
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Subform problem

    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

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Subform problem

    In terms of database design, Iwould suggest thinking about normalising the database. I would have a property number (which could be an autonumber) identifying each property and used this as a link between a property table with the location details and the resident table with the PersonName and IndexInHousehold fields. Such a database would be much easier to manage, smaller, less likely to die on you, take up less disk space, and run more quickly.
    David Grugeon
    Brisbane Australia

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Subform problem

    1) When you alias a field in a query, you refer to the field by its alias. If you base a form on such a query, you'll see the alias name in the field list.

    2) That is the way you would alias it. Without the exact error you're getting, I can't really explain it. Is HOH a table that holds the name of the head of household? Does it have a Name field or a HOHName field? If the latter (because you've changed the field name), then use the new field name without any alias.

    As Grugeon suggests, you would have less trouble if you built a more relational model. A Household table that included keys for Village and Hamlet would have its own unique identifier, a HouseholdID. Then if you had a HouseholdMembers table, it would contain the householdID and name in a separate record for each member of the household, which would include their gender and possibly their age (birthdate would be a better choice, because their age changes every year, right?). You could have a separate HeadOfHousehold table that contained the HouseholdMember ID for the person who is head of household as well as the HouseholdID. Your tables would all join on the HouseholdID. You wouldn't need a name field in the HeadOfHousehold table, because that person would also be in the HouseholdMembers table.
    Charlotte

  8. #8
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Subform problem

    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

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Subform problem

    Glad you got it to work.

    Your question about SQL as a recordsource and SQL in VBA is answered simply. In VBA, when you build a SQL string to be executed, you usually build it in chunks instead of one huge line that is hard to follow.

    Since the SQL is built as a string and then executed in some fashion, it has to be in quotes. The underscores are line continuation characters, and are actually a space, and underscore, and a return. They tell the VBA engine that you're continuing a line. Ampersands are concatenation characters. They tell the enginer to join strings, and you will always have one on the next line after an underscore unless you have one just before the underscore.

    I can't really help you with the on-line tutorial, although I would suggest an internet search on CBT and Microsoft Access. That turned up plenty of hits for me.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •