Very Basic Query

[danian]

I have written a basic query that looks at the date field, department field and cost the cost field on a table. What I would like is a form that you can chose a date (19/09/2005) and a it looks at the table and calculates the total sum of money for each department for this date. Ie.

Date: 19/09/2005

Dept 1 = £123.00
Dept 2 = £1234.00
Dept 3 = £123.00
Dept 4 = £1234.00
Dept 5 = £123.00
Dept 6 = £1234.00

I can get the query to work but only 1 department at a time. Any ideas?

I have attached the DB

Thanks
D

 

[neileg]

Just take the criterion off the Department field and your query returns the totals for all departments. If you want to show this in a form, you need a continuous form.

Some Observations:
Not sure why you are holding all the product information in tblSalesTransactions.
Don't use VBA reserved words as field names. The field called Date will get you into trouble sooner or later.
Why are you hodling date and time as separate text fields? A date/time field would be more appropriate.
Just calling a field ID is not very helpfull to yourself. It is convention that you include the table name, eg SalesTransactionID

 

[danian]

What is a continuous form and how do create one?

D

 

[neileg]

A continuous form is one that shows multiple records in the Detail section. If you build a form through the wizard, choosing tabular format produces a continuous form. The form property Default View allows you to choose continuous in an existing form.

 

[danian]

Thanks for the reply. I did what you said and it worked. the only problem i have is with changing the date. I have created a textbox and called it txtEnterDate. What i need to happen is when the date is entered into the textbox it changes the query date. I have attached DB. Look at form frmSalesFigures.

Thanks
D

 

[danian]

no one want help!
D

 

[neileg]

Remember this?

Some Observations:
Don't use VBA reserved words as field names. The field called Date will get you into trouble sooner or later.
Why are you holding date and time as separate text fields? A date/time field would be more appropriate.

You need to rename the field called Date, say TransDate, and change its datatype to Date/Time.
In the AfterUpdate event of txtEnterDate add the code:
Me.Requery

Then it works. See attached