Trying to create a total

[Guest]

I have three columns: truck ID, Invoice Date, and Amount. I am trying to create a query that will give me one line with the following information : the truck ID, (all of one month), and the sum of invoice amounts. I am wanting a query to figure out cost per month. In my table I have all of our trucks the invoice dates of maintenance and the invoice amount. For some reason when I take a specific truck and one month (i.e. all of January) and the amounts for all of htose invoice I can't seem to get it to create the sum. Any suggestions would be greatly appreciated.

Thanks!!

Kelli

 

[Nikos Yannacopoulos]

Kelli,

What you need is a plain select query with the Totals function turned on.
Start making a query on your table, get the truckID and Amount fields down
to the grid, then add two calculated ones like:

InvMonth: Month([Invoice Date])
InvYear: Year([Invoice Date])

These two will return the month and year respectivelyy, so you can use them
to filter on for a specific month or range of months.
Next, go to menu View > Totals, and notice the new line that appears in the
grid, headed Total:, defualted Group By for all fields. Leave Group By under
Truck ID, change it to Sum under amount, and leave it to Group By under the
two calculated fields if you want them to show, or change it to Where if you
don't.

HTH,
Nikos

I have three columns: truck ID, Invoice Date, and Amount. I am trying to

create a query that will give me one line with the following information :
the truck ID, (all of one month), and the sum of invoice amounts. I am
wanting a query to figure out cost per month. In my table I have all of our
trucks the invoice dates of maintenance and the invoice amount. For some
reason when I take a specific truck and one month (i.e. all of January) and
the amounts for all of htose invoice I can't seem to get it to create the
sum. Any suggestions would be greatly appreciated.

 

[Guest]

Thank you for your response. I think I understand everything except I am not positive on how to add the two calculated ones. If you could explain the process on how to do that, that would be great.

Thanks again,

Kelli

Kelli,

What you need is a plain select query with the Totals function turned on.
Start making a query on your table, get the truckID and Amount fields down
to the grid, then add two calculated ones like:

InvMonth: Month([Invoice Date])
InvYear: Year([Invoice Date])

These two will return the month and year respectivelyy, so you can use them
to filter on for a specific month or range of months.
Next, go to menu View > Totals, and notice the new line that appears in the
grid, headed Total:, defualted Group By for all fields. Leave Group By under
Truck ID, change it to Sum under amount, and leave it to Group By under the
two calculated fields if you want them to show, or change it to Where if you
don't.

HTH,
Nikos

I have three columns: truck ID, Invoice Date, and Amount. I am trying to

create a query that will give me one line with the following information :
the truck ID, (all of one month), and the sum of invoice amounts. I am
wanting a query to figure out cost per month. In my table I have all of our
trucks the invoice dates of maintenance and the invoice amount. For some
reason when I take a specific truck and one month (i.e. all of January) and
the amounts for all of htose invoice I can't seem to get it to create the
sum. Any suggestions would be greatly appreciated.

Thanks!!

Kelli

 

[Nikos Yannacopoulos]

Kelli,

In your query design view, you go to the first empty column in the grid ,
put the cursor in the first row (headed Field and type or paste in the
expression:
InvMonth: Month([Invoice Date])
then likewise with
InvYear: Year([Invoice Date])
then set the appropriate Total function for them.

HTH,
Nikos

Thank you for your response. I think I understand everything except I am

not positive on how to add the two calculated ones. If you could explain the
process on how to do that, that would be great.

Thanks again,

Kelli

Kelli,

What you need is a plain select query with the Totals function turned on.
Start making a query on your table, get the truckID and Amount fields down
to the grid, then add two calculated ones like:

InvMonth: Month([Invoice Date])
InvYear: Year([Invoice Date])

These two will return the month and year respectivelyy, so you can use them
to filter on for a specific month or range of months.
Next, go to menu View > Totals, and notice the new line that appears in the
grid, headed Total:, defualted Group By for all fields. Leave Group By under
Truck ID, change it to Sum under amount, and leave it to Group By under the
two calculated fields if you want them to show, or change it to Where if you
don't.

HTH,
Nikos

I have three columns: truck ID, Invoice Date, and Amount. I am trying

to
create a query that will give me one line with the following information :
the truck ID, (all of one month), and the sum of invoice amounts. I am
wanting a query to figure out cost per month. In my table I have all of our
trucks the invoice dates of maintenance and the invoice amount. For some
reason when I take a specific truck and one month (i.e. all of January) and
the amounts for all of htose invoice I can't seem to get it to create the
sum. Any suggestions would be greatly appreciated.

Thanks!!

Kelli