Query Help

  • Thread starter Thread starter ChrisStorer
  • Start date Start date
C

ChrisStorer

Hi,

I have a database that I'm trying to do a many to many query on I
guess....

Here is the table structure:

Table 1 - Item Configuration (this table may contain MANY of the SAME
item_id's with different item_config's)
item_id
item_config

Table 2 - Item Locations (this table may contain MANY of the same
item_id's with many item_configs)
item_id
item_config

Basically, I need to generate a query that will only return the records
between the two tables that don't contain the same data.

For example,

Table 1 may contain:
item_ID = 123
item_config = 10PC
item_ID = 123
item_config = 12PC

Table 2 may contain
item_ID = 123
item_config = 10PC
item_ID = 123
item_config = 20PC

So I would want to see that item 123 has two non-corresponding records,
20PC and 12PC.

Can anyone help?

Thanks

Chris
 
Hi Chris,

This should be fairly easy for you to do, by starting with a wizard that is
included in Access. Select the Queries tab in the database window. Click on
the New button. The last item reads "Find Unmatched Query Wizard". This will
allow you to find records that are present in one table, but not the other.
So, you can go through this wizard two times, to find unmatched records in
each direction. To finish it off, you can "glue" the results of the two
SELECT queries together by creating a Union query, if you don't mind having a
read only result.

Give it a try and post back if you need addtional help.


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
Hi Chris,

Here are some queries I just created for you. It turns out that I had to
slightly customize the result from the unmatched query wizard, by creating an
additional Left Join. To use these SQL statements, create a new query.
Dismiss the Add Tables dialog without adding any tables. In query design
view, click on View > SQL View. You should see the word SELECT highlighted.
Copy the following SQL statements (Ctrl C) and paste them into the SQL view
(Ctrl V), replacing the SELECT keyword each time:

Suggested name for this query:
qryItemConfigurationWithoutMatchingItemLocations

SELECT [Item Configuration].item_id, [Item Configuration].item_config,
"In Item Configurations, but not in Item Locations." AS [Found]
FROM [Item Configuration] LEFT JOIN [Item Locations]
ON ([Item Configuration].item_config = [Item Locations].item_config)
AND ([Item Configuration].item_id = [Item Locations].item_id)
WHERE ((([Item Locations].item_id) Is Null));


Suggested name for this query:
qryItemLocationsWithoutMatchingItemConfiguration

SELECT [Item Locations].item_id, [Item Locations].item_config,
"In Item Locations, but not in Item Configurations." AS [Found]
FROM [Item Locations] LEFT JOIN [Item Configuration]
ON ([Item Locations].item_config = [Item Configuration].item_config)
AND ([Item Locations].item_id = [Item Configuration].item_id)
WHERE ((([Item Configuration].item_id) Is Null));

You can then switch back to the more familiar design view, if you wish, by
clicking on View > Design View. Run the query. Both of the above queries
should be read / write.

Putting these two results together, using a UNION query, results in a read
only query. Also, you cannot switch to the more familiar design view for
union queries. These types of queries cannot be represented in the Query by
Example (QBE) grid.


Suggested name for this query:
quniNonCorrespondingRecords

SELECT [Item Configuration].item_id, [Item Configuration].item_config,
"In Item Configurations, but not in Item Locations." AS [Found]
FROM [Item Configuration]
LEFT JOIN [Item Locations]
ON ([Item Configuration].item_config = [Item Locations].item_config)
AND ([Item Configuration].item_id = [Item Locations].item_id)
WHERE ((([Item Locations].item_id) Is Null))

UNION

SELECT [Item Locations].item_id, [Item Locations].item_config,
"In Item Locations, but not in Item Configurations." AS [Found]
FROM [Item Locations]
LEFT JOIN [Item Configuration]
ON ([Item Locations].item_config = [Item Configuration].item_config)
AND ([Item Locations].item_id = [Item Configuration].item_id)
WHERE ((([Item Configuration].item_id) Is Null));



Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
Tom - you rock.

Thanks a million for the help!

Chris
Tom said:
Hi Chris,

Here are some queries I just created for you. It turns out that I had to
slightly customize the result from the unmatched query wizard, by creating an
additional Left Join. To use these SQL statements, create a new query.
Dismiss the Add Tables dialog without adding any tables. In query design
view, click on View > SQL View. You should see the word SELECT highlighted.
Copy the following SQL statements (Ctrl C) and paste them into the SQL view
(Ctrl V), replacing the SELECT keyword each time:

Suggested name for this query:
qryItemConfigurationWithoutMatchingItemLocations

SELECT [Item Configuration].item_id, [Item Configuration].item_config,
"In Item Configurations, but not in Item Locations." AS [Found]
FROM [Item Configuration] LEFT JOIN [Item Locations]
ON ([Item Configuration].item_config = [Item Locations].item_config)
AND ([Item Configuration].item_id = [Item Locations].item_id)
WHERE ((([Item Locations].item_id) Is Null));


Suggested name for this query:
qryItemLocationsWithoutMatchingItemConfiguration

SELECT [Item Locations].item_id, [Item Locations].item_config,
"In Item Locations, but not in Item Configurations." AS [Found]
FROM [Item Locations] LEFT JOIN [Item Configuration]
ON ([Item Locations].item_config = [Item Configuration].item_config)
AND ([Item Locations].item_id = [Item Configuration].item_id)
WHERE ((([Item Configuration].item_id) Is Null));

You can then switch back to the more familiar design view, if you wish, by
clicking on View > Design View. Run the query. Both of the above queries
should be read / write.

Putting these two results together, using a UNION query, results in a read
only query. Also, you cannot switch to the more familiar design view for
union queries. These types of queries cannot be represented in the Query by
Example (QBE) grid.


Suggested name for this query:
quniNonCorrespondingRecords

SELECT [Item Configuration].item_id, [Item Configuration].item_config,
"In Item Configurations, but not in Item Locations." AS [Found]
FROM [Item Configuration]
LEFT JOIN [Item Locations]
ON ([Item Configuration].item_config = [Item Locations].item_config)
AND ([Item Configuration].item_id = [Item Locations].item_id)
WHERE ((([Item Locations].item_id) Is Null))

UNION

SELECT [Item Locations].item_id, [Item Locations].item_config,
"In Item Locations, but not in Item Configurations." AS [Found]
FROM [Item Locations]
LEFT JOIN [Item Configuration]
ON ([Item Locations].item_config = [Item Configuration].item_config)
AND ([Item Locations].item_id = [Item Configuration].item_id)
WHERE ((([Item Configuration].item_id) Is Null));



Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Tom Wickerath said:
Hi Chris,

This should be fairly easy for you to do, by starting with a wizard that is
included in Access. Select the Queries tab in the database window. Click on
the New button. The last item reads "Find Unmatched Query Wizard". This will
allow you to find records that are present in one table, but not the other.
So, you can go through this wizard two times, to find unmatched records in
each direction. To finish it off, you can "glue" the results of the two
SELECT queries together by creating a Union query, if you don't mind having a
read only result.

Give it a try and post back if you need addtional help.


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
Click to expand...
Click to expand...
 
Back
Top