INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Adding the total quantity of a type of record in a table to each record

 Forum Search FAQs Links MVPs

## Adding the total quantity of a type of record in a table to each record

(OP)
I have a table that contains four fields: Unit, Device, Qty, and LineNo
and I would like to add a fifth field that contains the total quantity
of Devices or Units of each specific type within that table to each record
such as total field in the example below.

In other words, There are a total of 20 Units 019933 and a total of
16 Devices 008117, hence the totals in the following example:

Unit	Device	Qty	LineNo	Total
019933		4	1	20
019933		8	5	20
019933		8	7	20
019935		2	6	 6
019935		4	20	 6
008117	8	 1	16
008117	8	19	16
015968	2	 3	 6
015968	4	14	 6
019838	2	15	 2 

Is there an easy way to add this total to each record in this fifth field?

Thanks

### RE: Adding the total quantity of a type of record in a table to each record

Hi,

Aggregations should not be stored in tables, rather calculated at run time.

Skip,

Just traded in my OLD subtlety...
for a NUance!

### RE: Adding the total quantity of a type of record in a table to each record

#### CODE -->

SELECT A.Unit, A.Device, A.Qty, A.LineNo, (Select sum(B.qty) from tblDevice as B where A.Device = B.Device or A.Unit = B.Unit) AS Total
FROM tblDevice AS A; 

### RE: Adding the total quantity of a type of record in a table to each record

(OP)
Thanks MajP

This works really well

I tried using the same method on another table that I have but it reports the totals on each line for all items for the particular grouping.

My SQL looks like this;

#### CODE -->

SELECT A.PC, A.SP, A.Qty, A.MySum, A.PreUsed, A.LineNO, A.Tend_SP, A.Tend_Lic, A.MLIC, A.MPC, (Select sum(B.Qty)
from PC_SP_Line_tbl as B where A.PC = B.PC or A.SP = B.SP) AS Total
FROM PC_SP_Line_tbl AS A; 

and the results look like this:
PC	SP		Qty	MySum	PreUsed	LineNO	Tend_SP	Tend_Lic	MLIC	MPC	Total
019933			4	4		6						26
019933			8	8		5						26
019933			8	8		27						26
019935			2	2		35						26
019935			4	4		20						26
019838		4	4		8						60
ENCRYPTED	4	4		9						60
019838		4	4		24						60
ENCRYPTED	4	4		25						60
008117		8	8		27						60
008117		8	8		31						60
105733		8	8		32		LC301, LC101, DMLC		60
019838		8	8		33						60
ENCRYPTED	8	8		34						60
019838		2	2		39						60
ENCRYPTED	2	2		40						60 

Not sure why this is so different from your example as the SQL looks like it follows the same format...

### RE: Adding the total quantity of a type of record in a table to each record

Do you own the source of this table, and can you redesign it? Some of your problem is that the table is not normalized. You should have one column ItemID and then another column for itemType (PC or SP) similary (Unit or Device). That would make your queries much easier. Fixing it would be easy using a normalization union query.

My guess is that the empty SP or PC fields are not null but have an empty string. Null cannot equal null, but I am wondering if empty string will equal and empty string thus giving the whole group. If you cannot normalize the table then make a union query to put PC and SP in one column and you could still keep the separate columns. Then do the sum on the normalized query.

### RE: Adding the total quantity of a type of record in a table to each record

(OP)
Thanks MajP

I can add an auto number ItemID field to my table but the reason for an individual field for PC and SP
as there are several of each of these types.

Or are you suggesting that I lump all PC and SP items in one field and then have another field
to differentiate one from another?

In other words this:
ID	Device	Type	Qty	Line
x	019933	PC	4	1
x	008117	SP	8	1 

Instead of what I currently have:

ID	PC	SP	Qty	Line
x	019933		4	1
x		008117	8	1 

The fields are text fields and they either contain a text value or they are Null but there are
an empty string as I am testing for NULL on these fields later on in my code.

Thanks again

### RE: Adding the total quantity of a type of record in a table to each record

#### Quote:

Or are you suggesting that I lump all PC and SP items in one field and then have another field
to differentiate one from another?
Yes. You Would have a pet table like this

#### CODE -->

PetName PetType
Fido     Dog
Molly    Cat
Rover    Dog
Snowball Cat

And not

DogName  CatName
Fido
Molly
Rover
Snowball 

### RE: Adding the total quantity of a type of record in a table to each record

However, this may work
A.PC = B.PC or A.SP = B.SP
to
(A.PC & A.SP) = (B.PC & B.SP)

### RE: Adding the total quantity of a type of record in a table to each record

I may even go further and do this:

PetName PetType
Fido      1
Molly     2
Rover     1
Snowball  2

Table: MyPetType
PetType  PetDescr
1        Dog
2        Cat
3        Crocodile

So adding a new type of pet is easy.

Have fun.

---- Andy

There is a great need for a sarcasm font.

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

#### Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!