query problem

[colinmunnelly]

I have a linked table called (COR_log) This is for incoming orders. I have a main table which holds the data for outgoing orders. Each outgoing order is funded by particular incoming so when entering thje data for an outgoing order i can choose which incoming instruction i want. I need to create a report that shows me what incoming orders ( COR_Log) have not been used for funding at all.
Hope i have explained enough

Anyone have any suggestions

 

[FoFa]

Whatever foreign is not used, there you go. I do not know what you are joining the two tables on. Where is the key that joins the two, in your input or output table?

 

[colinmunnelly]

At the moment i just use the COR_Log as a lookup table really. The only join there is is when i enter and outgoing with the incoming.

 

[simongallop]

Guessing (or is it hoping??) that you have an ID field in COR_Log. Next presumption is that in the main outgoing table you register the ID field of the incoming instruction. So long as this is the case then you run a query showing all ID numbers in COR_Log that do not exist in the main table. Something like:

SELECT COR_Log.ID
FROM Main_Table_Name RIGHT JOIN COR_Log ON Main_Table_Name.IncomingFieldID = COR_Log.ID
WHERE Main_Table_Name.IncomingFieldID Is Null;


HTH

 

[colinmunnelly]

Thanks for your reply but there is no field within the cor_log that notes any outgoing instruction therfore the is null doesn't work. By the way i call the outoing instruction a CMI_Number and COR_Number.

 

[simongallop]

Note. I am not taking the outgoing ID in any of the example.

The code I sent uses the following:

Table: COR_Log, FIELD: ID {This ought to be the ID of the INCOMING order}

Table: Main_Table_Name, FIELD: IncomingFieldID {This is the field in the main table that ought to show which INCOMING ID you used for the relevant Outgoing order}

I am then saying that if an ID exists in COR_Log BUT does NOT exist in Main_Table_Name then ergo the INCOMING order has not been used.

HTH