Query involving multiple sources

[hyrican]

I have data collected from on-site data loggers that I need to analyze. I have run a query to sort DataLoggerKey from AreaTypeTable based on the classification of the Area type that the logger was installed in. From this query I have a list of unique DataLoggerKey entries. I want to query another table that has data (and therefore multiple entries) that correlate to each DataLoggerKey. Example:

DataLoggerTable -
DataLoggerKey_________Date_________Occupied_________ Lights
8171_________________5/5/08_________0_______________0
8171__________________5/6/08_________1__________________0
8171__________________5/7/08_________1__________________1
8171__________________5/8/08_________0__________________1
415__________________5/6/08_________1__________________0
415__________________5/7/08_________0__________________1
7555__________________5/8/08_________1__________________1

AreaTypeTable -
DataLoggerKey_________Refrigerated_________Aisle?
415__________________non__________________aisle
7555__________________refrig_______________open space
4632__________________freezer______________aisle
8171__________________non_________________aisle

How do I structure a query that will yield the columns Date, Occupied, Lights, based on the criteria that DataLoggerKey from DataLoggerTable matches DataLoggerKey from AreaTypeTable based on area type (Refrigerated Aisles, Nonrefrig aisles, freezer open spaces etc.)?

Thanks in advance.

 

[MSAccessRookie]

I have data collected from on-site data loggers that I need to analyze. I have run a query to sort DataLoggerKey from AreaTypeTable based on the classification of the Area type that the logger was installed in. From this query I have a list of unique DataLoggerKey entries. I want to query another table that has data (and therefore multiple entries) that correlate to each DataLoggerKey. Example:

DataLoggerTable -
DataLoggerKey Date Occupied Lights
8171 5/5/08 0 0
8171 5/6/08 1 0
8171 5/7/08 1 1
8171 5/8/08 0 1
415 5/6/08 1 0
415 5/7/08 0 1
7555 5/8/08 1 1

AreaTypeTable -
DataLoggerKey Refrigerated Aisle?
415 non aisle
7555 refrig open space
4632 freezer aisle
8171 non aisle

How do I structure a query that will yield the columns Date, Occupied, Lights, based on the criteria that DataLoggerKey from DataLoggerTable matches DataLoggerKey from AreaTypeTable based on area type (Refrigerated Aisles, Nonrefrig aisles, freezer open spaces etc.)?

Thanks in advance.

Sounds like you want an Inner Join of the Two tables based on the DataLoggerKey that extracts the fields Date, Occupied, and Lights from the table DataLoggerTable, and sorts by the fields Refrigerated and Aisle in the AreaTypeTable. Something like this might be a good start:

Select dlt.Date, dlt.Occupied, dlt.Lights 
From DataLoggerTable As dlt Inner Join AreaTypeTable As att
On dlt.DataLoggerKey = att.DataLoggerKey
Order by att.Aisle, att.Refrigerated;

 

[hyrican]

MS Access Rookie,
Thanks for the tip about inner join. I have been reading up on this and I think you're right, this is what I need to use. When I try to implement your code, and when I tried to write my own, I get a "Syntax Error in Join Operation" error. Here's the code I've written adapted from yours and with the names changed to match my Access Table names:

SELECT DataLoggerKey, TimeAndDate, Occupied, Lights
FROM MDT Inner Join AreaTypeComparisonFiltered
ON DataLoggerKey.MDT=DataLoggerKey.AreaTypeComparisonFiltered
WHERE [HMG Category Type].AreaTypeComparisonFiltered = 'n';

Any further help you could offer would be great...

Thanks,

 

[MSAccessRookie]

MS Access Rookie,
Thanks for the tip about inner join. I have been reading up on this and I think you're right, this is what I need to use. When I try to implement your code, and when I tried to write my own, I get a "Syntax Error in Join Operation" error. Here's the code I've written adapted from yours and with the names changed to match my Access Table names:

SELECT DataLoggerKey, TimeAndDate, Occupied, Lights
FROM MDT Inner Join AreaTypeComparisonFiltered
ON DataLoggerKey.MDT=DataLoggerKey.AreaTypeComparisonFiltered
WHERE [HMG Category Type].AreaTypeComparisonFiltered = 'n';

Any further help you could offer would be great...

Thanks,

If MDT is the table, and DataLoggerKey is the column, then you have the syntax reversed (The correct Syntax being {TableName}.{ColumnName}). I will try to correct it using what I believe you mean.

SELECT DataLoggerKey, TimeAndDate, Occupied, Lights
FROM MDT Inner Join AreaTypeComparisonFiltered
ON MDT.DataLoggerKey = AreaTypeComparisonFiltered.DataLoggerKey
WHERE AreaTypeComparisonFiltered.[HMG Category Type] = 'n';

 

[hyrican]

Yea, I had the syntax reversed. Thanks for your prompt help Rookie.

 

[MSAccessRookie]

Yea, I had the syntax reversed. Thanks for your prompt help Rookie.

Glad to help sort that out for you

 

[hyrican]

Quick additional question: When I try to make this query into a "Make Table" query, the error "Invalid Argument" shows up. If I run the query as a simple select query, it works perfectly. Any ideas why I can't make a table with this data?

 

[boblarson]

Any ideas why I can't make a table with this data?

Why do you need to make a table with this query? What do you need a table for that the query itself won't do?

 

[hyrican]

Hmm...I suppose you're right Bob, I was trying to get to a point where I could open up a table later to view the data, but I can just run the query.