Query - what is in stock

[Flemming]

Dear newsgroup

I am trying to make a my first access query. Here is what i want to do:

Two tables regarding delivery of containers which we empty and send back.
Delivery: Delivery Note*, ContainerNO, InvoiceNO, weightIn, DateIn
Return: ContainerNO*, InvoiceNO*, weightOut, LeaveDate
*=primary key. (since both the ContainerNO and the InvoiceNO can reappear i
have made a combined primary key in the Return table.)

By comparing these two tables i want to be able to tell which containers we
are having in our stock.
By using the querry when entering data into the Return table I would like a
drop down list that gives me the possible invoiceNo. Preferable like this: I
enter the outgoing ContainerNo and get the Corresponding InvoiceNO .
Remember that if the Container-Invoice pair occur in both table then that
delivery has been completed.

Ill hope someone in here can help me getting started. I have tried to read
about queries in the help files - but it doesnt seem to help me. When
answering please keep in mind that this is my first querry.
thanks in advance
Flemming

 

[Gary Walter]

I am trying to make a my first access query. Here is what i want to do:

Two tables regarding delivery of containers which we empty and send back.
Delivery: Delivery Note*, ContainerNO, InvoiceNO, weightIn, DateIn
Return: ContainerNO*, InvoiceNO*, weightOut, LeaveDate
*=primary key. (since both the ContainerNO and the InvoiceNO can reappear
i have made a combined primary key in the Return table.)

By comparing these two tables i want to be able to tell which containers
we are having in our stock.
By using the querry when entering data into the Return table I would like
a drop down list that gives me the possible invoiceNo. Preferable like
this: I enter the outgoing ContainerNo and get the Corresponding InvoiceNO
. Remember that if the Container-Invoice pair occur in both table then
that delivery has been completed.

Ill hope someone in here can help me getting started. I have tried to read
about queries in the help files - but it doesnt seem to help me. When
answering please keep in mind that this is my first querry.
thanks in advance

Hi Fleming,

I believe you want to return all records from table Delivery
that have not been completed. If that is so...

Click on "Create Query in Design View"

In the Show Table dialog box,
click on your table "Delivery",
click Add,
click on your table "Return",
click on Add again,
and then click Close.

You should now show a copy of each table
in the query designer.

I believe you want to join them
on the ContainerNO and InvoiceNO fields.

Click and hold down on Delivery table's
ContainerNO field
and "drag and drop"
over on Return table's ContainerNO field.

You should now have a (join) line
connecting the 2 tables going
from
Delivery.ContainerNO
to
Return.ContainerNO

Click and hold down on Delivery table's
InvoiceNO field
and "drag and drop"
over on Return table's InvoiceNO field.

You should now have another (join) line
connecting the 2 tables going
from
Delivery.InvoiceNO
to
Return.InvoiceNO

Right-mouse click on each line and
choose "Join Properties."

Select the option (probably 2) that
includes all records from Delivery table
and only those records from Return table
where the join fields are equal.

Double-click on the top of the Delivery
table which should select all the fields
in table Delivery.

Drag-and-drop this selection down
into a Field row in the grid.

Hold down CTRL key and select
InvoiceNO and ContainerNO in
table Return.

Drag-and-drop this selection from
the table Return down to an empty
Field row in grid.

In the Criteria row under the column
in the grid for Return.InvoiceNO, type

IS NULL

This column should now look like:

Field: InvoiceNO
Table: Return
Sort:
Show: <unchecked>
Criteria: IS NULL
Or:

Do the same thing under column
in grid for Return.ContainerNO:

Field: ContainerNO
Table: Return
Sort:
Show: <unchecked>
Criteria: IS NULL
Or:

If you now go up to top menus
and choose "SQL View," you should
see something like the following for
your SQL:

SELECT
Delivery.[Delivery Note],
Delivery.ContainerNO,
Delivery.InvoiceNO,
Delivery.weightIn,
Delivery.DateIn
FROM
Delivery
LEFT JOIN
Return
ON
Delivery.ContainerNO = Return.ContainerNO
AND
Delivery.InvoiceNO = Return.InvoiceNO
WHERE
Return.ContainerNO IS NULL
AND
Return.InvoiceNO IS NULL;

I think that will give you what you want....

good luck,

gary

 

[Flemming]

Thank you very much.You just made my day.
It was just that kind of detailed help i needed.
Now it is fun again to play around with Access. I have reused "your" query
different places in my database. eg now i can check what is on the way to
the stock.
I will probably be back soon with some more questions - but first ill have
to experiment a little with this new world of possibilities.

Thanks again
Flemming