Rich, you need a completely different data structure to solve this problem.
It sounds like a customer can order multiple publications at once, and and
order for one publication might be supplied in multiple shipments. These
need to be represented as one-to-many relations.
Therefore you will need tables like this:
Product table (one record for each publication you offer), with fields:
- ProductID AutoNumber primary key
- ProductName Text title of the publication
Client table (one record for each person/company you do business with):
- ClientID AutoNumber primary key
- MainName Text surname or company name
- Firstname Text Christian name or contact person.
- IsCorporate Yes/No Yes if it's a company; no if it's a
person.
Orders table (one record each time a client orders something):
- OrderID AutoNumber primary key
- ClientID Number who ordered this
- OrderDate Date/Time when it was ordered
OrderDetail table (one record for each row in the order):
- OrderDetailID AutoNumber primary key
- OrderID Number which order this row belongs to.
- ProductID Number what was ordered.
- Quantity Number how many they want.
If none of that makes sense, open the Northwind sample database, look in the
Relationships window, and you'll see essentially that.
Now you want to handle shipments as well. Theoretically, a shipment could
contain items from different orders, and partically fill the ordered
amounts. Therefore you need 2 more tables:
Shipment table (one record for each time you send a delivery):
- ShipmentID AutoNumber primary key
- ClientID Number who you sent this to
- ShipDate Date/time when this package left
ShipmentDetail table (one record for each type of product in a shipment):
- ShipmentDetailID AutoNumber primary key
- ShipmentID Number which shipment this row belongs to.
- OrderDetailID Number which order line-item was shipped.
- QtyShipped Number how many were shipped.
As an example, say you get an order from John Brown orders 100 of product 1
(publication A), and 500 of product 2 (publication B.) This means you have a
row in the Orders table, and 2 rows in OrderDetail (say OrderDetailID 213
and 214.) You ship the 100 As, but only have 200 of the Bs to ship. So you
create a record in the Shipment table, and it has 2 rows in the Shipment
Detail. These 2 rows refer back to OrderDetail 213 and 214, and you know
from that what the product was and that it's not all shipped yet. You can
query the difference, and find the shortfall.
Later, when more Bs arrive, you create another Shipment record to the same
client, and one row in the Shipment Detail, showing 300 as the QtyShipped.
For each OrderDetailID, when the QtyShipped = the Quantity ordered, the
order is filled.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
Rich said:
Thanks for the post. It is under the date field.
I have tried the suggestion, but the result is exactly the same as before.
I've made many other attempts since to try and solve this, without
success -
starting to wonder if this is possible?
When we receive a request for publications, we note the number requested
and
the date it was requested. Another member of staff then notes how many
where
issued and when it was issued. Mostly this occurs on the same day, but if
we
are out of stocks, part of a request will be issued, with the rest
following.
In other words the fields in the query are, for each of 8 different
publications, a text field (noting the number issued) and a date field
(noting the date it was issued). If publication A and publication B
issuing
dates are both within the user specified dates, this works fine. However
if
publication B was sent outside the user specified dates, the number issued
is
still being included, if publication A was issued within the user
specified
dates.
Any ideas?
--
Thanks
Rich
Allen Browne said:
So you have this in the Criteria row in query design.
What field is it under?
If you open the table in design view, it is a Date/Time field, or a Text
field.
Assuming it's date/time, the next thing would be to explicitly declare
the
parameters in the query, so you have defined their type as well. In query
design view, choose Parameters from the Query menu. Access opens a
dialog.
Enter 2 rows:
[Please enter start date] Date
[Please enter end date] Date
If it still fails after that, post the SQL statement (by switching your
query to SQL View.)
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
I have a table of available publications, with fields for the number
requested, number issued, date requested and date sent for each
publication.
For any individual order, part may be issued first and then the rest of
the
order at a later date.
I need a query to establish the number issued between user defined
dates.
I
currently have the very simple:
Between [Please enter start date] And [Please enter end date]
on separate criteria rows in the date issued fields for each
publication
type. However this also returns the number issued in an earlier time
period,
if part of the order was sent within the user defined dates. I have
tried
numerous criteria, but can't tie down one that will return only the
part
of
an order that was issued between the user defined dates and ignore the
other
part of the order that was issued outside of these dates.
