Question Combining fields and grouping by them

eb2

New member
Local time
Today, 15:09
Joined
Aug 11, 2014
Messages
3
Hello guys

New to this forum and new to access (fairly)

I'm having to tweak an existing database so am trying to play with what I already have

there is a sales table which is set up roughly along th elines of

Transaction No., Product 1 Price, Product 1 Type , Product 2 Price, Product 2 Type
I need to group all the sales made by product type ( regardless of whether they are the first or sceond line of a transcation and total each group as they need to be coded seperately,

eg for the lines,

1, £5, apples, £1, pears
2, £4, bananas, £2, apples
3, £2, pears

I need to show

Apples T1, £5 Total £7
T2 £2

Pears T1 £1 Total £3
T3 £2

Is this possible? Any help would be much appreciated.

EB2!
 
welcome

It's a bad design flaw in a relational database to have . . .
Code:
Product1, Price1, Product2, Price2, ..., ..., ProductN, PriceN
. . . in a single record. One record should hold information about one thing at most.

If you are going to spend time working with relational database tables, it's worth reading up on "database normalization," and start to gain an understanding of how to model data. If your data model is wrong, making anything work is going to be very difficult. Get your data model right, and everything will seem easy.

Hope this helps,
 
I'm aware its a bad design but am trying to make the best of a bad situation as the database is in use and transferring the data would be very long winded, was looking for a way to get round the flaws.

Any ideas on how this could be achieved.
 
It's messy but you can do something using a UNION query, you'll need to use the SQL editor in Access as the query designer doesn't do Unions.

Code:
SELECT
 transactionNo
 ,Product1Price as ProductPrice
 ,Product1Type as ProductType
FROM sales
UNION ALL
SELECT
  transactionNo
 ,Product2Price as ProductPrice
 ,Product2Type as ProductType
FROM Sales
repeat for as many ProductNPrice/Code columns as you have.
The UNION ALL is there just in case someones put two lots of apples, for examples in Product1 and Product2 (with the same price) for the same Transaction and ensures both columns are included in the output. A UNION without the ALL statement will remove what Access considers to be duplicate Values.

This should give you output:

Code:
Transaction   ProductPrice   ProductCode
----------------------------------------
1              £5              Apples
2              £4              Bananas
3              £2              Pears
1              £1              Pears
2              £2              Apples

Which should give you the building block for what you're trying to do.
A GROUP and SUM on that query using ProductCode and ProductPrice will now give you your totals.
 

Users who are viewing this thread

Back
Top Bottom