Table Design to get the Query results I want!

[Guest]

Hi

I am currently trying to design a table (or 2) that will give me the
opportunity to build a query to get the results I need.

Basically the situation is that we receive deliveries each day from around 8
different couriers.

I need to log each delivery and each parcel. The parcels 99% of the time
have a purchase order of some description and I need a brief description of
what's in the parcel.

So the fields are:
Courier
Number of boxes
Purchase Order number
Description
Signatory
Date

the purchase orders are not unique as you may have several parcels at
different times on the same purchase order. I want to be able to input
information for Courier, no of boxes, signatory and date once, and somehow
link the different Purchase Order numbers to the one delivery.

I then need to create a query where you can search on either, Purchase order
number, description or date to see when it came in.

I've played about with different tables and queries and not got everything
to work and I'm sure there is a simple answer that I've just missed.

Many thanks in advance for your help

Elaine

 

[Joseph Meehan]

Hi

I am currently trying to design a table (or 2) that will give me the
opportunity to build a query to get the results I need.

You want four tables not one or two

Deliveries
Date
NumberofBoxes
Parcels
Descritpion
Couriers
PurchaseOrders

Other fields I am not too sure of as I am not sure how they relate to
each other.

Basically the situation is that we receive deliveries each day from
around 8 different couriers.

I need to log each delivery and each parcel. The parcels 99% of the
time have a purchase order of some description and I need a brief
description of what's in the parcel.

So the fields are:
Courier
Number of boxes

Boxes per PO or Delivery?

 

[Allen Browne]

Elaine, if you are trying to handle purchase orders and deliveries, and
match them against each other, you will need at least these tables:

Supplier table:
SupplierID primary key

Product table:
ProductID primary key

Courier table:
CourierID primary key

PurchOrder table:
PurchOrderID AutoNumber primary key
PurchOrderDate Date/Time
SupplierID relates to Supplier.SupplierID

PurchOrderDetail table:
PurchOrderDetailID AutoNumber primary key
PurchOrderID Number relates to
PurchOrder.PurchOrderID
Quantity Number how many were ordered.
ProductID relates to Product.ProductID
UnitPrice Currency how much each

Delivery table:
DeliveryID AutoNumber primary key
DeliveryDate Date/Time
CourierID relates to Courier.CourierID
BoxCount Number number of packages
delivered.
Signatory

DeliveryDetail table:
DeliveryDetailID AutoNumber primary key
DeliveryID Number relates to
Delivery.DeliveryID
Quantity Number how many were ordered.
PurchOrderDetailID Number relates to
PurchOrderDetail.PurchOrderDetailID

Since one purchase order can cover many items, you need a header table
(PurchOrder), and a related table with a record for each product purchased
(PurchOrderDetail.) If that is a new concept, open the Northwind sample
database that installs with Access, and choose Relationships from the Tools
menu. The Orders and Order Details tables work like that.

Similarly, one delivery can cover many items, so you need a Delivery table
and a DeliveryDetail table. The items in the delivery may not all relate to
the same purchase order, so you will have to match the DeliveryDetail items
to the PurchOrderDetail items.

For any row in PurchOrderDetail, it is fully delivered with the sum of the
Quantity for all the matching PurchOrderDetailID records in the
DeliveryDetail table matches the Quantity originally ordered. This allows
you to determine which rows from which orders are undelivered, partially
delivered, or fully delivered.

You will then be able to create the queries you asked for.

 

[Guest]

Hi Joseph

All fields relate to Deliveries and the Purchase order is just a useful
reference number, sorry for any confusion!

Thanks for your help - I'll have a fiddle and see if that will work

Elaine

 

[Guest]

Thanks Allen

I will have a go at something like this

Elaine

 

[Joseph Meehan]

Hi Joseph

All fields relate to Deliveries and the Purchase order is just a
useful reference number, sorry for any confusion!

Thanks for your help - I'll have a fiddle and see if that will work

Elaine

It looks like Allen B has found this thread and has given you a good
detailed answer. I am sure you will not go wrong by following his advice,
he knows ten times as much about Access as I do.