sum, dsum or iif

[Jay]

I used a ct query to create a form my OrderDetails, Depots and Category
tables. The form has the fields: Order No, New Tyres, Retreads, Breakdowns,
Wheel Align, Sundries and Wages(all categories), as wel as a field called
Depots.
In the form footer I've managed to put textboxes with running totals for
each category. However, I have three depots(PRL, PE and NAM) and I want to
put running totals for each category, but by depot. I've tried dsum, iif, etc
and are busy losing the plot slowly but surely.... PLS help

 

[Allen Browne]

From your description, I take it that you are handling tyre sales and
installation jobs. It would seem to me that you would need tables like this:

Job (a header record for each job you take on) with fields:
- JobID AutoNumber primary key
- ClientID relates to a table of clients
- JobDate Date/Time when this work was requested
- DepotID relates to a table of depots

JobDetail (the line items in a job), with fields:
- JobDetailID AutoNumber primary key
- JobID relates to Job table (which job this line item belongs
to)
- CategoryID relates to a table of cagegories
- Quantity Number how many
- UnitPrice Currency how much each

The Depot table will have 3 records: "PRL", "PE", ...
The Category table will have 5 records: "New tyre", "Retread", ...

I have assumed that all the work in any one job is performed in *one* depot?
If not, move the DepotID field into the JobDetail table.

The crucial thing here is that you have a *record* for each charge in the
JobDetail table, rather than a field for each category. This makes it so
much easier to sum the charges per depot (as well as many other types of
queries.)

In the end, if you do need to display the data in the way you currently have
it, you would use a crosstab query where:
- Quantity * UnitPrice is the Value (Sum)
- CategoryID is the Column Heading (group by)

 

[Jay]

From your description, I take it that you are handling tyre sales and
installation jobs. It would seem to me that you would need tables like this:

Job (a header record for each job you take on) with fields:
- JobID AutoNumber primary key
- ClientID relates to a table of clients
- JobDate Date/Time when this work was requested
- DepotID relates to a table of depots

JobDetail (the line items in a job), with fields:
- JobDetailID AutoNumber primary key
- JobID relates to Job table (which job this line item belongs
to)
- CategoryID relates to a table of cagegories
- Quantity Number how many
- UnitPrice Currency how much each

The Depot table will have 3 records: "PRL", "PE", ...
The Category table will have 5 records: "New tyre", "Retread", ...

I have assumed that all the work in any one job is performed in *one* depot?
If not, move the DepotID field into the JobDetail table.

The crucial thing here is that you have a *record* for each charge in the
JobDetail table, rather than a field for each category. This makes it so
much easier to sum the charges per depot (as well as many other types of
queries.)

In the end, if you do need to display the data in the way you currently have
it, you would use a crosstab query where:
- Quantity * UnitPrice is the Value (Sum)
- CategoryID is the Column Heading (group by)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.



I manage a tyre bay a transporting firm. We have three depots like I mentioned. I'm using the database to keep track of all my orders issued(so I have an Orders + Order Details tables). I have separate tables for Categories, Depots, and Suppliers. The thing is, I want to be able to make a report(Tabular form layout) with the clolumns: Date : Order No : New tyres : Retreads : Breakdowns : Wages : Sundries : Depot

At the bottom of each page I'd like to have the total for each
column(category) for that page. I would then like to go further, and do the
same, but for each depot. In other words there would be three boxes at the
bottom of the page totaling New Tyres, but the one would only calculate
Orders relating to the PRL depot, the other box only the PE depot, etc.
Iwould like to do this for all the categories.

 

[Allen Browne]

Okay, I take it that you are not interested in using normalized tables (or
perhaps you already have them and your reference to 'ct' in your original
post stood for crosstab.)

In any case, you can use the Sorting And Grouping box in report design view
to create a group header and group footer on the Depot field, and sum the
values in the group footer. The Control Source of the sum of Retreads text
box would be:
=Sum([Retreads])

Once again, this assumes that the Retreads field contains some kind of
numeric or currency data.

I'm not sure I have understood your question.

 

[Jay]

Okay, I take it that you are not interested in using normalized tables (or
perhaps you already have them and your reference to 'ct' in your original
post stood for crosstab.)

In any case, you can use the Sorting And Grouping box in report design view
to create a group header and group footer on the Depot field, and sum the
values in the group footer. The Control Source of the sum of Retreads text
box would be:
=Sum([Retreads])

Once again, this assumes that the Retreads field contains some kind of
numeric or currency data.

I'm not sure I have understood your question.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I manage a tyre bay a transporting firm. We have three depots like I
mentioned. I'm using the database to keep track of all my orders
issued(so I have an Orders + Order Details tables). I have separate
tables for Categories, Depots, and Suppliers.

The thing is, I want to be able to make a report(Tabular form layout)
with the clolumns:
Date : Order No : New tyres : Retreads : Breakdowns : Wages :
Sundries : Depot

At the bottom of each page I'd like to have the total for each column
(category) for that page. I would then like to go further, and do the
same, but for each depot. In other words there would be three
boxes at the bottom of the page totaling New Tyres, but the one
would only calculate Orders relating to the PRL depot, the other box
only the PE depot, etc. I would like to do this for all the categories.

Hi agian. Sorry, reading thru it all again i can see I was a bit vague. OK, so I've got tables: Orders, OrderDetails, Categories and Depots. I then used a cross tab query, like u said, on which i based my form + report. I have already tried what u suggested,(=sum([Retreads]) and it works fine in the group footer. As soon as I try the same in the page footer i either get an #error value, or a zero. I've also checked the running sum properties, and tried all the possibilities there, with no luck

I used to do this before, but in Excel. So in each line there would be the
date, an Order No, a value in the respective category cloumn, and lastlly the
Depot to which the order was allocated. The "Running Totals" would be at the
end of each page, etc. I would like to keep "the look" of my Excel layout,
in Access.

 

[Allen Browne]

Ah: so you are trying to sum into the page footer?

See:
Reports: Page Totals
at:
http://allenbrowne.com/ser-11.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Okay, I take it that you are not interested in using normalized tables
(or
perhaps you already have them and your reference to 'ct' in your original
post stood for crosstab.)

In any case, you can use the Sorting And Grouping box in report design
view
to create a group header and group footer on the Depot field, and sum the
values in the group footer. The Control Source of the sum of Retreads
text
box would be:
=Sum([Retreads])

Once again, this assumes that the Retreads field contains some kind of
numeric or currency data.

I'm not sure I have understood your question.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I manage a tyre bay a transporting firm. We have three depots like I
mentioned. I'm using the database to keep track of all my orders
issued(so I have an Orders + Order Details tables). I have separate
tables for Categories, Depots, and Suppliers.

The thing is, I want to be able to make a report(Tabular form layout)
with the clolumns:
Date : Order No : New tyres : Retreads : Breakdowns : Wages :
Sundries : Depot

At the bottom of each page I'd like to have the total for each column
(category) for that page. I would then like to go further, and do the
same, but for each depot. In other words there would be three
boxes at the bottom of the page totaling New Tyres, but the one
would only calculate Orders relating to the PRL depot, the other box
only the PE depot, etc. I would like to do this for all the
categories.

Hi agian. Sorry, reading thru it all again i can see I was a bit vague.
OK, so I've got tables: Orders, OrderDetails, Categories and Depots. I
then used a cross tab query, like u said, on which i based my form +
report. I have already tried what u suggested,(=sum([Retreads]) and it
works fine in the group footer. As soon as I try the same in the page
footer i either get an #error value, or a zero. I've also checked the
running sum properties, and tried all the possibilities there, with no
luck

I used to do this before, but in Excel. So in each line there would be the
date, an Order No, a value in the respective category cloumn, and lastlly
the
Depot to which the order was allocated. The "Running Totals" would be at
the
end of each page, etc. I would like to keep "the look" of my Excel
layout,
in Access.

 

[Jay]

Hi, I'm fairly new to MS Access(u probably geussed). I've followed the link
and found a gold mine. Thanks a LOT. I'm "self taught" and I've only had the
guts so far to play arround with querries, relationships, etc., but haven't
really had the nerve to try my hand at SQL yet. I'm gonna fully check out
your site, and who knows. Thanks again, you've helped a lot.

Ah: so you are trying to sum into the page footer?

See:
Reports: Page Totals
at:
http://allenbrowne.com/ser-11.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Okay, I take it that you are not interested in using normalized tables
(or
perhaps you already have them and your reference to 'ct' in your original
post stood for crosstab.)

In any case, you can use the Sorting And Grouping box in report design
view
to create a group header and group footer on the Depot field, and sum the
values in the group footer. The Control Source of the sum of Retreads
text
box would be:
=Sum([Retreads])

Once again, this assumes that the Retreads field contains some kind of
numeric or currency data.

I'm not sure I have understood your question.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

"Jay" <[email protected]> replied in message
I manage a tyre bay a transporting firm. We have three depots like I
mentioned. I'm using the database to keep track of all my orders
issued(so I have an Orders + Order Details tables). I have separate
tables for Categories, Depots, and Suppliers.

The thing is, I want to be able to make a report(Tabular form layout)
with the clolumns:
Date : Order No : New tyres : Retreads : Breakdowns : Wages :
Sundries : Depot

At the bottom of each page I'd like to have the total for each column
(category) for that page. I would then like to go further, and do the
same, but for each depot. In other words there would be three
boxes at the bottom of the page totaling New Tyres, but the one
would only calculate Orders relating to the PRL depot, the other box
only the PE depot, etc. I would like to do this for all the
categories.

Hi agian. Sorry, reading thru it all again i can see I was a bit vague.
OK, so I've got tables: Orders, OrderDetails, Categories and Depots. I
then used a cross tab query, like u said, on which i based my form +
report. I have already tried what u suggested,(=sum([Retreads]) and it
works fine in the group footer. As soon as I try the same in the page
footer i either get an #error value, or a zero. I've also checked the
running sum properties, and tried all the possibilities there, with no
luck

I used to do this before, but in Excel. So in each line there would be the
date, an Order No, a value in the respective category cloumn, and lastlly
the
Depot to which the order was allocated. The "Running Totals" would be at
the
end of each page, etc. I would like to keep "the look" of my Excel
layout,
in Access.