Query multiple tables/create new with results

[hollarindy]

Hello again,

I am trying to query a like field on two table and make one table. The field is a simple check box and i need the query to return all values equal to -1 (checked). It is frustrating because I can see what i want for an end product I just can't figure out how to get started.:banghead:

 

[jdraw]

I am trying to query a like field on two table and make one table. ???
How about showing us the actual query or the tables involved?

 

[hollarindy]

Here are the tables I am working with. I keep forgetting you can upload files in here man my brain is mush. Anyway... Using the "selected" column I want to build a query that will pull results from both tables where the value is equal to true or -1. It changes (the value) depending on the format you view the table in but it's all the same.

 

[jdraw]

When I try to open your attachments(both of them), it says unrecognizable format.

 

[hollarindy]

Sorry about that. Government computers are picky about how you do things. Lets try them from a .zip file.

 

[jdraw]

What exactly in plain English are you trying to do? Can you give a simple example showing
start conditions and what I want to end up with?
What do these 2 tables represent?

 

[hollarindy]

These two tables are a randomly selected sample of a larger group of data. As you can see I have a list of 20 and a list of 10. I am going to add some conditional formating to the forms these two tables are linked to that will highlight duplicates in the AUTH column. The user will then be able to uncheck items on the list of 20 that are duplicated and "replace" them by checking a box on the list of 20. I know this would be easier with VBA but I have ben instructed to not use it.

 

[jdraw]

??? You mention forms but you have only shown 2 xls files????
What makes a duplicate a duplicate? Remember that in database terms a duplicate has
equal values in the associated fields of 2 or more records.
Auth is only 1 field, what else is involved in the "duplicate" criteria?

 

[hollarindy]

I'm trying not to side track you here. That is why I did not give you the forms. The check boxes on the forms control the selected column on the tables. The typical rules for a database do not apply to what I need to do ass far as duplicate records go. I realize that the entire record is not a duplicate and that the AUTH column is potential the only repeated part of the record. All I need to know is how to write my query to combine tblOvrDrwfrm and tblRndfrm based on what is placed in the selected column. The end table I want will have no duplicates in the AUTH column. If the AUTH column has a duplicate then that means I have 2 like items they could have different part numbers but thay are for my purposes the same so I need to through at least 1 of them out.

 

[jdraw]

Looking at the xls files as tables, you can find the duplicates(Auth) within the table(tblRndfrm) using

SELECT tblRndfrm.[Auth], tblRndfrm.[ID1], tblRndfrm.[ID], tblRndfrm.[Part Number], tblRndfrm.[Noun], tblRndfrm.[Serial Number]
FROM tblRndfrm
WHERE (((tblRndfrm.[Auth]) In (SELECT [Auth] FROM [tblRndfrm] As Tmp GROUP BY [Auth] HAVING Count(*)>1 )))
ORDER BY tblRndfrm.[Auth];

and tblOvrDrwfrm using

SELECT tblOvrDrwfrm.[Auth], tblOvrDrwfrm.[ID1], tblOvrDrwfrm.[ID], tblOvrDrwfrm.[Part Number], tblOvrDrwfrm.[Noun], tblOvrDrwfrm.[Serial Number]
FROM tblOvrDrwfrm
WHERE (((tblOvrDrwfrm.[Auth]) In (SELECT [Auth] FROM [tblOvrDrwfrm] As Tmp GROUP BY [Auth] HAVING Count(*)>1 )))
ORDER BY tblOvrDrwfrm.[Auth];

It is not clear from your posts which field associates records in one table to the other.

This query shows which records have the same ID1 in each table, but I just selected that field.

SELECT tblOvrDrwfrm.ID1, tblOvrDrwfrm.ID, tblOvrDrwfrm.[Part Number]
, tblOvrDrwfrm.Noun, tblOvrDrwfrm.Auth AS AO, tblRndfrm.Auth AS AR, tblRndfrm.[Part Number]
FROM tblOvrDrwfrm INNER JOIN tblRndfrm ON tblOvrDrwfrm.ID1 = tblRndfrm.ID1;

 

[hollarindy]

Thanks for the help. I'm going to give your ideas a try and see if they can get me closer to my goal. Even if it doesn't work exactly he way I want I think it will push me in the right direction.

Let me know what you think about this. I run a create table query to take all of the "True" values from the selected field on tblRndfrm and then run an Append query to take all the "True" values from tblOvrDrwfrm and add them to the table created with the first query. Does that make sense?

 

[jdraw]

That may work, you know the data and what your real goal is better than I do.
What fields relate these tables?
It is better if the reader has some idea of what you are trying to do. I know you trying not to complicate things, but we don't know your experience level. It is easier to give an overview in plain English and the reader has the choice to ask for clarification or more details, or replay with some advice/assistance.

The reality is the form is just a method to display/present data from underlying tables (either directly or via a query). So describing the Form and posting the data for the tables (I think) leads to a little confusion.

In any case Good luck with your project.