linking two tables

  • Thread starter Thread starter Greg
  • Start date Start date
G

Greg

Hi,

sorry for the basic question, I’m really green at Access...

I have 2 identical tables with identical headings with headings have no real
significance.
Values in the first table are repetitive # days e.g., 1, 30, 90, 180, 360.
The other has associated numerical values, so the relationship is between
corresponding cell in two tables.

I’m trying to see the numerical values in table 2 for 30 day point for
example or in other words excel speaking, build a pivot table. if I had two
columns of data, one with days the other one with values, I would simply
vlookup. However, since the data is in matrix form I cannot pull it off in
excel.

Your answer or a link to tutorial would be much appreciated.
 
I do not understand what you are trying to accomplish but it seems like you
do not need table1. Just use a parameter query to pull the required
records from table2.

Post sample data from the tables so your undertaking can be understood.
Also what you expect to extract from the data.
 
Sorry for not being clear, samples are below.
for every #days from table1 i need to see the series of correspodning
values in table 2:
so for example:
3600 days (equivalent of 10 yrs): 2.803, 15.348, 33.214, 33.214, 33.214,
35.16, 35.16, 21.872 and so on.

table 1:
3/25/09 3/24/09 3/23/09 3/20/09
3600 2520 2520 2520
3600 3600 3600 3600
5400 5400 3600 3600
360 360 360 360
3600 3600 3600 3600
720 720 720 720
10800 10800 10800 10800
2520 2520 2520 2520
3600 3600 3600 3600

table2:
3/25/09 3/24/09 3/23/09 3/20/09
2.803 2.803 2.803 2.803
15.348 33.214 33.214 33.214
35.16 35.16 35.16 35.16
94.281 81.768 76.441 95.725
21.872 21.872 21.872 21.872
4.295 4.295 4.295 4.295
8.771 8.771 8.771 8.771

5.729 5.912 5.911 9.323
 
Form what I see you need to add a new field to each table so it looks like
this --
table 1:
Row 3/25/2009 3/24/2009 3/23/2009 3/20/2009
1 3600 2520 2520 2520
2 3600 3600 3600 3600
3 5400 5400 3600 3600
4 360 360 360 360
5 3600 3600 3600 3600
6 720 720 720 720
7 10800 10800 10800 10800
8 2520 2520 2520 2520
9 3600 3600 3600 3600

table2:
Row 3/25/2009 3/24/2009 3/23/2009 3/20/2009
1 2.803 2.803 2.803 2.803
2 15.348 33.214 33.214 33.214
3 35.16 35.16 35.16 35.16
4 94.281 81.768 76.441 95.725
5 21.872 21.872 21.872 21.872
6 4.295 4.295 4.295 4.295
7 8.771 8.771 8.771 8.771
8
9 5.729 5.912 5.911 9.323

Then two union queries --
TableX_Roll --
SELECT TableX.Row, #3/25/2009# AS [Date1], TableX.[3/25/09] AS [Days]
FROM TableX
UNION ALL SELECT TableX.Row, #3/24/2009# AS [Date1], TableX.[3/24/09] AS
[Days]
FROM TableX
UNION ALL SELECT TableX.Row, #3/23/2009# AS [Date1], TableX.[3/23/09] AS
[Days]
FROM TableX
UNION ALL SELECT TableX.Row, #3/22/2009# AS [Date1], TableX.[3/22/09] AS
[Days]
FROM TableX
UNION ALL SELECT TableX.Row, #3/21/2009# AS [Date1], TableX.[3/21/09] AS
[Days]
FROM TableX
UNION ALL SELECT TableX.Row, #3/20/2009# AS [Date1], TableX.[3/20/09] AS
[Days]
FROM TableX;

TableY_Roll --
SELECT TableY.Row, #3/25/2009# AS [Date1], TableY.[3/25/09] AS [DateVal]
FROM TableY
UNION ALL SELECT TableY.Row, #3/24/2009# AS [Date1], TableY.[3/24/09] AS
[DateVal]
FROM TableY
UNION ALL SELECT TableY.Row, #3/23/2009# AS [Date1], TableY.[3/23/09] AS
[DateVal]
FROM TableY
UNION ALL SELECT TableY.Row, #3/22/2009# AS [Date1], TableY.[3/22/09] AS
[DateVal]
FROM TableY
UNION ALL SELECT TableY.Row, #3/21/2009# AS [Date1], TableY.[3/21/09] AS
[DateVal]
FROM TableY
UNION ALL SELECT TableY.Row, #3/20/2009# AS [Date1], TableY.[3/20/09] AS
[DateVal]
FROM TableY;

And last query ---
SELECT TableX_Roll.Days, TableY_Roll.DateVal, TableX_Roll.Row,
TableX_Roll.Date1
FROM TableX_Roll INNER JOIN TableY_Roll ON (TableX_Roll.Date1 =
TableY_Roll.Date1) AND (TableX_Roll.Row = TableY_Roll.Row)
WHERE (((TableX_Roll.Days)="3600"))
ORDER BY TableX_Roll.Row, TableX_Roll.Date1 DESC;
 
hi Karl,
Thank you for your response. I'm getting "Query is too complex" when
creating TableX_Roll and Table_Y_Roll as i have 130 columns across (data
below is just a small sample)

is there any workaround?
 
You can try to do the union queries in pieces and append to temp tables.
Then run the last query on the temp tables instead of union queries.
Greg said:
hi Karl,
Thank you for your response. I'm getting "Query is too complex" when
creating TableX_Roll and Table_Y_Roll as i have 130 columns across (data
below is just a small sample)

is there any workaround?
--
______
Regards,
Greg


KARL DEWEY said:
Form what I see you need to add a new field to each table so it looks like
this --
table 1:
Row 3/25/2009 3/24/2009 3/23/2009 3/20/2009
1 3600 2520 2520 2520
2 3600 3600 3600 3600
3 5400 5400 3600 3600
4 360 360 360 360
5 3600 3600 3600 3600
6 720 720 720 720
7 10800 10800 10800 10800
8 2520 2520 2520 2520
9 3600 3600 3600 3600

table2:
Row 3/25/2009 3/24/2009 3/23/2009 3/20/2009
1 2.803 2.803 2.803 2.803
2 15.348 33.214 33.214 33.214
3 35.16 35.16 35.16 35.16
4 94.281 81.768 76.441 95.725
5 21.872 21.872 21.872 21.872
6 4.295 4.295 4.295 4.295
7 8.771 8.771 8.771 8.771
8
9 5.729 5.912 5.911 9.323

Then two union queries --
TableX_Roll --
SELECT TableX.Row, #3/25/2009# AS [Date1], TableX.[3/25/09] AS [Days]
FROM TableX
UNION ALL SELECT TableX.Row, #3/24/2009# AS [Date1], TableX.[3/24/09] AS
[Days]
FROM TableX
UNION ALL SELECT TableX.Row, #3/23/2009# AS [Date1], TableX.[3/23/09] AS
[Days]
FROM TableX
UNION ALL SELECT TableX.Row, #3/22/2009# AS [Date1], TableX.[3/22/09] AS
[Days]
FROM TableX
UNION ALL SELECT TableX.Row, #3/21/2009# AS [Date1], TableX.[3/21/09] AS
[Days]
FROM TableX
UNION ALL SELECT TableX.Row, #3/20/2009# AS [Date1], TableX.[3/20/09] AS
[Days]
FROM TableX;

TableY_Roll --
SELECT TableY.Row, #3/25/2009# AS [Date1], TableY.[3/25/09] AS [DateVal]
FROM TableY
UNION ALL SELECT TableY.Row, #3/24/2009# AS [Date1], TableY.[3/24/09] AS
[DateVal]
FROM TableY
UNION ALL SELECT TableY.Row, #3/23/2009# AS [Date1], TableY.[3/23/09] AS
[DateVal]
FROM TableY
UNION ALL SELECT TableY.Row, #3/22/2009# AS [Date1], TableY.[3/22/09] AS
[DateVal]
FROM TableY
UNION ALL SELECT TableY.Row, #3/21/2009# AS [Date1], TableY.[3/21/09] AS
[DateVal]
FROM TableY
UNION ALL SELECT TableY.Row, #3/20/2009# AS [Date1], TableY.[3/20/09] AS
[DateVal]
FROM TableY;

And last query ---
SELECT TableX_Roll.Days, TableY_Roll.DateVal, TableX_Roll.Row,
TableX_Roll.Date1
FROM TableX_Roll INNER JOIN TableY_Roll ON (TableX_Roll.Date1 =
TableY_Roll.Date1) AND (TableX_Roll.Row = TableY_Roll.Row)
WHERE (((TableX_Roll.Days)="3600"))
ORDER BY TableX_Roll.Row, TableX_Roll.Date1 DESC;
Click to expand...
Click to expand...
 
Greg said:
hi Karl,
Thank you for your response. I'm getting "Query is too complex" when
creating TableX_Roll and Table_Y_Roll as i have 130 columns across (data
below is just a small sample)

is there any workaround?
Click to expand...

I could be mistaken here but if you have a table with 130 columns I
would say that your table structure is "probably" incorrect. It's not
uncommon for users of Excel to misunderstand how the tables need to be
set up. Look up the term normalization. It confused me completely when I
first started out.

gls858
 
Back
Top