creating a query from two tables

  • Thread starter Thread starter hard2findparts
  • Start date Start date
H

hard2findparts

I know just enough about Access to get into trouble. I have created this db
from scratch, and now I need some help.

This is my sql:

SELECT INV.MFG, INV.CONDITION, ALTERNATE.ALT, INV.DESCRIPTION, INV.QTY,
INV.LOCATION
FROM INV INNER JOIN ALTERNATE ON INV.[P/N] = ALTERNATE.[P/N]
WHERE (((INV.QTY)>0))
ORDER BY INV.MFG;

The INV table contains information such as autonumber id, part number,
description, condition, quantity, location, notes, weight, etc.

The ALTERNATE table contains autonumber id, part number, alternate part
number.

When I run this query, I get the following results:

MFG CONDITION ALT DESCRIPTION QTY LOCATION
A/M NEW 123 MONITER 5 5S6
A/M USED 456 KEYBOARD 3 4P3
A/M NEW 789 MOUSE 2 9Z7

The query is doing exactly what I told it to, but I don't know how to tell
it to do what I want it to do.
I would like it to compare the ALT column with the corresponding part number
in the INV table and display the information from the MFG column for the
alternate part number.

The result I am looking for would look like:
MFG CONDITION ALT DESCRIPTION QTY LOCATION
DEL NEW 123 MONITER 5 5S6
APL USED 456 KEYBOARD 3 4P3
HP NEW 789 MOUSE 2 9Z7

Can someone please help?
Thanks in advance
david
 
Am I right that the Alternate.[ALT] field refers back to another
record in the INV table, and it's that record's MFG that you want to
display? If so, you can try a subquery like this:

SELECT
(SELECT INV.MFG FROM INV WHERE INV.[P/N]=ALTERNATE.[ALT]) as
MFG,
INV.CONDITION, ALTERNATE.ALT, INV.DESCRIPTION, INV.QTY,
INV.LOCATION
FROM INV INNER JOIN ALTERNATE ON INV.[P/N] = ALTERNATE.[P/N]
WHERE (((INV.QTY)>0))
ORDER BY INV.MFG;
 
someone helped me on another thread, solved that problem, and I applied the
same idea to this problem. I ended up with this sql:

SELECT
(SELECT INV.MFG FROM INV WHERE INV.[P/N]=ALTERNATE.[ALT]) as
MFG,
INV.CONDITION, ALTERNATE.ALT, INV.DESCRIPTION, INV.QTY,
INV.LOCATION
FROM INV INNER JOIN ALTERNATE ON INV.[P/N] = ALTERNATE.[P/N]
WHERE (((INV.QTY)>0))
ORDER BY INV.MFG;

It seems to be working perfectly.
Thanks

bismuth83 said:
Am I right that the Alternate.[ALT] field refers back to another
record in the INV table, and it's that record's MFG that you want to
display? If so, you can try a subquery like this:

SELECT
(SELECT INV.MFG FROM INV WHERE INV.[P/N]=ALTERNATE.[ALT]) as
MFG,
INV.CONDITION, ALTERNATE.ALT, INV.DESCRIPTION, INV.QTY,
INV.LOCATION
FROM INV INNER JOIN ALTERNATE ON INV.[P/N] = ALTERNATE.[P/N]
WHERE (((INV.QTY)>0))
ORDER BY INV.MFG;



I know just enough about Access to get into trouble. I have created this db
from scratch, and now I need some help.

This is my sql:

SELECT INV.MFG, INV.CONDITION, ALTERNATE.ALT, INV.DESCRIPTION, INV.QTY,
INV.LOCATION
FROM INV INNER JOIN ALTERNATE ON INV.[P/N] = ALTERNATE.[P/N]
WHERE (((INV.QTY)>0))
ORDER BY INV.MFG;

The INV table contains information such as autonumber id, part number,
description, condition, quantity, location, notes, weight, etc.

The ALTERNATE table contains autonumber id, part number, alternate part
number.

When I run this query, I get the following results:

MFG CONDITION ALT DESCRIPTION QTY LOCATION
A/M NEW 123 MONITER 5 5S6
A/M USED 456 KEYBOARD 3 4P3
A/M NEW 789 MOUSE 2 9Z7

The query is doing exactly what I told it to, but I don't know how to tell
it to do what I want it to do.
I would like it to compare the ALT column with the corresponding part number
in the INV table and display the information from the MFG column for the
alternate part number.

The result I am looking for would look like:
MFG CONDITION ALT DESCRIPTION QTY LOCATION
DEL NEW 123 MONITER 5 5S6
APL USED 456 KEYBOARD 3 4P3
HP NEW 789 MOUSE 2 9Z7

Can someone please help?
Thanks in advance
david
Click to expand...
Click to expand...
 
Back
Top