Exclude data when appears on other tables

[markdooler]

Ok so this is a complicated one so i will do my best to explain

So i have a table called tblDaySheet

This is all my master data that spans data from 6 different sites.

This table has 2 fields that i want to reference, WIPNumber and Site

i also have 6 other tables labelled tblRB51, tblRB52, tblRB53, tblRB54, tblRB55, tblRB56.

These tables all have 1 field named WIPNumber

The reason i have these 6 tables is the fact that the 6 different sites need to input 1 field of data from an external system. This data is specific to site and there is no other way of me getting it into the database without manual input. (i have already created this function and it is working.

SO.......

In a query, i would like to get the query to look at Site in tblDaySheet, then go and look at that sites tblRB5 and look for the WIPNumber. If the WIPNumber does not appear in both tables, i want it to exclude it from the query output.

Any ideas?

 

[Ranman256]

if this a form where yo enter the 2 #'s, wip & site
then pick the 2 tables to search,
then if true show the results

or a table tblDaySheet with lots of sites & wips
and they must join to the tables 51 thru 56 to find a result?

 

[markdooler]

if this a form where yo enter the 2 #'s, wip & site
then pick the 2 tables to search,
then if true show the results

or a table tblDaySheet with lots of sites & wips
and they must join to the tables 51 thru 56 to find a result?

Sorry, not sure i understand what you are asking.

the tblDaysheet holds all the data and i want to use the data in each tblRB5 to act as a filter, if it doesnt exist in one of the tblRB5 then filter it out.

Thanks

 

[plog]

...there is no other way of me getting it into the database without manual input. (i have already created this function and it is working.

That's kinda contradictory--If its "manual" what does the function do?

Import process aside, all your tblRB5X data needs to be in the same table. Whether you do this on import or have a query APPEND all the data together once the process is done, that's the solution for the issue you posted about. Once the data is in one table a simple INNER JOIN is the way to go.

 

[The_Doc_Man]

Two comments:

1. If these things in the tblRBx are ALL the same structure, then you have a structural error in that you would have these values all in one table with one more field that tells you the origin of the data.

2. You can use a "NOT IN" subquery to do what you want but your question is a bit ambiguous. However, if you have six different tables to test, that means you might have six different tests to run.

SELECT WIPnumber FROM tblDaySheet WHERE WIPnumber NOT IN ( SELECT WIPnumber FROM tblRB5 ) ;

However, because you have six different tables to test, you might have to write this as

SELECT WIPnumber FROM tblDaySheet WHERE ( WIPnumber NOT IN ( SELECT WIPnumber FROM tblRB1 ) ) 
AND ( WIPNumber NOT IN ( SELECT WIPnumber FROM tblRB2 ) )
AND ....

But the test would be FAR easier if you combined those tables. The other way to do that would be to build a UNION query that gives you the content of all six tables at once and run the NOT IN operation against the UNION query.

 

[markdooler]

That's kinda contradictory--If its "manual" what does the function do?

Import process aside, all your tblRB5X data needs to be in the same table. Whether you do this on import or have a query APPEND all the data together once the process is done, that's the solution for the issue you posted about. Once the data is in one table a simple INNER JOIN is the way to go.

So the data in the tblRB5X is a log of "open jobs" once closed in our system they disappear from this report and we need to update the data. I want my DB to do the same but not delete the data completely. So if the WIPNumber is on the RB5 its still live and i still want it to show in the job list. If its not on the RB5 I don't want it to show on the job list. The manual part is us taking this report from our system and putting it into the DB.

This data changes daily, hourly etc.

I thought about joining all 6 tables into 1 via an append but all 6 sites will update at different intervals so I would need to delete all the data in the merged table and re-append every time someone updates it so I keep the list current.

I will research the inner join method, its not something i am familiar with.

 

[markdooler]

Two comments:

1. If these things in the tblRBx are ALL the same structure, then you have a structural error in that you would have these values all in one table with one more field that tells you the origin of the data.

2. You can use a "NOT IN" subquery to do what you want but your question is a bit ambiguous. However, if you have six different tables to test, that means you might have six different tests to run.

SELECT WIPnumber FROM tblDaySheet WHERE WIPnumber NOT IN ( SELECT WIPnumber FROM tblRB5 ) ;

However, because you have six different tables to test, you might have to write this as

SELECT WIPnumber FROM tblDaySheet WHERE ( WIPnumber NOT IN ( SELECT WIPnumber FROM tblRB1 ) )
AND ( WIPNumber NOT IN ( SELECT WIPnumber FROM tblRB2 ) )
AND ....

But the test would be FAR easier if you combined those tables. The other way to do that would be to build a UNION query that gives you the content of all six tables at once and run the NOT IN operation against the UNION query.

Yes i could use a union query to join them all but i would need to add another field to the tblRB5x table to add the site that they originated from.

The added complication is that there may be a duplicate WIPNumber from 2 different sites so it would need to reference the site first/

I think i may be asking too much from the system to be fair.

 

[markdooler]

OK so i think i solved this myself based on what you have all said.

I created a new field in each tblRB5 with a default value of the site it relates to.

Created a union query to join them.

In the query that runs the output form i added the union query and added a relationship to both WIPNumber and Site (that appear in both) and set it to display where the data appears in both. Violla........ i think.

 

[Pat Hartman]

You shouldn't be using a union query. ALL the data needs to be stored in a single table. Once the data is stored correctly, the problem goes away.

 

[markdooler]

You shouldn't be using a union query. ALL the data needs to be stored in a single table. Once the data is stored correctly, the problem goes away.

Couldnt agree more, however our opperation and management sytem we use makes that impossible.

 

[Pat Hartman]

I suggest that you rethink that excuse. Nothing external should control your table design. As long as the consolidated table includes a site identifier, you can always isolate the records for a site using a query.