Distinct Upsert Query, Access 2007

[Brian Woods]

Im new to Upsert queries in Access and I have run into a problem. I have two tables, tblParticipants and tblAssignments. What I need is a query that will perform an upsert from tblParticipants to tblAssignments. For example, the following data in tblParticipants will upsert into tblAssignments as follows:

tblParticipants
CONF BLD RM DATE
1 A 100 1/1/2011
1 A 100 1/1/2011
1 A 101 2/1/2011
1 A 101 3/2/2011
1 B 100 1/1/2011
2 C 200 3/1/2011
2 A 100 4/1/2011

tblAssignments
CONF BLD RM DATE
1 A 100 1/1/2011
1 A 101 2/1/2011
1 B 100 1/1/2011
2 C 200 3/1/2011
2 A 100 4/1/2011

Essentially, there are conferences with multiple participants. Each participant is assigned a room, sometimes with a roommate. So the upsert query will take the records from tblParticipants where the BLD and RM codes are distinct for the earliest DATE, and UPSERT them into tblAssignments. Any help would be great, as I am totally lost on how to do this without temporary tables or deleting any records from a similar table. Thank you.

[NeoPa]

Most people are new to Upsert queries in Access Brian, as they don't exist (At least I could find no reference to them even for newer versions). The Upsert concept in Jet appears to be managed by INSERT-ing and UPDATE-ing. I would suggest using the UPDATE first, followed by the INSERT (Why bother to complicate the filtering for the UPDATE by either excluding the new records or duplicating an update of a new record?).

Does that answer your question?

[nico5038]

Hmm, looks to me you need a GroupBy query to get the "tblAssignments".
But basically you don't need such a table, as the GroupBy query can be used instead.
Your query would look like:

SELECT CONF, BLD, RM, Min(DATE), count(*) as Participants
FROM tblParticipants 
Group by CONF, BLD, RM
 

I've added [Participants] as an extra field showing you how many participants there are for the Group By fields.

Getting the idea ?

Nic;o)

[NeoPa]

Good point Nico. I was so focused on the UPSERT thingy I missed the obvious.

@Brian, While there may be some interesting info in my post (I hope) you should treat Nico's as the one most appropriate for your needs ;-)

[nico5038]

Focussed or "UPSET" by UPSERT ?

<LOL>

Nic;o)

[Brian Woods]

Nico, that's essentially what I am trying to do. The problem is, I can't get the data that is in the query you gave me into the tblAssignments. This is an example of another Upsert query in my DB

UPDATE dbo_CONF 
 
LEFT JOIN [Local dbo_CONF] ON 
dbo_CONF.CONFERENCE_NO=[Local dbo_CONF].CONFERENCE_NO 
 
SET [Local dbo_CONF].CONFERENCE_NO = dbo_CONF.CONFERENCE_NO, 
[Local dbo_CONF].CONF_NAME = dbo_CONF.CONF_NAME, 
[Local dbo_CONF].DATE_IN = dbo_CONF.DATE_IN, 
[Local dbo_CONF].DATE_OUT = dbo_CONF.DATE_OUT, 
[Local dbo_CONF].UDEF02 = dbo_CONF.UDEF02;

The above query works just fine. What I have for the Participants Upsert query so far is this (Participant Residences is actually tblAssignments):

UPDATE [Local dbo_CONF] 
 
LEFT JOIN ([Local dbo_PART] 
LEFT JOIN [Participant Residences] 
ON ([Local dbo_PART].ROOM_NO = [Participant Residences].Residence) 
AND ([Local dbo_PART].BUILDING_CODE = [Participant Residences].[Building Code]) 
AND ([Local dbo_PART].CONFERENCE_NO = [Participant Residences].[Conference No])) 
ON [Local dbo_CONF].CONFERENCE_NO = [Local dbo_PART].CONFERENCE_NO 
 
SET [Participant Residences].[Conference No] = [Local dbo_PART].[CONFERENCE_NO], 
[Participant Residences].Conference = [Local dbo_CONF].[CONF_NAME], 
[Participant Residences].[Conference Short Name] = [Local dbo_CONF].[UDEF02], 
[Participant Residences].[Check-In] = [Local dbo_PART].[DATE_IN], 
[Participant Residences].[Check-Out] = [Local dbo_PART].[DATE_OUT], 
[Participant Residences].[Building Code] = [Local dbo_PART].[BUILDING_CODE], 
[Participant Residences].Residence = [Local dbo_PART].[ROOM_NO];
 

Somewhere, I need that GroupBy clause in this code, and I am totally lost.

[nico5038]

Just curious why you want such a table as I stated in my first comment "But basically you don't need such a table, as the GroupBy query can be used instead."

The only reason to store the data would be to get a history when the tblParticipants isn't holding all data.

For the UPSERT (new for me, but I get the idea) I normally use two queries:
1) Delete * from tblAssignments where CONF & BLD & RM in (select CONF & BLD & RM from tblParticipants)
2) INSERT INTO tblAssignments (<the groupby query>)

Getting the idea ?

Nic;o)

[NeoPa]

Nico:
For the UPSERT (new for me, but I get the idea) I normally use two queries:
1) Delete * from tblAssignments where CONF & BLD & RM in (select CONF & BLD & RM from tblParticipants)
2) INSERT INTO tblAssignments (<the groupby query>)

I'm always hesitant to question anything Nico says (as I don't know anyone with more experience of Access than he), but the above approach might cause problems in a situation where not all the data is provided by the data that is available. This can happen when AutoNumber IDs are used for instance.

This may well not be a problem for you, but if it is then the approach outlined earlier (post #2) should still work.

PS. As Nico said Brian, there's almost no conceivable reason for maintaining a separate tblAssignments table in your database. Duplicating data like that is never recommended, and would cause your database to fail all the normalisation tests (See Database Normalisation and Table structures).