Simple inventory question

[Guest]

Hello,

I was hoping to get som help with a challenge I am facing...

My company has decided to rent out part of it's warehouse, and yours truly
is in charge of keeping track of what's in there and invoicing our customers
for the storage.

I have made a simple database, where the main fields are 'client_id',
'date', and 'number' (number of pallets going in and out of the warehouse.
Positive number for incoming, negative for outgoing).

At any given time, I would like to run a weekly query that told me how many
pallets each client had in store at the end of each working day (regardless
of if the client had any transactions every day). That way, I would know how
much to invoice each client at the end of each week.

Hope this was clear enough, and that someone is able to help me out. I have
some knowledge of Access and know a little VBA. I just can't get my head
around this one.

Thanks

Baard

 

[Guest]

Sounds like you are on your way to a simple solution. I like the
positive/negative pallets idea.

Each week you can just sum the pallets by clientID.

I would think you might need to check for the existence of records entered
since the last invoice and handle them separately for pro-rating the charges.

 

[Van T. Dinh]

See a similar discussion on:

http://www.tinyurl.com/vhfej

 

[Guest]

Unfortunately, I can't just sum by ClientID, because that does not take into
account WHEN the oallets has gone in and out of the warehouse. I need to make
a sum of how many pallets are in the warehouse at the end of each day, not
figure out how many pallets are there at the end of the week.

thx

Baard

 

[Guest]

Try this
For this example, the invoice date is 10/13/2006
Strategy is to
1) take the [total pallets for the client at the start of the invoice week *
7]
2) calculate the net pallet activity for the client during the invoice week.
3) sum these two for the total pallet-days during the invoice week

Table Clients: field clientID
Table pallet_InOut: fields clientID, date, palletsInOut

1) Query palletsAtWeekStart:
SELECT pallet_InOut.clientID, Sum(pallet_InOut.palletsInOut * 7) AS
SumOfpalletsInOut
FROM pallet_InOut
WHERE (((pallet_InOut.date)<#10/7/2006#))
GROUP BY pallet_InOut.clientID;

2) Query palletsThisWeek:

SELECT pallet_InOut.clientID,
Sum(DateDiff("d",[pallet_InOut]![date],"10/13/2006")*[pallet_InOut]![palletsInOut]) AS palletDays
FROM pallet_InOut
WHERE (((pallet_InOut.date)>=#10/7/2006#))
GROUP BY pallet_InOut.clientID;

3) Query palletInvoice:

SELECT palletClients.clientID,
[palletsAtWeekStart]![SumOfpalletsInOut]+[pattetsThisWeek]![palletDays] AS
palletDays
FROM (palletClients INNER JOIN palletsAtWeekStart ON palletClients.clientID
= palletsAtWeekStart.clientID) INNER JOIN pattetsThisWeek ON
palletClients.clientID = pattetsThisWeek.clientID;

You should be able to parameterize the dates to be used in the WHERE clauses.

Does this get it ?

 

[Guest]

Wow, that worked like a charm.

I would like to thank you for taking the time to help me. The solution was
much more complex than I thought, and I would never have gotten there on my
own.

Thx again

Baard

Try this
For this example, the invoice date is 10/13/2006
Strategy is to
1) take the [total pallets for the client at the start of the invoice week *
7]
2) calculate the net pallet activity for the client during the invoice week.
3) sum these two for the total pallet-days during the invoice week

Table Clients: field clientID
Table pallet_InOut: fields clientID, date, palletsInOut

1) Query palletsAtWeekStart:
SELECT pallet_InOut.clientID, Sum(pallet_InOut.palletsInOut * 7) AS
SumOfpalletsInOut
FROM pallet_InOut
WHERE (((pallet_InOut.date)<#10/7/2006#))
GROUP BY pallet_InOut.clientID;

2) Query palletsThisWeek:

SELECT pallet_InOut.clientID,
Sum(DateDiff("d",[pallet_InOut]![date],"10/13/2006")*[pallet_InOut]![palletsInOut]) AS palletDays
FROM pallet_InOut
WHERE (((pallet_InOut.date)>=#10/7/2006#))
GROUP BY pallet_InOut.clientID;

3) Query palletInvoice:

SELECT palletClients.clientID,
[palletsAtWeekStart]![SumOfpalletsInOut]+[pattetsThisWeek]![palletDays] AS
palletDays
FROM (palletClients INNER JOIN palletsAtWeekStart ON palletClients.clientID
= palletsAtWeekStart.clientID) INNER JOIN pattetsThisWeek ON
palletClients.clientID = pattetsThisWeek.clientID;

You should be able to parameterize the dates to be used in the WHERE clauses.

Does this get it ?

Unfortunately, I can't just sum by ClientID, because that does not take into
account WHEN the oallets has gone in and out of the warehouse. I need to make
a sum of how many pallets are in the warehouse at the end of each day, not
figure out how many pallets are there at the end of the week.

thx

Baard