In a Form: Show sums for all unique values

I

igorin

Hello,

I have a DB where I store all the data for the use of equipment.

I have the following values:

Date Area Equipment Quantity Total Hours Comments
03/27/09 Area 1 Excavator 2 26 asgftfe
03/27/09 Area 1 Excavator 1 7 jasjgs
03/27/09 Area 2 Excavator 1 12 jkla
03/27/09 Area 1 Crane 1 8 hhdkh
03/27/09 Area 3 Compactor 3 21 hch
03/28/09 Area 3 Crane 2 14 lkhjd
03/28/09 Area 3 Crane 1 3 gktut
03/28/09 Area 2 Excavator 3 27 jjweq
03/28/09 Area 1 Grader 2 11 tuitb

I need a form where I can type in the date into a text box and have all the
sums for the unique values for that date appear (taking into consideration
for "unique" the fields: date, area, equipment).

This means that for 03/27/09 the number of excavators to show for area 1
should be 3, with total hours 33.

So that if I type in 03/27/09, the form should display this:

Date Area Equipment Quantity Total Hours
03/27/09 Area 1 Excavator 3 33
03/27/09 Area 2 Excavator 1 12
03/27/09 Area 1 Crane 1 8
03/27/09 Area 3 Compactor 3 21

And if I type in 03/28/09, then the form should display this:

Date Area Equipment Quantity Total Hours
03/28/09 Area 3 Crane 3 17
03/28/09 Area 2 Excavator 3 27
03/28/09 Area 1 Grader 2 11

Please, help!
 
K

KARL DEWEY

Use a totals query with criteria for YourDate from unbound text box on your
form and a command button to call an event or macro to refresh the query.

The criteria would be --
CVDate([Forms]![YourFormName]![TextBoxName])
 
I

igorin

Dear Karl,

Thanks for the reply. I'm fairly new to Access and my job doesn't leave me
enough time to learn much about it on my own. So, I understand the bit about
the totals query but I have a few questions:

1) "YourDate" is supposed to be the name of the unbound text box, right?

2) Is "YourDate" also a field in the totals query?

3) I have never used a command button and have never run an event to refresh
a query.

Could you, please, explain a little bit more?

Thank you very much in advanced!

--
igor


KARL DEWEY said:
Use a totals query with criteria for YourDate from unbound text box on your
form and a command button to call an event or macro to refresh the query.

The criteria would be --
CVDate([Forms]![YourFormName]![TextBoxName])

igorin said:
Hello,

I have a DB where I store all the data for the use of equipment.

I have the following values:

Date Area Equipment Quantity Total Hours Comments
03/27/09 Area 1 Excavator 2 26 asgftfe
03/27/09 Area 1 Excavator 1 7 jasjgs
03/27/09 Area 2 Excavator 1 12 jkla
03/27/09 Area 1 Crane 1 8 hhdkh
03/27/09 Area 3 Compactor 3 21 hch
03/28/09 Area 3 Crane 2 14 lkhjd
03/28/09 Area 3 Crane 1 3 gktut
03/28/09 Area 2 Excavator 3 27 jjweq
03/28/09 Area 1 Grader 2 11 tuitb

I need a form where I can type in the date into a text box and have all the
sums for the unique values for that date appear (taking into consideration
for "unique" the fields: date, area, equipment).

This means that for 03/27/09 the number of excavators to show for area 1
should be 3, with total hours 33.

So that if I type in 03/27/09, the form should display this:

Date Area Equipment Quantity Total Hours
03/27/09 Area 1 Excavator 3 33
03/27/09 Area 2 Excavator 1 12
03/27/09 Area 1 Crane 1 8
03/27/09 Area 3 Compactor 3 21

And if I type in 03/28/09, then the form should display this:

Date Area Equipment Quantity Total Hours
03/28/09 Area 3 Crane 3 17
03/28/09 Area 2 Excavator 3 27
03/28/09 Area 1 Grader 2 11


Please, help!
Click to expand...
Click to expand...
 
K

KARL DEWEY

1) I said YourDate for the field in your table as the word Date is reserved
and may cause problems if used as field name.
2) See above.
3) Create a button, select Form Operators and Refresh Form Data.
Click to expand...
Create totals query --
SELECT [Date], [Area], [Equipment], Sum([Quantity]) AS SumOfQuanity,
Sum([Total Hours]) AS SumOfTotal_Hours
FROM YourTable
WHERE [Date] = CVDate([Forms]![YourFormName]![TextBoxName])
GROUP BY [Date], [Area], [Equipment];

Open form, enter date, click button.

igorin said:
Dear Karl,

Thanks for the reply. I'm fairly new to Access and my job doesn't leave me
enough time to learn much about it on my own. So, I understand the bit about
the totals query but I have a few questions:

1) "YourDate" is supposed to be the name of the unbound text box, right?

2) Is "YourDate" also a field in the totals query?

3) I have never used a command button and have never run an event to refresh
a query.

Could you, please, explain a little bit more?

Thank you very much in advanced!

--
igor


KARL DEWEY said:
Use a totals query with criteria for YourDate from unbound text box on your
form and a command button to call an event or macro to refresh the query.

The criteria would be --
CVDate([Forms]![YourFormName]![TextBoxName])

igorin said:
Hello,

I have a DB where I store all the data for the use of equipment.

I have the following values:

Date Area Equipment Quantity Total Hours Comments
03/27/09 Area 1 Excavator 2 26 asgftfe
03/27/09 Area 1 Excavator 1 7 jasjgs
03/27/09 Area 2 Excavator 1 12 jkla
03/27/09 Area 1 Crane 1 8 hhdkh
03/27/09 Area 3 Compactor 3 21 hch
03/28/09 Area 3 Crane 2 14 lkhjd
03/28/09 Area 3 Crane 1 3 gktut
03/28/09 Area 2 Excavator 3 27 jjweq
03/28/09 Area 1 Grader 2 11 tuitb

I need a form where I can type in the date into a text box and have all the
sums for the unique values for that date appear (taking into consideration
for "unique" the fields: date, area, equipment).

This means that for 03/27/09 the number of excavators to show for area 1
should be 3, with total hours 33.

So that if I type in 03/27/09, the form should display this:

Date Area Equipment Quantity Total Hours
03/27/09 Area 1 Excavator 3 33
03/27/09 Area 2 Excavator 1 12
03/27/09 Area 1 Crane 1 8
03/27/09 Area 3 Compactor 3 21

And if I type in 03/28/09, then the form should display this:

Date Area Equipment Quantity Total Hours
03/28/09 Area 3 Crane 3 17
03/28/09 Area 2 Excavator 3 27
03/28/09 Area 1 Grader 2 11

How can I do this?<<<

Please, help!
Click to expand...
Click to expand...
Click to expand...
 
I

igorin

Thank you very much for taking the time to help, Karl!

--
igor


KARL DEWEY said:
1) I said YourDate for the field in your table as the word Date is reserved
Click to expand...
and may cause problems if used as field name.
2) See above.
3) Create a button, select Form Operators and Refresh Form Data.
Click to expand...
Create totals query --
SELECT [Date], [Area], [Equipment], Sum([Quantity]) AS SumOfQuanity,
Sum([Total Hours]) AS SumOfTotal_Hours
FROM YourTable
WHERE [Date] = CVDate([Forms]![YourFormName]![TextBoxName])
GROUP BY [Date], [Area], [Equipment];

Open form, enter date, click button.

igorin said:
Dear Karl,

Thanks for the reply. I'm fairly new to Access and my job doesn't leave me
enough time to learn much about it on my own. So, I understand the bit about
the totals query but I have a few questions:

1) "YourDate" is supposed to be the name of the unbound text box, right?

2) Is "YourDate" also a field in the totals query?

3) I have never used a command button and have never run an event to refresh
a query.

Could you, please, explain a little bit more?

Thank you very much in advanced!

--
igor


KARL DEWEY said:
Use a totals query with criteria for YourDate from unbound text box on your
form and a command button to call an event or macro to refresh the query.

The criteria would be --
CVDate([Forms]![YourFormName]![TextBoxName])

:

Hello,

I have a DB where I store all the data for the use of equipment.

I have the following values:

Date Area Equipment Quantity Total Hours Comments
03/27/09 Area 1 Excavator 2 26 asgftfe
03/27/09 Area 1 Excavator 1 7 jasjgs
03/27/09 Area 2 Excavator 1 12 jkla
03/27/09 Area 1 Crane 1 8 hhdkh
03/27/09 Area 3 Compactor 3 21 hch
03/28/09 Area 3 Crane 2 14 lkhjd
03/28/09 Area 3 Crane 1 3 gktut
03/28/09 Area 2 Excavator 3 27 jjweq
03/28/09 Area 1 Grader 2 11 tuitb

I need a form where I can type in the date into a text box and have all the
sums for the unique values for that date appear (taking into consideration
for "unique" the fields: date, area, equipment).

This means that for 03/27/09 the number of excavators to show for area 1
should be 3, with total hours 33.

So that if I type in 03/27/09, the form should display this:

Date Area Equipment Quantity Total Hours
03/27/09 Area 1 Excavator 3 33
03/27/09 Area 2 Excavator 1 12
03/27/09 Area 1 Crane 1 8
03/27/09 Area 3 Compactor 3 21

And if I type in 03/28/09, then the form should display this:

Date Area Equipment Quantity Total Hours
03/28/09 Area 3 Crane 3 17
03/28/09 Area 2 Excavator 3 27
03/28/09 Area 1 Grader 2 11

How can I do this?<<<

Please, help!
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

Conditional Sum in a Form 7
Cannot verify XNPV with PV or HP-12C 4
Calculating Elapsed Time Over a Set Period 2
Is there an Excel formula to show the highest letter in a row? 1
counting values as they turn up in a column 4
Max Min Differnce in a Range 6
Form - Subform - Subforms (Help!) 2
Simulate MS Excel Workday & NetWorkday functions in MS Access 0

Top