• Status: Solved
• Priority: Medium
• Security: Public
• Views: 153

# sql beginer! 2 recrds into one record

i have an access table built like this:

Factory      Pollutant     Units        Date          Value
AGA           Cd               mg/l         21/1/02   5
AGA           Flow           m^3/day    21/1/02    12

and i wand to calculate the amount of Cd (meaning Cd records multiplied by the flow in these dates)
how can i build an sql statement that does that?
(the date factory pollutant combination is unique)
0
yairsuari
1 Solution

Commented:
Try:

Select Pollutant, Date, Count(*) As Total from table1 Where Pollutant = 'Cd' And Date = #01/21/2002# Group By Pollutant, Date
0

Author Commented:
i dont know why but it shouts for missing operator in the expresion count(*)
0

Commented:
It's not, try this :

SELECT COUNT(*) FROM Table WHERE UPPER(Pollutant) = 'CD'

(This ignores the case on the Cd - cd - Cd - CD)

COUNT(*) will count the number of records

I think you should leave out the date in the WHERE statement if you want all records.

If you want the total of the values try :

SELECT SUM(Value) FROM Table WHERE UPPER(Pollutant) = 'CD'
0

Author Commented:
i dont know why but it shouts for missing operator in the expresion count(*)
0

Commented:
I think that you are really looking for something like this:

Select Factory, [Date], Sum(Value * (Select Value From MyTable FacTable Where Factory = FacTable.Factory And Pollutant = 'Flow') As Result From MyTable Where Pollutant = 'Cd' Group By Factory,[Date] Order By Factory,[Date]

This could be quite a long query as for each CD entry it finds it has to search for a matching 'Flow' record in the same table (here referenced again with an alias).
0

Author Commented:
i think i wasnt clear i need to multiply Cd concentration with the flow to get the quantity of Cd discharged.
this means i need to take 2 records from the same table and make one record in the query out of them.
0

Author Commented:
i think i wasnt clear i need to multiply Cd concentration with the flow to get the quantity of Cd discharged.
this means i need to take 2 records from the same table and make one record in the query out of them.
0

Author Commented:
i think i wasnt clear i need to multiply Cd concentration with the flow to get the quantity of Cd discharged.
this means i need to take 2 records from the same table and make one record in the query out of them.
0

Commented:
Is this what you're looking for?

Select Cd.Factory, cd.Date, cd.Value * Flow.Value As Total From Table1 As Cd, Table1 as Flow
Where Cd.Factory = Flow.Factory and
cd.Date = flow.date and
cd.pollutant = "Cd" and
Flow.pollutant = "Flow";

This query joins the table to itself and takes one value from each of the two records.
0

Author Commented:
bobonline this definately looks closer but it prompts for all the things with . in the midle (like Cd.Date)
why is that and how do i make those things non parametric?
0

Commented:

SELECT CdValue * FlowValue AS Result, CdTable.Value AS CdValue, FlowTable.Value AS FlowValue FROM Table1 AS CdTable, Table1 AS FlowTable WHERE CdTable.Factory = FlowTable.Factory AND CdTable.Pollutant = FlowTable.Pollutant AND CdTable.Date = FlowTable.Date

0

Commented:
Sorry, my mistake :

SELECT CdValue * FlowValue AS Result, CdTable.Value AS CdValue, FlowTable.Value AS FlowValue FROM (SELECT * FROM Table1 WHERE Pollutant = 'Cd') AS CdTable, (SELECT * FROM Table1 WHERE POLLUTANT = 'Flow') AS FlowTable WHERE CdTable.Factory = FlowTable.Factory AND CdTable.Pollutant = FlowTable.Pollutant AND CdTable.Date = FlowTable.Date
0

Commented:
Sorry, my mistake :

SELECT CdValue * FlowValue AS Result, CdTable.Value AS CdValue, FlowTable.Value AS FlowValue FROM (SELECT * FROM Table1 WHERE Pollutant = 'Cd') AS CdTable, (SELECT * FROM Table1 WHERE POLLUTANT = 'Flow') AS FlowTable WHERE CdTable.Factory = FlowTable.Factory AND CdTable.Pollutant = FlowTable.Pollutant AND CdTable.Date = FlowTable.Date
0

Author Commented:
you have helped me a lot been playing with it for too long
0

Commented: