Monthly Reports - Macro Count

L

Locobans

Hello community ^_^

I'm in the need of some help!

I got 2 questions that hopefully you guys can help me find the answer

First,
The function of the database is to store the information of printer
cartridges. We normally do storage count every month, so I need a report to
print out how many cartridges were used based on the count in the storage
room and the new count. So basiclly OldCount-NewCount.

NewCount: will be inputted by user.
Total Used: NewCount-OldCount
OldCount: after the calculation, it will then get the value from NewCount

How can I be able to do that in a form?

Second,
I would like to know how can I create a report that will ask me an StartDate
and an EndDate and depending on the info inserted it will display the
records. This is to display the TOTAL USED cartridges used per month.

Database Structure:
- Table (Storage) the table contains these fields: Manufacturer, Model,
Category (all referring to the cartridges) a field for Storage (I use this
for oldcount) and the Count field (we can use this as newcount), Total Used
(empty, but on the query I set it as Storage-Count) StartDate and EndDate
fields empty.

- Form (Storage) is based on the Table.

- Query (Storage) displays all fields from the table.

I really appreciate any help, and I'm willing to mail a copy of the database
if anybody wants to take a deeper look into it.

~Manny
 
L

Locobans

Sheridan:

Thanks a lot for your quick reply. I really appreciate it.

I followed your steps just as you told me...unfortunetly when I create the
form and the unbound text box...after setting its contrrolsource to what you
specify...I keep getting:

#Name?

As result....Also where I'm supposed to insert the if condition? the unbound
text > properties > event > which event? I used double click > where should I
use Code Builder/Expression Builder/Macro Builder?

Thanks,

KenSheridan via AccessMonster.com said:
Manny:

You should really break down your Storage table into 3 normalized tables,
Categories, Models and Stock say, a follows:

Ctaegories
….CategoryID (PK)
….Category

Models
….ModelID (PK)
….Model
….CategoryID

For the Stock table you only need the one column to record the stock count as
the previous cont will be in the row with the same ModelID and the latest
previous date counted. Recording the same value in two rows introduces
redundancy and the possibility of inconsistent data being entered, so the
table would be like this:

Stock:
….ModelID (part of composite PK)
….StockDate (part of composite PK)
….StockInHand

For the stock used over date range you can then use a query like this:

PARAMETERS
[Enter start date:] DateTime,
[Enter end date:] DateTime;
SELECT DISTINCT [Enter start date:] AS DateFrom,
[Enter end date:] AS DateTo, ModelID,
(SELECT StockInHand
FROM Stock As S2
WHERE S2.ModelID = S1.ModelID
AND S2.StockDate =
(SELECT MIN(Stockdate)
FROM Stock As S3
WHERE S3.ModelID = S1.ModelID
AND S3.Stockdate >=[Enter start date:]))
-(SELECT StockInHand
FROM Stock As S4
WHERE S4.ModelID = S1.ModelID
AND S4.StockDate =
(SELECT MAX(Stockdate)
FROM Stock As S5
WHERE S5.ModelID = S1.ModelID
AND S5.Stockdate <=[Enter end date:]))
AS StockUsed
FROM Stock AS S1;

This will give you the stock used per model over the date range defined by
the parameters even if there have been several intervening counts between the
start and ends of the range.

For the form first create another query, qryStockUsed say, this time without
parameters to give you the stock used per item since the latest previous
stock count:

SELECT S1.ModelID, S1.StockDate,
(SELECT StockInHand
FROM Stock As S2
WHERE S2.ModelID = S1.ModelID
AND S2.StockDate =
(SELECT MAX(Stockdate)
FROM Stock As S3
WHERE S3.ModelID = S1.ModelID
AND S3.Stockdate < S1.StockDate))-StockInHand
AS StockUsed
FROM Stock AS S1;

You can then look up the StckUsed value for the current ModelID and StockDate
in an unbound text box in a form based on the Stock table by setting its
ControlSource property to:

=DLookUp("StockUsed","qryStockUsed","ModelID = " & Nz([ModelID],0) & " And
Stockdate = #" & Format(Nz([StockDate],0),"yyyy-mm-dd") & "#")

To update the value in this text box, put the following in its Enter event
procedure:

If Me.Dirty Then
Me.Dirty = False
Me.Recalc
End If

Relying on the difference between two stock counts to give you the number
used does of course assume that nothing is added to stock, which is a rather
unrealistic scenario. If items are added to stock between two counts then
the values of the later count may well exceed the values of the former; in
fact they are likely to do so as normally restocking would be triggered by a
low count being reached. You could cater for this with the following table:

StockReceived
….ModelID (part of composite PK)
….DateReceived (part of composite PK)
….Quantity

The above queries would then be amended as follows:

SELECT S1.ModelID, S1.StockDate,
(SELECT StockInHand
FROM Stock As S2
WHERE S2.ModelID = S1.ModelID
AND S2.StockDate =
(SELECT MAX(Stockdate)
FROM Stock As S3
WHERE S3.ModelID = S1.ModelID
AND S3.Stockdate < S1.StockDate))
+NZ((SELECT SUM(Quantity)
FROM StockReceived
WHERE StockReceived.ModelID = S1.ModelID
AND StockReceived.DateReceived BETWEEN
(SELECT MAX(Stockdate)
FROM Stock As S4
WHERE S4.ModelID = S1.ModelID
AND S4.Stockdate < S1.StockDate)
AND S1.StockDate),0)
-StockInHand AS StockUsed
FROM Stock AS S1;


PARAMETERS
[Enter start date:] DateTime,
[Enter end date:] DateTime;
SELECT DISTINCT [Enter start date:] AS DateFrom,
[Enter end date:] AS DateTo, ModelID,
(SELECT StockInHand
FROM Stock As S2
WHERE S2.ModelID = S1.ModelID
AND S2.StockDate =
(SELECT MIN(Stockdate)
FROM Stock As S3
WHERE S3.ModelID = S1.ModelID
AND S3.Stockdate >=[Enter start date:]))
-(SELECT StockInHand
FROM Stock As S4
WHERE S4.ModelID = S1.ModelID
AND S4.StockDate =
(SELECT MAX(Stockdate)
FROM Stock As S5
WHERE S5.ModelID = S1.ModelID
AND S5.Stockdate <=[Enter end date:]))
+NZ((SELECT SUM(Quantity)
FROM StockReceived
WHERE StockReceived.ModelID = S1.ModelID
AND StockReceived.DateReceived BETWEEN
[Enter start date:] AND [Enter End date:]),0)
AS StockUsed
FROM Stock AS S1;

Note that in the StockReceived table a row should not be inserted with the
same DateReceived date as a StockDate in a row for that model in the Stock
table. This is because the stock count on that day could have been before or
after the stock count.

Also the initial rows in StockReceived for each item should be before the
first stock counts for the items to initialise the stock.

Ken Sheridan
Stafford, England
Hello community ^_^

I'm in the need of some help!

I got 2 questions that hopefully you guys can help me find the answer

First,
The function of the database is to store the information of printer
cartridges. We normally do storage count every month, so I need a report to
print out how many cartridges were used based on the count in the storage
room and the new count. So basiclly OldCount-NewCount.

NewCount: will be inputted by user.
Total Used: NewCount-OldCount
OldCount: after the calculation, it will then get the value from NewCount

How can I be able to do that in a form?

Second,
I would like to know how can I create a report that will ask me an StartDate
and an EndDate and depending on the info inserted it will display the
records. This is to display the TOTAL USED cartridges used per month.

Database Structure:
- Table (Storage) the table contains these fields: Manufacturer, Model,
Category (all referring to the cartridges) a field for Storage (I use this
for oldcount) and the Count field (we can use this as newcount), Total Used
(empty, but on the query I set it as Storage-Count) StartDate and EndDate
fields empty.

- Form (Storage) is based on the Table.

- Query (Storage) displays all fields from the table.

I really appreciate any help, and I'm willing to mail a copy of the database
if anybody wants to take a deeper look into it.

~Manny
 
L

Locobans

Sheridan:

Sorry I toke too long to answer back...

I just tried it and removed the break between the And and StockDate but
still giving me the same Name error...

Regarding the On Enter event...here's what I get...

"Run-time error '2455':
You entered an expression that has an invalid reference to the property
Dirty."

And when I run the queries I do get results...explain me this...

Does my tables needs to be in any relationships?

Table CATEGORIES: It has 2 fields "CategoryID" (PK) AutoNumber and
"Category" Text.
What's the use for this table?

Table MODELS: it has 3 fields "ModelID" (PK) Text, "Model" text and
"CategoryID" Number.
What's the use for this table? Here I will store the models for example...
1 - Epson blah blah - xxx
The "xxx" represents the CategoryID field...I'm supposed to input that? or
it's supposed to pull it from the categories table? how?

Table STOCK: it has 3 fields "ModelID" Text, "StockDate" Date/time and
"StockInHand" Number.
What's the use for this table? I will input data when doing my count? Let's
say it's time to count the cartridges in the storage room... for the Epson
3600 I got 4 cartridges so it should be like this
xxx - Date im doing the count - my count (actual number)
The "xxx" represents the ModelID field...once again I'm supposed to input it
or what?

Table STOCKRECEIVED: for this table...once again i'm supposed to input the
ModelID? notice that in all these tables ModelID is TEXT type...

Now let's say to get the report to see how many cartridges where used from
1/1/1 to 10/10/2010 I will run the query1 and insert those dates and the
result should be the field STOCKUSED?

I'm just trying to find the logic on the database...kinda getting confused...

Thanks,

KenSheridan via AccessMonster.com said:
Manny:

#Name means there is something in the ControlSource property that Access
isn't recognizing. Its working in a form I've created, so there is no syntax
error, so check that the names of the query and its columns are referred to
correctly.

One possibility is that the expression has been broken over two lines in
your newsreader (it breaks between And and StockDate in mine) and you've
copied and pasted it in either without removing the line break, or you've
done so but not left a space between And and StockDate, or wherever its
breaking in yours.

The code goes in the Enter event procedure of the unbound text box. If you
are not familiar with entering code in event procedures this is how its done:

Select the control in form design view and open its properties sheet if its
not already open. Then select the On Enter event property in the properties
sheet. Click on the 'build' button; that's the one on the right with 3 dots.
Select 'Code Builder' in the dialogue, and click OK. The VBA window will
open at the event procedure with the first and last lines already in place.
Enter the lines of code between these two existing lines.

One thing you haven't said is whether the queries are returning the results
you'd expect from your data? As far as I can see, with some dummy data I've
entered, they appear to be doing so.

Ken Sheridan
Stafford, England
Sheridan:

Thanks a lot for your quick reply. I really appreciate it.

I followed your steps just as you told me...unfortunetly when I create the
form and the unbound text box...after setting its contrrolsource to what you
specify...I keep getting:

#Name?

As result....Also where I'm supposed to insert the if condition? the unbound
text > properties > event > which event? I used double click > where should I
use Code Builder/Expression Builder/Macro Builder?

Thanks,
[quoted text clipped - 195 lines]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top