Conditional Appending?

[mindbender]

I am wondering if this is possible.



I have a table that has install and order data and another table that has rep data
I have 1 query that joins the install Id and order Id to the rep data table. If both install and order id match the reps id then it is marked with an OI
This query appends to a table with the primary key set to Order number
I have a second query that matches on install Id to the rep data table. If it matches it is marked with an I
This appends to the table above and the records already set to OI get ignored
I have a third query that matches on order Id to the rep data table. If it matches it is marked with an O
This appends to the table above and the records already set to OI get ignored

The OIs go into the table first because this is where the rep is both order and install rep works fine. My issue us when the order rep and the install rep are different but both reps are in the rep table. They will not be loaded as an OI in the first step. The install rep will be appended as an I but then the Order Rep will not get appended because the order number already exists in the table as an I. If I add revenue type (OI,O,I) to the primary key I get I and O records and I records, as well as OI records for those orders where the order and install rep are the same.

Is there a way to say append the O and I data if there is no OI data already in the table? Having brain freeze.
Hope this isn't too confusing
Thanks in advance for the help.

[ranman256]

you could do a Dcount on the table that would have OI recs. (or a premade query)
if count = 0 , run the append

i = Dcount("*","table","[field]='OI'")
IF i = 0 then docmd.openquery "qaAppendQuery"

[mindbender]

Added a graphic to try and describe what is happening and what I would like to happenPresentation1.pdf

[mindbender]

I added a graphic to show what I am trying to do. I am not sure how I would get your advice to work. It looks like it would not append anything if there are OI recs in the table which is not quite what I am trying to do

[aytee111]

What is the purpose of the final table? If order number is the primary key then you can't have duplicates so cannot add it twice. To me it looks like it should be a query with some IIF statements.

[mindbender]

I think you are right as always but cannot figure it out.

The final table should append and mark records as OI where the order and install emp match the dtt in the rep table, append and mark records as O where the order emp matches the dtt in the rep table and append and mark records as I where the install emp and dtt match. The issue seems to be with where the order and install emp are different but both in the rep table.

[aytee111]

According to your graphic, create these queries:
- query1 (OI) - order table joined to rep table on terr, and order terr = install terr
- query2 (O) - order table joined to rep table on order terr, and order terr <> install terr
- query3 (I) - order table joined to rep terr on install terr, and order terr <> install terr
- query 4 - using UNION ALL bring in all three queries, this will give you a full list of all the various settings

[mindbender]

Awesome!!! I think I have it working. Just need to validate the results.

Thank you very much