basic query help for two linked tables

[shadowsong]

i have three tables: order entry, order receipt, and an part number
cross reference table. part numbers are related across all three
tables, as are order numbers. i would like to find the complete order
history (all orders entered and all orders received) for a given part
number.

i can find all records for that part number that match across both
tables (ie, all orders that have been both entered and received), or
all records for the part number on one table (either orders entered OR
orders received, but not both). what i need is all matching records on
the order entry table, plus any records associated with them on the
order receipt table.

the results would then look like this:

PART NUM ONUM OQTY ORDERDATE INVNUM INVQTY INVOICEDATE
8210017822 2847C 700 1/16/2006 5145 700 3/16/2006
8210017822 2907C 300 3/20/2006 5198 300 5/20/2006
8210017822 2938C 300 4/25/2006
8210017822 3009C 300 8/4/2006

(sorry, it lines up better in courier)

part number and order number are found on both tables, order qty and
date are only found on order entry, and the last three invoice columns
are only found on the order receipt table.

so 4 records match the search on the order entry table, and two records
on the order receipt table are linked to the records returned on the
order entry table by way of the part number and order number fields.


how do i do this?

 

[Jeff Boyce]

It sounds like you want to see all order entry and all order receipt
information for each part number, regardless of whether either/both/neither
exist.

Start with your [PartNumber] table in your query design mode. Add the
[Order Entry] table and join on the part number field. Now highlight the
join and change it so that it takes ALL of the [PartNumber] records and ANY
[Order Entry] records with matching part number.

Repeat for the third table, including the "directional" join.

Does this do what you want?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[shadowsong]

ah, i'd been doing inner joins instead of left joins. that fixed it.

however.... is there any way to have it promt me for the criteria (ie
the part number i'm searching for) every time i run the query? is there
any way to have it prompt me for the part number field being
constrained?

ideally i would like to be able to choose between orderentry.partnumij
and orderentry.partnumia for the fields that contain the criteria - i
know it's a messy system, but we have two different kinds of part
numbers.

is this something i should be doing on a data access page rather than
doing it directly, or is a data access page only for when you can't get
to a database directly?

 

[Jeff Boyce]

If I recall correctly, the data access page has more to do with where the
data is store (e.g., SQL-Server vs. Jet).

One approach to doing a somewhat variable "select by" is to use a form to
gather the selection criteria, and to either modify your queries to use the
value(s) on the form, or to actually generate a SQL statement (i.e., a
query) "on the fly", based on the selected values on the form. Either way
will take a bit of work, but the former is a bit easier.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[shadowsong]

here we go: looks like i just needed to do a parameter query.

however, i would like it to be even more complicated: is there a way to
have it constrain the results by EITHER orderentry.partnumij OR
orderentry.partnumia?

here's the current code:

SELECT OrderEntry.PartNumIJ, OrderEntry.OrderNum, OrderEntry.OrderQty,
OrderEntry.OrderDate, OrderReceipt.InvoiceNum, OrderReceipt.InvoiceQty,
OrderReceipt.InvoiceDate
FROM OrderEntry LEFT JOIN OrderReceipt ON (OrderEntry.PartNumIJ =
OrderReceipt.PartNumIJ) AND (OrderEntry.OrderNum =
OrderReceipt.OrderNum)
GROUP BY OrderEntry.PartNumIJ, OrderEntry.OrderNum,
OrderEntry.OrderQty, OrderEntry.OrderDate, OrderReceipt.InvoiceNum,
OrderReceipt.InvoiceQty, OrderReceipt.InvoiceDate
HAVING (((OrderEntry.PartNumIJ)=[Enter Part Number:]));


could i do something like
HAVING (((OrderEntry.PartNumIJ)=[Enter Part Number:])
or ((OrderEntry.PartNumIA)=[Enter Part Number:]));

i just tried it and it doesn't work, but i don't know if that's because
my premise is flawed, or just my syntax.

 

[Jeff Boyce]

If you work in the query design mode and get it working there, you can then
change the view to SQL and see how Access writes the SQL statement.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[shadowsong]

Ah, i thought the "or" line on the criteria field only worked when you
had two criteria in the same column, but it looks like it works across
both columns and does exactly what i need.

Is there a way to have the query only prompt for the second constraint
if nothing is entered in the first constraint box?
Or alternately, instead of displaying two separate text boxes for the
two constraints, can I have it display one text box with a radio button
to select which field is being constrained?

 

[Jeff Boyce]

If you want that kind of functionality, you'll have to step outside of the
query itself.

Create a form that lets you select the criterion or criteria you wish.
Revise the query to use the form's controls as a source. Note that the form
has to be open for the revised query to work.

This may become too complex to use parameters (pointing to the form) from
the query. In that case, in code behind a command button on the form, add a
way to dynamically build the SQL statement, based on whether/not certain
criteria are selected/indicated.

Regards

Jeff Boyce
Microsoft Office/Access MVP