Monthly totals on a form 1

[MinnKota]

I have a form that auto-generates the date using the now() function. It is basically a tally sheet on which the users record walk-in customers, and products every day. I have a unbound controls on the form that I want to display the totals for the month in each category.

I assume that I need to use the Month() function somehow...

Thanks,

 

[PHV]

What about a listbox with RowSource something like this:
="SELECT theCategory,Count(*) AS Total FROM theTable" _
& " WHERE 100*Year(theDate)+Month(theDate)=100*Year(Now)+Month(Now)" _
& " GROUP BY theCategory"
If instead of counting records you have to sum amounts, simply replace the Count(*) by Sum(theColumnName)

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[willir]

MinnKota

A little light on detail, so I will have to improvise.

TRANSFORM Sum(T.YourTransActionField) AS SumOfYourTransactionField
SELECT T.YourGroup
FROM T.YourTable AS T
WHERE Month([YourDateField])=Month(Date())
GROUP BY T.YourGroup
PIVOT Month([YourDateField]);

YourTransactionField refers to the field where you want to base the totals. I am not sure if this is a "sale" or "count".

(This was built using the query builder. Other select statements will also work.)

As an FYI, DATE() is generally better for handling dates than NOW() which returns the date and time.

Richard

 

[MinnKota]

Sorry for the lack of info.

If possible, I would like to base the control source for a text box control [walkinstotal] on a DSUM function. The table name is [TALLY] and the field in the table for which I want totals is [walk ins]. The user enters how many walk-in customers each day. I want to display the total number of walk-ins for the current month. I have a date field in my table that is called [Date].

 

[PHV]

If possible, I would like to base the control source for a text box control [walkinstotal] on a DSUM function. The table name is [TALLY] and the field in the table for which I want totals is [walk ins]. The user enters how many walk-in customers each day. I want to display the total number of walk-ins for the current month. I have a date field in my table that is called [Date].
Something like this ?
DSum("[walk ins]", "TALLY", "Format([Date],""yyyymm"")=Format(Date(),""yyyymm"")"

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[OhioSteve]

the month() and year() functions both return integers. For example, month(#1/1/1999#)=1 and year(#1/1/2000#)=2000. So if you want to do a count(), sum(), or Dsum() for a certain month, its really no big deal. Its just like any other criteria involving integers.

As others have indicated, you must test on both month and year to get a particular month.

 

[MinnKota]

PHV,
I tried your DSUM method, but I just get "#Name?" in the text box. Also, OhioSteve is right. I need to check for both month and year, but how do I do this? I thought about adding the numbers but then january 2002 would equal february 2001...

 

[OhioSteve]

Here is a query that finds the sum of field "b" based on the date field "a".

SELECT Sum(Table1.b) AS total
FROM Table1
WHERE (((Month([a]))=1) AND ((Year([a]))=1999));

This particular query totals the records for January 1999.

 

[MinnKota]

OhioSteve,

I have been able to construct such a query, but I am not sure how to bind the control source of a text box to a query....

 

[MinnKota]

I think that I have decided to go the query route...and add the query to my form's control source. The query looks like this

SELECT Sum([Tally].[Walk ins]) AS [Sum Of Walk ins], Sum([Tally].[Parts]) AS [Sum Of Parts]
FROM Tally
WHERE (((Month([date]))=Month(Date())) AND ((Year([date]))=Year(Date())));

 

[OhioSteve]

If you don't want the form to have a control source, there are many other ways to get the value into the field. I would probably do the calculations in a query. Then I would refer to the query in a DLookup()...

=DLookUp("[total]","query6")


Remember that a query can have unbound fields in a form as criteria. This is very helpful if the user will be choosing the parameters.

 

[MinnKota]

OhioSteve,

This works well for my purposes. The query that I was using would not allow me to add new records with my form...I wonder why that was?

 

[OhioSteve]

Generally, if a select query is your control source then you CAN add records. But sometimes this is not true. The rules on this issue are, well, not really really rules in my view.

Maybe one of the other members can enlighten us on this.