Subtotal for a record

[Rob Drummond, Jr]

In my database I track what components are needed for a repair. It is
possible to have as many as three components per record (serial number).
Each record has (for needed components) 1st/2nd/3rd part number, description,
and cost fields. How do I get a total for the three cost fields for each
record (serial number)? Every formula I have tried gives me a grand total of
all values in those fields across the entire report.

Here is what I would like to see (in brief) for each record

1st cost $100
2nd cost $50
3rd cost $25

total cost $175

I am using the total cost field to do the calculation and what I am getting
is the total for ALL 1st, 2nd, and 3rd costs across the entire report instead
of each record.

I have used =SUM([1st cost]+[2nd cost]+[3rd cost]) and tried basing it on
the report, the query used for the report, and the table used for the query.

 

[Duane Hookom]

Is it too late to fix (normalize) your table structure? IMHO, each cost
should be stored in its own record in a related table. I wouldn't go any
further until the structure is changed.

 

[Rob Drummond, Jr]

I have a separate table for the components needed for repair. That table is
then associated with the main data table with a system generated key. Or are
you recomending three tables, one for each possible component needed?

Is it too late to fix (normalize) your table structure? IMHO, each cost
should be stored in its own record in a related table. I wouldn't go any
further until the structure is changed.

--
Duane Hookom
Microsoft Access MVP

In my database I track what components are needed for a repair. It is
possible to have as many as three components per record (serial number).
Each record has (for needed components) 1st/2nd/3rd part number, description,
and cost fields. How do I get a total for the three cost fields for each
record (serial number)? Every formula I have tried gives me a grand total of
all values in those fields across the entire report.

Here is what I would like to see (in brief) for each record

1st cost $100
2nd cost $50
3rd cost $25

total cost $175

I am using the total cost field to do the calculation and what I am getting
is the total for ALL 1st, 2nd, and 3rd costs across the entire report instead
of each record.

I have used =SUM([1st cost]+[2nd cost]+[3rd cost]) and tried basing it on
the report, the query used for the report, and the table used for the query.

 

[Duane Hookom]

It sounds like you might have Serial Number as a primary key. If so, you
would have a new table for part cost like:

tblPartCosts
================
PartCostID autonumber primary key
SerialNumber
PartNumber
Cost

If you had 3 partnumbers for a single serial number, this would result in 3
records in the new table.
--
Duane Hookom
Microsoft Access MVP

I have a separate table for the components needed for repair. That table is
then associated with the main data table with a system generated key. Or are
you recomending three tables, one for each possible component needed?

Is it too late to fix (normalize) your table structure? IMHO, each cost
should be stored in its own record in a related table. I wouldn't go any
further until the structure is changed.

--
Duane Hookom
Microsoft Access MVP

In my database I track what components are needed for a repair. It is
possible to have as many as three components per record (serial number).
Each record has (for needed components) 1st/2nd/3rd part number, description,
and cost fields. How do I get a total for the three cost fields for each
record (serial number)? Every formula I have tried gives me a grand total of
all values in those fields across the entire report.

Here is what I would like to see (in brief) for each record

1st cost $100
2nd cost $50
3rd cost $25

total cost $175

I am using the total cost field to do the calculation and what I am getting
is the total for ALL 1st, 2nd, and 3rd costs across the entire report instead
of each record.

I have used =SUM([1st cost]+[2nd cost]+[3rd cost]) and tried basing it on
the report, the query used for the report, and the table used for the query.

 

[Rob Drummond, Jr]

The serial number is the key in the main data field. This database was
created by importing a spreadsheet so when the components table was created,
it was based on the 9 cells associated with each serial number. If I am
understanding correctly, I should edit that table to only have 4 items,
autonumber (as key), serial number, part number, and cost. For this, I will
have to use my data entry form to re-enter the component information to
repopulate the edited table. As I am at the very beginning of creating this
database, that is not an issue because I am only using a limited amount of
records for the creation. Once this is done, would I be able to import only
the relevant cells from the original spreadsheet to populate the edited
table, or would it be better to, as I said, use my data entry form to add the
information? Sorry to ask so many questions, but I am very new to database
design and this is the first on that has require calculations beyond basic
"grand totals" done with simply queries.

It sounds like you might have Serial Number as a primary key. If so, you
would have a new table for part cost like:

tblPartCosts
================
PartCostID autonumber primary key
SerialNumber
PartNumber
Cost

If you had 3 partnumbers for a single serial number, this would result in 3
records in the new table.
--
Duane Hookom
Microsoft Access MVP

I have a separate table for the components needed for repair. That table is
then associated with the main data table with a system generated key. Or are
you recomending three tables, one for each possible component needed?

Is it too late to fix (normalize) your table structure? IMHO, each cost
should be stored in its own record in a related table. I wouldn't go any
further until the structure is changed.

--
Duane Hookom
Microsoft Access MVP


:

In my database I track what components are needed for a repair. It is
possible to have as many as three components per record (serial number).
Each record has (for needed components) 1st/2nd/3rd part number, description,
and cost fields. How do I get a total for the three cost fields for each
record (serial number)? Every formula I have tried gives me a grand total of
all values in those fields across the entire report.

Here is what I would like to see (in brief) for each record

1st cost $100
2nd cost $50
3rd cost $25

total cost $175

I am using the total cost field to do the calculation and what I am getting
is the total for ALL 1st, 2nd, and 3rd costs across the entire report instead
of each record.

I have used =SUM([1st cost]+[2nd cost]+[3rd cost]) and tried basing it on
the report, the query used for the report, and the table used for the query.

 

[Rob Drummond, Jr]

Ok, I have edited the table with the component information to only have one
each of component, description, and cost. The association did not change and
seems to work. However, when I changed my data entry form to have all three
of the component, descrition, and cost fields point to the same place in the
components table, it automatically duplicates data entered into any of the
fields on the for in all three similiar fields. So the question I now have
is how do I tell the database that I want to create a new record in the
components table for the same serial number (the key in the main field). I
am guessing I can create a macro that can be tied to a button that will also
create new fields for data entry. Or is it possible to tell the existing
fields that they are for a new record for the table? The more I do this, the
less sense I am making.

The serial number is the key in the main data field. This database was
created by importing a spreadsheet so when the components table was created,
it was based on the 9 cells associated with each serial number. If I am
understanding correctly, I should edit that table to only have 4 items,
autonumber (as key), serial number, part number, and cost. For this, I will
have to use my data entry form to re-enter the component information to
repopulate the edited table. As I am at the very beginning of creating this
database, that is not an issue because I am only using a limited amount of
records for the creation. Once this is done, would I be able to import only
the relevant cells from the original spreadsheet to populate the edited
table, or would it be better to, as I said, use my data entry form to add the
information? Sorry to ask so many questions, but I am very new to database
design and this is the first on that has require calculations beyond basic
"grand totals" done with simply queries.

It sounds like you might have Serial Number as a primary key. If so, you
would have a new table for part cost like:

tblPartCosts
================
PartCostID autonumber primary key
SerialNumber
PartNumber
Cost

If you had 3 partnumbers for a single serial number, this would result in 3
records in the new table.
--
Duane Hookom
Microsoft Access MVP

I have a separate table for the components needed for repair. That table is
then associated with the main data table with a system generated key. Or are
you recomending three tables, one for each possible component needed?

:

Is it too late to fix (normalize) your table structure? IMHO, each cost
should be stored in its own record in a related table. I wouldn't go any
further until the structure is changed.

--
Duane Hookom
Microsoft Access MVP


:

In my database I track what components are needed for a repair. It is
possible to have as many as three components per record (serial number).
Each record has (for needed components) 1st/2nd/3rd part number, description,
and cost fields. How do I get a total for the three cost fields for each
record (serial number)? Every formula I have tried gives me a grand total of
all values in those fields across the entire report.

Here is what I would like to see (in brief) for each record

1st cost $100
2nd cost $50
3rd cost $25

total cost $175

I am using the total cost field to do the calculation and what I am getting
is the total for ALL 1st, 2nd, and 3rd costs across the entire report instead
of each record.

I have used =SUM([1st cost]+[2nd cost]+[3rd cost]) and tried basing it on
the report, the query used for the report, and the table used for the query.

 

[Duane Hookom]

What you are attempting to create is not much different than the Orders and
Order Details in the Northwind database. The order details are similar to
your separate costs and are displayed/edited in a continuous subform on a
main form.

You could have created the records in this table with a union query like:
SELECT SerialNumber, PartNumber1 as PartNumber, Cost1 as Cost
FROM tblOldTable
UNION ALL
SELECT SerialNumber, PartNumber2, Cost2
FROM tblOldTable
WHERE PartNumber2 Is Not Null
UNION ALL
SELECT SerialNumber, PartNumber3, Cost3
FROM tblOldTable
WHERE PartNumber3 Is Not Null;

This is what some of us refer to as a "normalizing union query".

--
Duane Hookom
Microsoft Access MVP

Ok, I have edited the table with the component information to only have one
each of component, description, and cost. The association did not change and
seems to work. However, when I changed my data entry form to have all three
of the component, descrition, and cost fields point to the same place in the
components table, it automatically duplicates data entered into any of the
fields on the for in all three similiar fields. So the question I now have
is how do I tell the database that I want to create a new record in the
components table for the same serial number (the key in the main field). I
am guessing I can create a macro that can be tied to a button that will also
create new fields for data entry. Or is it possible to tell the existing
fields that they are for a new record for the table? The more I do this, the
less sense I am making.

The serial number is the key in the main data field. This database was
created by importing a spreadsheet so when the components table was created,
it was based on the 9 cells associated with each serial number. If I am
understanding correctly, I should edit that table to only have 4 items,
autonumber (as key), serial number, part number, and cost. For this, I will
have to use my data entry form to re-enter the component information to
repopulate the edited table. As I am at the very beginning of creating this
database, that is not an issue because I am only using a limited amount of
records for the creation. Once this is done, would I be able to import only
the relevant cells from the original spreadsheet to populate the edited
table, or would it be better to, as I said, use my data entry form to add the
information? Sorry to ask so many questions, but I am very new to database
design and this is the first on that has require calculations beyond basic
"grand totals" done with simply queries.

It sounds like you might have Serial Number as a primary key. If so, you
would have a new table for part cost like:

tblPartCosts
================
PartCostID autonumber primary key
SerialNumber
PartNumber
Cost

If you had 3 partnumbers for a single serial number, this would result in 3
records in the new table.
--
Duane Hookom
Microsoft Access MVP


:

I have a separate table for the components needed for repair. That table is
then associated with the main data table with a system generated key. Or are
you recomending three tables, one for each possible component needed?

:

Is it too late to fix (normalize) your table structure? IMHO, each cost
should be stored in its own record in a related table. I wouldn't go any
further until the structure is changed.

--
Duane Hookom
Microsoft Access MVP


:

In my database I track what components are needed for a repair. It is
possible to have as many as three components per record (serial number).
Each record has (for needed components) 1st/2nd/3rd part number, description,
and cost fields. How do I get a total for the three cost fields for each
record (serial number)? Every formula I have tried gives me a grand total of
all values in those fields across the entire report.

Here is what I would like to see (in brief) for each record

1st cost $100
2nd cost $50
3rd cost $25

total cost $175

I am using the total cost field to do the calculation and what I am getting
is the total for ALL 1st, 2nd, and 3rd costs across the entire report instead
of each record.

I have used =SUM([1st cost]+[2nd cost]+[3rd cost]) and tried basing it on
the report, the query used for the report, and the table used for the query.

 

[Rob Drummond, Jr]

Fortunately, I have made all changes to a backup of my original database so
what you are suggesting is still possible. I will try this, but even if I
get it to work, my original problem still exists. I do I get a formula
(calculation) that gives me a total of cost 1, cost 2, and cost 3 for one
record as opposed to a grand total. One thing this will allow is for me to
account for more then three parts (although rare that more then that are
needed). I will look at the order and order detail in the Northwind database
as I try to do this and see if it makes sense to me. Thank you for all the
help.

What you are attempting to create is not much different than the Orders and
Order Details in the Northwind database. The order details are similar to
your separate costs and are displayed/edited in a continuous subform on a
main form.

You could have created the records in this table with a union query like:
SELECT SerialNumber, PartNumber1 as PartNumber, Cost1 as Cost
FROM tblOldTable
UNION ALL
SELECT SerialNumber, PartNumber2, Cost2
FROM tblOldTable
WHERE PartNumber2 Is Not Null
UNION ALL
SELECT SerialNumber, PartNumber3, Cost3
FROM tblOldTable
WHERE PartNumber3 Is Not Null;

This is what some of us refer to as a "normalizing union query".

--
Duane Hookom
Microsoft Access MVP

Ok, I have edited the table with the component information to only have one
each of component, description, and cost. The association did not change and
seems to work. However, when I changed my data entry form to have all three
of the component, descrition, and cost fields point to the same place in the
components table, it automatically duplicates data entered into any of the
fields on the for in all three similiar fields. So the question I now have
is how do I tell the database that I want to create a new record in the
components table for the same serial number (the key in the main field). I
am guessing I can create a macro that can be tied to a button that will also
create new fields for data entry. Or is it possible to tell the existing
fields that they are for a new record for the table? The more I do this, the
less sense I am making.

The serial number is the key in the main data field. This database was
created by importing a spreadsheet so when the components table was created,
it was based on the 9 cells associated with each serial number. If I am
understanding correctly, I should edit that table to only have 4 items,
autonumber (as key), serial number, part number, and cost. For this, I will
have to use my data entry form to re-enter the component information to
repopulate the edited table. As I am at the very beginning of creating this
database, that is not an issue because I am only using a limited amount of
records for the creation. Once this is done, would I be able to import only
the relevant cells from the original spreadsheet to populate the edited
table, or would it be better to, as I said, use my data entry form to add the
information? Sorry to ask so many questions, but I am very new to database
design and this is the first on that has require calculations beyond basic
"grand totals" done with simply queries.

:

It sounds like you might have Serial Number as a primary key. If so, you
would have a new table for part cost like:

tblPartCosts
================
PartCostID autonumber primary key
SerialNumber
PartNumber
Cost

If you had 3 partnumbers for a single serial number, this would result in 3
records in the new table.
--
Duane Hookom
Microsoft Access MVP


:

I have a separate table for the components needed for repair. That table is
then associated with the main data table with a system generated key. Or are
you recomending three tables, one for each possible component needed?

:

Is it too late to fix (normalize) your table structure? IMHO, each cost
should be stored in its own record in a related table. I wouldn't go any
further until the structure is changed.

--
Duane Hookom
Microsoft Access MVP


:

In my database I track what components are needed for a repair. It is
possible to have as many as three components per record (serial number).
Each record has (for needed components) 1st/2nd/3rd part number, description,
and cost fields. How do I get a total for the three cost fields for each
record (serial number)? Every formula I have tried gives me a grand total of
all values in those fields across the entire report.

Here is what I would like to see (in brief) for each record

1st cost $100
2nd cost $50
3rd cost $25

total cost $175

I am using the total cost field to do the calculation and what I am getting
is the total for ALL 1st, 2nd, and 3rd costs across the entire report instead
of each record.

I have used =SUM([1st cost]+[2nd cost]+[3rd cost]) and tried basing it on
the report, the query used for the report, and the table used for the query.

 

[Duane Hookom]

To create a total in a query, you would use a totals query:
SELECT SerialNumber, Sum(Cost) as TotCost
FROM tblNewOne
GROUP BY SerialNumber

In a report, you can group by SerialNumber and then add a text box to the
SerialNumber group header or footer section with a control source of:
=Sum([Cost])
--
Duane Hookom
Microsoft Access MVP

Fortunately, I have made all changes to a backup of my original database so
what you are suggesting is still possible. I will try this, but even if I
get it to work, my original problem still exists. I do I get a formula
(calculation) that gives me a total of cost 1, cost 2, and cost 3 for one
record as opposed to a grand total. One thing this will allow is for me to
account for more then three parts (although rare that more then that are
needed). I will look at the order and order detail in the Northwind database
as I try to do this and see if it makes sense to me. Thank you for all the
help.

What you are attempting to create is not much different than the Orders and
Order Details in the Northwind database. The order details are similar to
your separate costs and are displayed/edited in a continuous subform on a
main form.

You could have created the records in this table with a union query like:
SELECT SerialNumber, PartNumber1 as PartNumber, Cost1 as Cost
FROM tblOldTable
UNION ALL
SELECT SerialNumber, PartNumber2, Cost2
FROM tblOldTable
WHERE PartNumber2 Is Not Null
UNION ALL
SELECT SerialNumber, PartNumber3, Cost3
FROM tblOldTable
WHERE PartNumber3 Is Not Null;

This is what some of us refer to as a "normalizing union query".

--
Duane Hookom
Microsoft Access MVP

Ok, I have edited the table with the component information to only have one
each of component, description, and cost. The association did not change and
seems to work. However, when I changed my data entry form to have all three
of the component, descrition, and cost fields point to the same place in the
components table, it automatically duplicates data entered into any of the
fields on the for in all three similiar fields. So the question I now have
is how do I tell the database that I want to create a new record in the
components table for the same serial number (the key in the main field). I
am guessing I can create a macro that can be tied to a button that will also
create new fields for data entry. Or is it possible to tell the existing
fields that they are for a new record for the table? The more I do this, the
less sense I am making.

:

The serial number is the key in the main data field. This database was
created by importing a spreadsheet so when the components table was created,
it was based on the 9 cells associated with each serial number. If I am
understanding correctly, I should edit that table to only have 4 items,
autonumber (as key), serial number, part number, and cost. For this, I will
have to use my data entry form to re-enter the component information to
repopulate the edited table. As I am at the very beginning of creating this
database, that is not an issue because I am only using a limited amount of
records for the creation. Once this is done, would I be able to import only
the relevant cells from the original spreadsheet to populate the edited
table, or would it be better to, as I said, use my data entry form to add the
information? Sorry to ask so many questions, but I am very new to database
design and this is the first on that has require calculations beyond basic
"grand totals" done with simply queries.

:

It sounds like you might have Serial Number as a primary key. If so, you
would have a new table for part cost like:

tblPartCosts
================
PartCostID autonumber primary key
SerialNumber
PartNumber
Cost

If you had 3 partnumbers for a single serial number, this would result in 3
records in the new table.
--
Duane Hookom
Microsoft Access MVP


:

I have a separate table for the components needed for repair. That table is
then associated with the main data table with a system generated key. Or are
you recomending three tables, one for each possible component needed?

:

Is it too late to fix (normalize) your table structure? IMHO, each cost
should be stored in its own record in a related table. I wouldn't go any
further until the structure is changed.

--
Duane Hookom
Microsoft Access MVP


:

In my database I track what components are needed for a repair. It is
possible to have as many as three components per record (serial number).
Each record has (for needed components) 1st/2nd/3rd part number, description,
and cost fields. How do I get a total for the three cost fields for each
record (serial number)? Every formula I have tried gives me a grand total of
all values in those fields across the entire report.

Here is what I would like to see (in brief) for each record

1st cost $100
2nd cost $50
3rd cost $25

total cost $175

I am using the total cost field to do the calculation and what I am getting
is the total for ALL 1st, 2nd, and 3rd costs across the entire report instead
of each record.

I have used =SUM([1st cost]+[2nd cost]+[3rd cost]) and tried basing it on
the report, the query used for the report, and the table used for the query.