Results 1 to 8 of 8
  1. #1
    schulzy175 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    98

    Can't get master query to populate all names of attached tables/queries

    I've been working on an inventory form in access and have been having good success so far, but just recently, I've noticed that I am having a hard time having the Master query populate with all the names of the attached tables/queries. I have the Master-tab subform set to Master Query while Site 1 and Site 2 have their own queries. I need any items newly added to Site 1 or Site 2 to be added to the Master Query, but I cannot get it to work. Here are some screenshots.



    Click image for larger version. 

Name:	s1.png 
Views:	16 
Size:	17.7 KB 
ID:	31306Click image for larger version. 

Name:	s2.png 
Views:	17 
Size:	37.9 KB 
ID:	31307

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Please furnish details of "can't get it to work".

  3. #3
    schulzy175 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    98
    I have item names listed for each site. 1 and 2. I created a new item with ID of 36 on site 1. I need the master inv to list every item name from site 1 and 2. Sorry, I am trying to find a better way to explain it. In short, I want the master to list all items from all sites regardless of matching records.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can you please describe your table set up?

    As for
    I am trying to find a better way to explain it
    -- just pretend you're talking to someone who knows nothing of you, you environment or what you are trying to do.
    Keep it simple, clear English - just as if you were talking to an 8 yr old.

  5. #5
    schulzy175 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    98
    Here is an image of the masterinv table. This is the same table for all sites just duplicated. I need it to where if any site adds a new item, it automatically adds it to the master inv.
    Click image for larger version. 

Name:	s3.png 
Views:	13 
Size:	33.0 KB 
ID:	31312

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Is it that the Master query form needs to be refreshed? In the AfterUpdate event of the site pages, add a Requery, either in VBA (Me!formname!itemcombobox.Requery) or thru a macro.

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I've been looking at your query design and have some concerns.

    minor -
    1) You have spaces in field names - a very bad idea.
    2) All tables have a PK field named "ID" - should take the time to create descriptive field names.

    Major -
    3) You have 6 tables that have the identical structure? Why not use 1 table (the master table) with an additional field for site(??) ?
    4) 6 tables linked to 1 table in a 1-to-1 relationship on PK fields? If the PK fields are Autonumber type, very rarely will two Autonumber numbers in different tables be the same and could match different non-related records!

  8. #8
    NoellaG's Avatar
    NoellaG is offline VIP
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi, you say the site names are attached tables. So I assume each site has its own table and needs to be kept update. I see 2 solutions:
    1/ add the Site field to the master inventory table and write an append query that adds the individual records of each site to the central DB (every day or every hour), make sure the site field gets filled there. When looking for the total inventory for 1 item write a groups query that totals the amount/item
    2/ switch to SQL server and replicate the data

    Kind regards
    Noëlla

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 11
    Last Post: 10-30-2016, 05:39 AM
  2. Replies: 3
    Last Post: 01-20-2014, 09:04 AM
  3. Replies: 3
    Last Post: 01-02-2014, 02:07 PM
  4. Replies: 5
    Last Post: 08-22-2013, 04:32 PM
  5. Replies: 5
    Last Post: 04-23-2013, 08:07 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums