RE: Subtotal for a record

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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.

"Duane Hookom" wrote:

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


"Rob Drummond, Jr" wrote:

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.

"Rob Drummond, Jr" wrote:

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.

"Duane Hookom" wrote:

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


"Rob Drummond, Jr" wrote:

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?

"Duane Hookom" wrote:

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


"Rob Drummond, Jr" wrote:

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.
.

Relevant Pages

  • RE: Subtotal for a record
    ... Order Details in the Northwind database. ... SELECT SerialNumber, PartNumber1 as PartNumber, Cost1 as Cost ... when I changed my data entry form to have all three ...
    (microsoft.public.access.reports)
  • Re: Having difficulty refactoring a DB application
    ... > I have a sensible database definition that more or less works for the ... OO is all about minimizing cost of certain future changes. ... the adapter itself - for example ensuring that the middle layer does ...
    (comp.object)
  • RE: .NET equivalent commands in Access VBA
    ... The true cost of software is not the cost to build it, ... the database is already split in that some of the data I read is ... I think I can call a command prompt from within Access so I ... Can I use .NET commands in Access? ...
    (microsoft.public.access.modulesdaovba)
  • Re: Multiplicity, Change and MV
    ... and SQL Server were making enough gains in tackling some of the ... seeing significant cost savings when they use products such as U2 from ... reading the database (unless they suffer the cost of the SQL-MV ... "upgrade" to a SQL DBMS to get the missing features, ...
    (comp.databases.theory)
  • RE: Subtotal for a record
    ... To create a total in a query, you would use a totals query: ... SELECT SerialNumber, Sumas TotCost ... SELECT SerialNumber, PartNumber1 as PartNumber, Cost1 as Cost ...
    (microsoft.public.access.reports)