How to build query to retrieve columns from multiple tables by date

[lilp32]

I am relatively new to Access (using 2010) and this seems like it should be simple yet I can't seem to figure it out.

I have aggregate count data from 30+ counting devices (101, 102, etc) which means I have one table for each device with three columns (Date, Count, Average). I want to create a query that will show the date in one column and the count from each device in separate columns (Date, Count.101, Count.102, etc). The date ranges for each unit are not the same. I can create queries for a few devices but when I get up to all 30 it just times out. There must be an easier way to do this! Any help would be much appreciated, thanks!

Here is an example of the SQL code:

SELECT Date2.Date, [101].Count, [102].Count, [103].Count, [104].Count, [105].Count, [106].Count, [107].Count, [108].Count, [109].Count, [110].Count, [111].Count, [112].Count, [113].Count, [114].Count, [115].Count, [116].Count, [117].Count, [118].Count, [120].Count, [121].Count, [122].Count, [123].Count, [124].Count, [125].Count, [126].Count, [127].Count, [128].Count INTO All3
FROM ((((((((((((((((((((((((((Date2 LEFT JOIN 109 ON Date2.Date = [109].Date) LEFT JOIN 105 ON Date2.Date = [105].Date) LEFT JOIN 106 ON Date2.Date = [106].Date) LEFT JOIN 108 ON Date2.Date = [108].Date) LEFT JOIN 107 ON Date2.Date = [107].Date) LEFT JOIN 104 ON Date2.Date = [104].Date) LEFT JOIN 103 ON Date2.Date = [103].Date) LEFT JOIN 102 ON Date2.Date = [102].Date) LEFT JOIN 101 ON Date2.Date = [101].Date) LEFT JOIN 110 ON Date2.Date = [110].Date) LEFT JOIN 111 ON Date2.Date = [111].Date) LEFT JOIN 112 ON Date2.Date = [112].Date) LEFT JOIN 113 ON Date2.Date = [113].Date) LEFT JOIN 114 ON Date2.Date = [114].Date) LEFT JOIN 115 ON Date2.Date = [115].Date) LEFT JOIN 116 ON Date2.Date = [116].Date) LEFT JOIN 117 ON Date2.Date = [117].Date) LEFT JOIN 118 ON [113].Date = [118].Date) LEFT JOIN 121 ON Date2.Date = [121].Date) LEFT JOIN 122 ON Date2.Date = [122].Date) LEFT JOIN 125 ON Date2.Date = [125].Date) LEFT JOIN 123 ON Date2.Date = [123].Date) LEFT JOIN 124 ON Date2.Date = [124].Date) LEFT JOIN 126 ON Date2.Date = [126].Date) LEFT JOIN 127 ON Date2.Date = [127].Date) LEFT JOIN 128 ON Date2.Date = [128].Date) LEFT JOIN 120 ON Date2.Date = [120].Date
ORDER BY Date2.Date;

[nico5038]

Hi Lisa,

I would start with normalizing the table design.
When you add a DeviceID (autonumber) to a table, all devices can be placed in one table.

Having such a table will allow you to use a crosstable query to get the needed overview.

Getting the idea ?

Nic;o)

[lilp32]

Thanks, I am not sure I understand - or maybe I wasn't clear before. Each table has ~90 days of data so adding Device ID just adds an ID for each day.

[nico5038]

The idea is to have one table like:

tblDevice with the fields:
DeviceID (autonumber)
DeviceName (e.g. your 101, 102, etc.)
DeviceDate (don't use "Date" as that's a reserved word!)
Count

The average can be calculated, thus doesn't need to be stored.

When you use an append query for each "Device table" and add the DeviceName, the result will be one table with all Device data.

Clearer?

Nic;o)

[lilp32]

Thanks for your help. I created the tblDevice but am still lost about how to do the queries.

[nico5038]

1) Select the tblDevice101 for a new query and set the query type to "Append" and select in the prompt the new table.
2) Place the Date and Count (hmm better call it DeviceCount as Count is also a reserved word)
3) Add a literal as field like:
DeviceName:101 and set the target fields as needed.
4) Execute the query
5) Change table to the tblDevice102 and literal field value into 102 and execute the query,
6) Etc. for 103 till end.

Getting the idea?

Nic;o)

[lilp32]

OK, that helps me get everything in one table but I really want to have one date value and counts for each date. This creates many duplicate dates. I want to be able to look at a day and see what the count is for that day on all devices. Is there some way to create a crosstab query that will do this?

[nico5038]

That's where the crosstable query is for.

Select the tblDevice for a new query and select the type "crosstable" and place the fields:
DeviceName (Column) (GroupBy)
DeviceDate (Header) (GroupBy)
DeviceCount (Value) (Max)

The first () indicates the setting for the extra line you get when selecting the "crosstable"

Nic;o)

[lilp32]

Thanks very much, I did get it to work!

[nico5038]

Glad I could help, success with your application !

Nic;o)