Dynacmic DLOOKUP in Query

[warhead92100]

Hi All,

I have this 2 table:

Table1:

SITE	12AM	1AM	2AM	3AM
A	1500	1490	1300	1500
B	600	800	740	900
C	600	600	600	600
A	9000	150	30000	5
B	6	8	67	8

and

Table 2

TIME	A	B/C
12 AM
1 AM
2 AM
3 AM

Basically I would like to create a query that will lookup the values (by specific time) for all A in Table1 and sum up the B and C.

Can anyone guide me how to do this?

Thanks!

[June7]

Really don't understand "lookup the values (by specific time) for all A in Table1 and sum up the B and C". Data structure makes no sense. How is record A related to records B and C? The records are always sets of 3? How should Access know which A goes with which B and C? Exactly what is the criteria for the DLookup? How does 'lookup for all A' get the data from B and C for summing?

[warhead92100]

Table 1 variables are "Site", "12AM", "1AM", etc. Table 2 variables are "Time", "Site A", "Site B", etc etc.

I need to get the values of Site A - 12 AM from table 1 to be the value of 12AM - Site A in Table 2.

from table above:

Table 2, Row 1 should be:

TIME | A | B/C
12AM | 10500 | 1206

** | - used to depict different columns
*** Sites in table 1 are dynamic, but not in table 2
**** Time in table 2 are already keyed in, the query will append this table with the data from Table 1

any idea?

[June7]

A UNION query:

SELECT "12AM" AS MyTime, Sum(IIf([Site]="A",[12AM],0)) AS [A], SUM(IIf([Site]="B" Or [Site]="C",[12AM],0)) AS [B_C] FROM table1
UN ION SELECT "1AM", Sum(IIf([Site]="A",[1AM],0)), SUM(IIf([Site]="B" Or [Site]="C",[1AM],0)) FROM table1
... ;

There is no query builder or wizard for UNION, must type in SQL View. There is a limit of 50 SELECT statements.

Or build the UNION with raw data (no aggregate calcs) which essentially produces the normalized structure data should have been in first place. Then use the UNION in subsequent queries to do calcs or as report RecordSource.

SELECT Site, "12AM" AS MyTime, [12AM] AS Data FROM table1
UN ION SELECT Site, "1AM", [1AM] FROM table1
... ;

So there really is no need for Table2 unless the goal is to normalize the data structure and this manipulation is a one-time event.

[warhead92100]

A UNION query:

SELECT "12AM" AS MyTime, Sum(IIf([Site]="A",[12AM],0)) AS [A], SUM(IIf([Site]="B" Or [Site]="C",[12AM],0)) AS [B_C] FROM table1
UN ION SELECT "1AM", Sum(IIf([Site]="A",[1AM],0)), SUM(IIf([Site]="B" Or [Site]="C",[1AM],0)) FROM table1
... ;

There is no query builder or wizard for UNION, must type in SQL View. There is a limit of 50 SELECT statements.

Or build the UNION with raw data (no aggregate calcs) which essentially produces the normalized structure data should have been in first place. Then use the UNION in subsequent queries to do calcs or as report RecordSource.

SELECT Site, "12AM" AS MyTime, [12AM] AS Data FROM table1
UN ION SELECT Site, "1AM", [1AM] FROM table1
... ;

So there really is no need for Table2 unless the goal is to normalize the data structure and this manipulation is a one-time event.

I understand less than half of the notes above.

I'm not really familiar with SQL. Do you know a quick and easily digested guide for it?

Thanks!

[June7]

Here is a good start http://www.w3schools.com/sql/sql_union.asp

Try building the second query I suggested then use that query object just like a table in subsequent queries or as report RecordSource.

[warhead92100]

reps added.

very helpful, though I just barely understand it.

Thanks again June.