INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • 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.

Jobs

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

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

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.

Reply To This Thread

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

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

Resources

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close