Forum Discussion
Creating relationships between multiple tables
Hi, very much a new user.... I have 5 data tables, all with similar column headings and granularity, just with different price data in each. e.g.:
Table 1
Date Hour PriceA
Table 2
Date Hour PriceB
Table 3
Date Hour PriceC
I want to build a report so that I can compare the PriceA/B/C in any date or hour. When I try create the relationships between either the date or hour fields I get either the error "This could be because there is missing intermediate data to connect the two columns" or "you can't create a relationsip between these two columns because one of the columns must have unique values".
I looked at other similar posts on here, but can't work out the solution.
Any ideas....? Thanks.
5 Replies
TMQ,
Have you considered unioning the 5 tables into one? You will need to add another column to identify item (A, B, C,...). So your resultant table would have the columns
Date / Hour / Price / Item
You can do this in the "Edit Queries" window by using "Append Queries". There's even an option to do "Three or more tables" which would be helpful in this case.
Does that help?
~ DerekI'll give that a try and let you know..... thanks!
No luck on the appending queries.... maybe the below picture will help explain. The five tables have similar (not exactly the same) data in the first three fields, and then I want to build reports analysing the remaining fields. When I tried to append the queries it brought it all together, but then I had an extra column with "null" in it.
