Unanswered: Can union query be updated by updating its sources tables
I'm new to access db and have been asked to create a database for work.
I have created multiples tables with the same type of data because each table represents a different process area.
eg. tbl1, field1 = location, field2 = result (number field)
tbl2, field1 = location, field2 = result (number field)
(the location data is obtain from list of fixed locations)
I have created a union query to combine information from both tables so that I can represent the data as a total result for that month by location (the common data).
An example of my union is:
SELECT [tbl1].[field1], [tbl1].[field2]
SELECT [tbl2].[field1], [tbl2].[field2]
From this union query I have created a select query so that I can perform calculations on the results and link data from another table to this result so that I can creat a chart to show monthly trends to management.
It works, however my dilema is that when new data is entered data into eg. tbl1 and tbl2, I have to physical run the union query again to see the update.
Is it possible to link in someway this data so that management can just click the chart button and it shows the update of data on the chart without having to run the union and select query everytime?
I am rattling my head for fear i have set up the table in a way that makes it impossible to achieve what I want.
If anyone can help me with this it will save me time and the headach of needing change the database with different tables.
If your select query is based on the union query, it should pull the current data from the tables. However, you definitely want to rethink your table design. Rather than having a table for each process area, create one table that includes a field for process area. You are bound to run into further problems and have to spend time coming up with work-arounds in the future.
I had a feeling I've made the table too complex, wondering if you could suggest another way of representing the process in the one table.
The reason why I have individual table for each process area is because I inserted this table as a control source as a subform within a main form for the user to enter other info relating to this process area. The main form for this process area sit within a tab form so the user can select the form tab relating to the process area they want to enter data for. I thought at first that I should just place this table as a separate form tab and let the user assign what process this relates to, but i got feed back that it was too much going back and forth for the user and most users were not very database friendly. In trying to make the form easier for the user I've made it worse for myself.
Initially I had a single subform and inserted the same subform into each form tab so the data entry subform was the same and childlink this to the batch number as each batch goes through different processing steps. What I found was that because the subform is controlled by the one table, everytime I view the records for each processing area it showed the same information and is not specific to the information for that particular tab. i hope I have explained it clear it enough.
So I'm aware that what I've done is too complex but I don't know an alternative way of doing this. Any suggestions?
Without seeing the actual data, I'd suggest two tables. One for Process Area information and one for the result detail. Link the tables one-to-many by the PK in the Process Area table. For your GUI you could I'd go with a form/subform setup. If you're actually using object names like tbl1 and field1, this would be a good time to change them to something more descriptive (there's a good sticky for naming conventions in this forum that you should take a look at). If you run into problems, include a bit of detail about the data and how it relates and/or what you're trying to accomplish with the database (I have no idea what the process area, location or results are or how they relate to each other).