?
Solved

sql beginer! 2 recrds into one record

Posted on 2003-02-19
15
Medium Priority
?
152 Views
Last Modified: 2010-05-01
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
Comment
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
  • Learn & ask questions
15 Comments
 
LVL 53

Expert Comment

by:Ryan Chong
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

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

Expert Comment

by:Sonofyoda
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 1

Author Comment

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

Expert Comment

by:TimCottee
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

by:yairsuari
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

by:yairsuari
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

by:yairsuari
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

by:bob_online
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

by:yairsuari
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

by:Sonofyoda
ID: 7981478
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
 

Expert Comment

by:Sonofyoda
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

by:
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

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

Expert Comment

by:Sonofyoda
ID: 7984270
Glad I could help !
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

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

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question