Need help creating a tables

[celestedeeva]

Hello All,

First off many thanks for any help provided.

I have one table as follows:

Unique ID Purchase Type
1 a
1 b
1 c
2 a
3 a
3 b
4 a
5 a
5 b
5 c

Table 1 - need a query to outline whereby we need to sum the total purchases per unique ID and give me a total of all unique ID's with that many purchases so related to the table above I would need a table created outlined as below:

Purchases QTY
1 2
2 1
3 2

Table 2 - need a query giving me the totals of the purchase type to look as follows:

Purchase Type QTY
a 5
b 3
c 2

Table 3 - need a query giving me the combinations of purchases and a quantity for that combiantion based on the number of unique ID's that would hold it:

Combination QTY
a 2
a,b 1
a,b,c 2

As mentioned any help is greatly appreciated - I am filling in for someone and I am not familiar enough with SQL in order to get done.

I think it is also important to note that there could be duplciate purchase types as well so we may have:

Unique ID Purchase Type
1 a
1 a
1 b
1 c
2 a
2 a
3 a
3 b
3 b
4 a
5 a
5 b
5 c

Where in all cases I only need to sum the QTY's on unique purchase type so the first table request would still be created as:

Purchases QTY
1 2
2 1
3 2

Second table would still be created as:

Purchase Type QTY
a 5
b 3
c 2

Third table would still be created as:

Combination QTY
a 2
a,b 1
a,b,c 2

Thanks so much.

Celeste.

 

[dgillz]

So do you really need a table for this? If you update the first table, it will not update the additional tables automatically.

A view or a stored procedure however, would update automatically.

Or you can run a crystal report to get any of the desired outputs.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[celestedeeva]

Hello,

I can certainly use a stored procedure instead of just creating a new table -can you briefly outline what would be involved in creating that stored procedure.

I will see what I can come up with - I have never created one before but getting a view or stored procedure would work just as well.

unfortunately I do not have access to crystal reports - are there any alternatives to this? I will see what is involved in order for me to get access to and use crystal reports.

Thank you.

C.

 

[dgillz]

What is the database type? MS SQL? I would repost this question in the appropriate forum asking how to create a stored procedure.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?