AnsweredAssumed Answered

What is similar to a union query in MSAccess?

Question asked by ssp.columbus on Feb 3, 2012
Latest reply on Feb 11, 2012 by philmodjunk


What is similar to a union query in MSAccess?


I am not a developer.  I am an interior designer developing a database to specify furniture on large commercial projects.  I have a database in Access that I am trying to recreate in FileMaker.  I am new to filemaker but have been through the training program.


Program:  FileMaker Pro 11 Advanced

Database Overview: The basics of the database …

Room table for all the rooms in a building

Catalog table of all the pieces of furniture

New Assets table (RoomDetailNew)– items from the catalog are put into a room

Typical Assets table (RoomDetailTypical) and subtables – items from the catalog are put into typicals (repeating group of furniture items) then the typical is put into a room – example is a typical office has a desk, credenza, bookcase and desk chair.  If there are 100 offices it is a lot faster to select the typical rather than enter the data into each of the 100 rooms.

Existing Assets Table (RoomDetailExisting) – items from another database of existing assets being reused that are put into a room


There is a room layout with 3 portals to add new assets, typicals and existing assets. (see attached image)


Problem:  I need various reports that combine the data from the 3 main Asset tables.  Although I can summarize the information I need an item list of every item that is in each room.


Desired Result: A table that automatically takes information from other tables to create a list of every item that is in every room. 


In Access I used a select query to get a list of all the items in a typical listed by room then I used a union query to combine the list of New Items, Items from the typicals and Exisitng Items. 


I have 2 specific problems I am trying to solve.


1)   How do I create a table which is a list of each item that is in each room from the typicals?  Table 1 (RoomDetailTypical) that combines the room # and the typical # (entered in the room layout) and allows the quantity to be entered.  The 2nd table (Typicals) is the table which contains the name of the typical and the foreign key for the 3rd table (TypicalDetails) which combines the typical # and the assets that make up the typical.  This table is then linked to the catalog of furniture.  The portal on the room layout shows the list of items in the typical.  I have created a “total assets from typical” table but I'm not sure how to get the data into it.

2)   Once there is the table that contains all the items in each room from the typicals I need to combine the items from the typicals, the new assets and the existing assets like I did in the union query in Access to create a “total assets” table that list each and every asset in each room.

I’m guessing I need to write scripts to set the data into the new “total assets from typical” and “Total Assets” but I’m not sure what the best method would be.  I would appreciate it if anyone could help me figure out the solution to these problems.