Complex Update Query

[gemadan96]

I have a table (EventDetails_FWDS) and a query (FWSDExt). The table includes information for a competitor. The query contains information for available divisions that a competitor can compete in. I want to create an update query that assigns the competitor to a specific division based on their age, experience, and event class.

The table contains the following fields: EDID (Primary Key), RegID (Identifies the competitior), Age, Experience, TID (identifies the tournament), EventClass, and DivCode


The query contains the following fields: DivCode, TID (Identifies the tournamnet), EventClass, MinAge, MaxAge, ExpLessThan.

I would like to update the DivCode in EventDetails_FWDS with the DivCode from FWSDExt where the TID and EventClass in both are equal, Age >= MinAge, Age <=MaxAge, and Experience < ExpLessThan

Is this possible?

[jzwp11]

A couple questions first...

What tables are involved in the query that has the available divisions?

Can multiple competitors be assigned to the same division? If so, you actually need a third table and then you would run an append query, not an update query.

[gemadan96]

There is just one table for the query that could be used instead. The Table is FWSD. The DivCode is a calculated field based on the primary key of FWSD. Yes multiple competitors can be assigned to the same division. An append query is used to create the data in the EventDetails_FSWD table based on the CompetitorDetailsExt query. A record is created for each event the competior registers for. When a competitor is added they are automatically assigned to a preliminary division. At a certain point registration closes. That information is used to see what divisions can be combined and which can be split and they becom custom divisions. An append query is used to is used to add records to the FWSD table based on registrations. So the idea is that after divisions are finalized competitors will be assigned to the appropriate divisions and score sheets created based on those final divisions.

[jzwp11]

I am guessing that trying to do this all in 1 update query might run into an un-updateable recordset issue. So I think you will have to use a domain aggregate function (Dlookup())

I came up with 2 approaches:

Approach 1:
Create a (select) query with a subquery that takes care of finding the right division and then doing the update with a separate query.

The query with subquery would look something like this:

query name: qryREGIDWithExpectedDivCode
SELECT EventDetails_FWDS.REGID AS TREGID, (SELECT DIVCODE FROM FWSDExt as Q1 WHERE EventDetails_FWDS.TID=Q1.TID AND EventDetails_FWDS.EventClass=Q1.EventClass AND (EventDetails_FWDS.AGE>Q1.MinAge AND EventDetails_FWDS.AGE<=Q1.MaxAge) AND EventDetails_FWDS.Experience<=Q1.ExpLessThan) AS ExpectedDIVCODE
FROM EventDetails_FWDS;

The above query will fail if a competitor meets the requirements for multiple divisions. If that happens you will have to add more criteria to return only 1 division or do some other manipulations with the subquery.


Now create the update query and use a DLookup() to get the divcode from the above query:

UPDATE EventDetails_FWDS SET EventDetails_FWDS.DivCode = DLookUp("ExpectedDivCode","qryREGIDWithExpectedDiv Code","TREGID=" & REGID)


Approach 2:

Alternatively, you could use the Dlookup() to handle what the subquery does in the first query I presented, but that will get kind of messy. If you wanted to use that approach, everything can be done in the update query as follows

UPDATE EventDetails_FWDS SET EventDetails_FWDS.DivCode = DLookUp("DivCode","FWSDExt","TID=" & TID & " AND EventClass=" & EventClass & " AND MinAge<" & AGE & " AND MaxAge>=" & AGE & " AND ExpLessThan>=" & Experience)

[gemadan96]

Working of your approaches I did the following.

Created a query of EventDetail_FWSD which has a calculated field as follows to assign the division:

DivCode: DLookUp("DID","FWSD","TID =" & [TID] & " AND EventClass=" & "'" & [EventClass] & "'" & " AND MinAge<=" & [Age] & " AND MaxAge>=" & [Age] & " AND ExpLessThan>" & [Experience] & " AND Active=" & -1)

Created a second query that updates the DivCode in EventDetail_FWSD to the DivCode in the first query.

Thanks for the help.

[jzwp11]

You're welcome