Solved

# sql beginer! 2 recrds into one record

Posted on 2003-02-19
Medium Priority
152 Views
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
Question by:yairsuari
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 53

Expert Comment

ID: 7980950
Try:

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

LVL 1

Author Comment

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

Expert Comment

ID: 7981073
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

LVL 1

Author Comment

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

LVL 43

Expert Comment

ID: 7981118
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

LVL 1

Author Comment

ID: 7981181
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

LVL 1

Author Comment

ID: 7981207
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

LVL 1

Author Comment

ID: 7981294
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

LVL 5

Expert Comment

ID: 7981318
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

LVL 1

Author Comment

ID: 7981422
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

Expert Comment

ID: 7981478

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

Expert Comment

ID: 7981499
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

Accepted Solution

Sonofyoda earned 400 total points
ID: 7981539
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

LVL 1

Author Comment

ID: 7983048
you have helped me a lot been playing with it for too long
0

Expert Comment

ID: 7984270
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library aâ€¦
Enums (shorthand for â€˜enumerationsâ€™) are not often used by programmers but they can be quite valuable when they are. Â What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that containsâ€¦
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). Uâ€¦
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This lâ€¦
###### Suggested Courses
Course of the Month10 days, 16 hours left to enroll