Subtotals

  • Thread starter Thread starter gary
  • Start date Start date
G

gary

My file contains 737,768 records.

The first 9 characters is the part-number;
the next 9 characters is the price;
the next 7 characters is the tax;
the last 4 characters is the fee.

The records are in ascending part-number order.
The part-number may occur in one or or multiple records.

I've been told that Access 2007 would be better to use than Excel 2007
to compute, for each part-number, the total price amount, the total
tax amount and the total fee amount.

How do I do this?

=============

The records look like this:

00971530800000404200004042000
00971530800000660000006600000
00971531100000805600008052000
00971531200001198200011982000
00971532500000538000005382000
00971532900002169000021682000
00971533500001038400010382000
00971533600000729400007282000
00971533700000836000008362000
00971533900002230600022302000
00971534200000640600006402000
00971534500001645300016452000
00971534700002187000021862000
00971534800001073200010732000
00971535200002658200026582000
00971535300001665000016642000
00971535300000458600004580000
00971536000000000000000000000
00971536500003280600032802000
00971536700007092400070922000
00971537500000978900009782000
00971537500000657600006570000
00971537500000013200000130000
00971537700003330600033302000
00971538300001807600018062000
 
Hi Gary,

Create a table named Records with the following four fields:

FieldName Data Type
ConcatData Text (29 characters)
Price Currency
Tax Currency
Fee Currency

Remove the default value of zero from the three currency fields. Save the
table.

Import your concatenated data into the ConcatData field in this table.
Create the following update query to split this data into the appropriate
fields. I'm not running Access 2007 right now, so the following directions
are for Access 2003. There may be slight differences.

1.) Create a new query. Dismiss the Add Table dialog without adding any
tables.
2.) Click on View | SQL View to change to the SQL view. You should see the
key word SELECT highlighted. Clear this keyword from the SQL View. Copy and
paste the following SQL statement into the SQL View:

UPDATE Records
SET Records.PartNumber = Left$([ConcatData],9),
Records.Price = Mid([ConcatData],10,9),
Records.Tax = Mid([ConcatData],19,7),
Records.Fee = Mid([ConcatData],26,4);

You can save this query with whatever name you like, for example
qupdRecords. Run the query. Hopefully, it will split all 737,768 records
without generating any errors (it works fine when using the 25 test records
you supplied).

3.) Create another new query in the same way, by switching to SQL View. Copy
and paste the following SQL statement:

SELECT Records.PartNumber,
Sum(Records.Price) AS SumOfPrice,
Sum(Records.Tax) AS SumOfTax,
Sum(Records.Fee) AS SumOfFee
FROM Records
GROUP BY Records.PartNumber
ORDER BY Records.PartNumber;

You can now switch to the more familiar design view by clicking on View |
Design View (again, this is valid for Access 2003--there must be a ribbon
equivalent to switch to design view). When you run this query, it should show
you a grouped total for each part number.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
Removing the default value is really not critical to getting this to work,
however, in Access 2003 you would open the table in design view, select the
field of interest, and then hit the F6 button to toggle to the lower window.
Here, you should see a default value property on the General tab. I don't
have Access 2007 available right now, so I cannot give you a definitive
answer for this newest version.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
Back
Top