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

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
Asked:
yairsuari
1 Solution
 
Ryan ChongCommented:
Try:

Select Pollutant, Date, Count(*) As Total from table1 Where Pollutant = 'Cd' And Date = #01/21/2002# Group By Pollutant, Date
0
 
yairsuariAuthor Commented:
i dont know why but it shouts for missing operator in the expresion count(*)
0
 
SonofyodaCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
yairsuariAuthor Commented:
i dont know why but it shouts for missing operator in the expresion count(*)
0
 
TimCotteeCommented:
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
 
yairsuariAuthor 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
 
yairsuariAuthor 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
 
yairsuariAuthor 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
 
bob_onlineCommented:
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
 
yairsuariAuthor 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
 
SonofyodaCommented:
Hmmm ... What about :


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


Result is your result number
0
 
SonofyodaCommented:
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
 
SonofyodaCommented:
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
 
yairsuariAuthor Commented:
you have helped me a lot been playing with it for too long
0
 
SonofyodaCommented:
Glad I could help !
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now