Relationship question

[asnyder]

I have two identical tables. Each table contains information about a different city. Each record in both tables has a date and a time field. By themselves the date and the time fields are not unique, but combined they should be. For example the data would look like this:

4/1/04 08:00AM
4/1/04 08:30AM
4/2/04 08:00AM
4/2/04 08:30AM

I want to combine the information let's say for a specific date and time for both tables. I can't create multiple-field primary keys because of the duplicates, and if I define a relationship between the two tables and create a query, I get two records showing for each table. What is the solution?

 

[FoFa]

Select Date1 from Tbl1
UNION ALL
Select Date1 from TBl2
Group by Date1

But do you have other data elements, and what do you want to do with those?

 

[theprez]

You have a normalization problem. Your data design is not valid, so you are bound to have problems. Unless you have a specific reason. You should have one table with all the fields in your two identical tables with and additional CITY field defined. In your definition combine the city, data and time for the key.