Is this possible?

[joe789]

Hi,

I have two tables, one table has a unique identifier (Primary Key) and shows only unique distinct rows. The other table has an identifier which is not unique (Foreign Key). Is it possible to join these two tables and display the unique identifier from table 1 with only the most recently entered data from table 2? Unfortunetly, I have tried several different query techniques to find that my 20,000 records in table 1 become 60,000 records when the two tables are joined. I would like to join the two tables to mimick table 1, show each unique identifier once, and show only the most recent data from table 2 (excluding data that is different but under the same identifier). Their is no linking option for doing something like this, does anyone have any ideas?

Is this possible?

Thank you very much,

Joe

 

[Pat Hartman]

Every table should have a unique primary key. If the table does not contain a single field or combination of fields that would provide a unique identifier, then you should add an autonumber and use that as the primary key.

That said, to obtain the data you want, you will need two queries. The first will find the "unique identifier" of the record that satisifies your condition and the second retrieves the associated data. I assume that each record has an entered date. I'm afraid that if they don't you're SOL.

Select YourForeignKey, Max(YourDate) As MaxDate
From YourManySideTable
Group By YourForeignKey;

Then make a query that joins the OneSideTable to Query1 on the ForeignKey and Query1 to the ManySideTable on the ForeignKey and the DateField. Select the columns you want from each table.

If the date field is unique within the ForeignKey this query will return only one row from the ManySideTable for each row of the OneSideTable. If there can be multiple entries for a given date for the same ForeignKey value, then this query will return each row from the ManySideTable for the MaxDate. If your ManySideTable had had a unique autonumber, that field would have been used by query1 rather than the date field and no duplicates would have been retrieved.

If you want to correct the problem of the missing unique identifier:
1. Create a new table containing all the columns of the ManySideTable
2. Add an autonumber column and set it as the primary key
3. Create a query that selects all the rows from the present ManySideTable. Include an order by clause that orders the recordset by ForeignKey and YourDate
4. Change the select query to an append query.
5. Run the append query
6. Remove any relationships between the OneSide and ManySide tables
7. Rename the existing ManySideTable so you can save it for a while.
8. Rename the new ManySideTable to the name of the original one.
9. Recreate the relationships between the two tables.

I suggest this method rather than the simple sounding one of just adding the autonumber column to the ManySideTable so that you can control the sequence in which the autonumber is assigned to the existing data. If you ran into duplicates from the query that joined query1 back to the main tables, you can change it now to use the autonumber you just assigned.

[This message has been edited by Pat Hartman (edited 10-15-2001).]

 

[joe789]

Thank you!