Selecting 2 different fields in the same table with different criteria

[JDog]

Hi,

Say for example I have a table with 3 fields: Date, Sector, Weight

Is it possible for me in the one SQL statement to select the sector field with one set of criteria and the weight field with another set of criteria?

ie is it possible to combine the following 2 SQL statements into 1 query?
SELECT fldSector FROM tblSectors WHERE fldDate = #12/19/2012#
SELECT fldWeight FROM tblSectors WHERE fldDate = #12/18/2012#

so the result displayed would be one column with the fldSector and another column next to it with fldWeight.

 

[plog]

Sort of. You would incorporate the criteria into the SELECT clause:

SELECT Iif(fldDate=#12/19/2012#, fldSector) AS Sector, Iif(fldDate=#12/18/2012#, fldWeight) AS Weight 
FROM tblSectors
WHERE fldDate=#12/19/2012# OR fldDate=#12/18/2012#;

 

[JDog]

Thanks the above statement kind of achieves what I want but not exactly.

Say I had another weight column called fldWeight2

is it possible to combine the following 2 SQL statements into 1 query?
SELECT fldWeight2 FROM tblSectors WHERE fldDate = #12/19/2012#
SELECT fldWeight FROM tblSectors WHERE fldDate = #12/18/2012#

and have the results grouped by sector. Right now using that statement above it will list both fldWeight and fldWeight2 but they will be on different rows. ie there are duplicate sectors.

ie the result of the query would look like this:

fldSector fldWeight fldWeight2
... .... ....

 

[plog]

Gonna need some sample data now. Post some data from your table and then what you would like the end result to be based on that sample data.

 

[Brianwarnock]

I think that this going to require 3 queries
1 sector and fldweight1
2 sector and fldweight2
3 join 1 and 2 on sector

Brian