tracking daily production

[Brian]

I have 2 Excel spread sheets to track production in my office. One is for
daily activity, vouchers received and processed. The other is for timeliness
of the voucher production. I tried to combine the spreadsheets into 1 Access
database. I was able to get the timeliness function to work in the report.
But I am having problems with the production function. How can I have Access
take yesterday’s ending numbers, add today’s received, subtract today’s
processed and give me today’s ending number?

Thank you,

 

[Adien]

Make two queries to select the numbers

SELECT Sum([tblDailyInvoices].[InvAm]) AS Amount
WHERE [tblDailyInvoices].[Date]=Date()

Select [tblInvoiceLog].[InvTot] AS Old
Where [tblInvoiceLog].[Date] = Date()-1


txtEndingBal = DSum("Amount", "Qry_TodaysNumbers") +
("Old","Qry_OldNumbers")

That should do the trick

 

[Brian]

I'll give it a try and get back with you if it works or not.

Thank You

 

[Michael Gramelspacher]

I have 2 Excel spread sheets to track production in my office. One is for
daily activity, vouchers received and processed. The other is for timeliness
of the voucher production. I tried to combine the spreadsheets into 1 Access
database. I was able to get the timeliness function to work in the report.
But I am having problems with the production function. How can I have Access
take yesterday’s ending numbers, add today’s received, subtract today’s
processed and give me today’s ending number?

Thank you,

Something to consider. Possibly there is an easier way.

CREATE TABLE Vouchers
(processing_date DATETIME NOT NULL PRIMARY KEY
, vouchers_received INTEGER NOT NULL
, vouchers_processed INTEGER NOT NULL);

INSERT INTO Vouchers VALUES (#12/3/2007#, 97, 93);
INSERT INTO Vouchers VALUES (#12/4/2007#, 86, 87);
INSERT INTO Vouchers VALUES (#12/5/2007#, 101, 100);
INSERT INTO Vouchers VALUES (#12/6/2007#, 89, 90);

SELECT Vouchers.processing_date AS [Date],
Vouchers.vouchers_received AS Received,
Vouchers.vouchers_processed AS Processed,
NZ((SELECT SUM(t.vouchers_received) - SUM(t.vouchers_processed)
FROM Vouchers AS t
WHERE t.processing_date < Vouchers.processing_date))
+ Vouchers.vouchers_received - Vouchers.Vouchers_processed
AS [On Hand]
FROM Vouchers;

Date Received Processed On-hand
12/3/2007 97 93 4
12/4/2007 86 87 3
12/5/2007 101 100 4
12/6/2007 89 90 3

 

[Brian]

I’m still having problems, I had to modify what you suggested to the following.

SELECT Sum([tblTravelRecieved].[PCSReceived]) AS Amount
WHERE [tblTravelRecieved].[DateReceived]=Date()

SELECT Sum([tblTravelBlocks].[PCS]) AS PCSProcessed
WHERE [tblTravelBlocks].[DateProcessed]=Date()

SELECT [tblTravelRecieved].[PCSEnd] AS OLD
WHERE [tblTravelRecieved].[DateReceived]=Date()-1

txtPCSEnd = DSum(“Amountâ€, “Qry_TodaysNumbersâ€) + (“Oldâ€,â€Qry_OldNumbersâ€) –
(“PCSProcessedâ€,â€Qry_PCSProcessedâ€)

The vouchers we work on here are broken down into 7 types and I have 6
workers who process them.

tblTravelBlocks holds the values for the processed vouchers
tblTravelRecieved holds the values for vouchers received, I also have fields
for total processed and ending numbers.

I tried to create a new query in SQL view and would get an error when I
tried to save it. The error say “The SELECT statement includes a reserved
word or an argument name that is misspelled or missing or the punctuation is
incorrect.â€

 

[Adien]

I tried to create a new query in SQL view and would get an error when I

tried to save it. The error say "The SELECT statement includes a reserved
word or an argument name that is misspelled or missing or the punctuation is
incorrect."

The =Date() may not work in an SQL statement.
Another option woukld be to put two hidden fields on your form, add
the lines:
Let me.txtToday=Date()
Let me.txtYestday=Date()-1

Then change the Queries too

SELECT Sum([tblTravelRecieved].[PCSReceived]) AS Amount
WHERE [tblTravelRecieved].[DateReceived]=[FORMS]![Form Name Here].
[txtToday]


SELECT Sum([tblTravelBlocks].[PCS]) AS PCSProcessed
WHERE [tblTravelBlocks].[DateProcessed]=[FORMS]![Form Name Here].
[txtToday]


SELECT [tblTravelRecieved].[PCSEnd] AS OLD
WHERE [tblTravelRecieved].[DateReceived]=[FORMS]![Form Name Here].
[txtYesterday]

Let me know how that treats you

Adien

 

[Brian]

It is still not working. I created the hidden fields on the form and it does
create today and yesterday, but doesn't account for weekends. I tried to
create a Query in SQL view, followed the directions and recieved the same
error.

should I send you a copy so you can see what I see and then you can tell me
where I am messing up?

I tried to create a new query in SQL view and would get an error when I
tried to save it. The error say "The SELECT statement includes a reserved
word or an argument name that is misspelled or missing or the punctuation is
incorrect."

The =Date() may not work in an SQL statement.
Another option woukld be to put two hidden fields on your form, add
the lines:
Let me.txtToday=Date()
Let me.txtYestday=Date()-1

Then change the Queries too

SELECT Sum([tblTravelRecieved].[PCSReceived]) AS Amount
WHERE [tblTravelRecieved].[DateReceived]=[FORMS]![Form Name Here].
[txtToday]


SELECT Sum([tblTravelBlocks].[PCS]) AS PCSProcessed
WHERE [tblTravelBlocks].[DateProcessed]=[FORMS]![Form Name Here].
[txtToday]


SELECT [tblTravelRecieved].[PCSEnd] AS OLD
WHERE [tblTravelRecieved].[DateReceived]=[FORMS]![Form Name Here].
[txtYesterday]

Let me know how that treats you

Adien

 

[Adien]

yeah I'll take a look at it, send to (e-mail address removed)

 

[Brian]

emailed last night. Had to change the extention from .mdb to .doc, even
hotmail refuses to send .mdb files.

Thank you for your assistance.