query joints.

[WH]

I have two tables, table 1 contains many records, each
record has its own "group1 number", "group2
number", "group3 number".
It looks like:

ID group1_number group2_number group3_number
1 2 1 3
2 5 2 2
etc..

table 2 looks like this:

"GROUP #" "group1 factor" "group2 factor" "group3 factor"
1 0.1 0.2 0.68
2 0.69 0.36 0.33
3 some other factors....
4
5
6 0.22 0.33 0.39
etc.

Now for each record in table1, I need to get its group1
factor, group2 factor, and group3 factor.

For example, a record has group1 number=2 group2 number=1
group3 number=6. I would want to go into table 2, and
return group1 factor = 0.69, group2 factor=0.2, group3
factor=0.39.

How should I do this most efficient?

Thanks a lot for any help.

WH

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You should redesign your tables. Take them out of spreadsheet "mode."
Like this:

Table 1
ID - corresponds to your ID
GroupID - corresponds to your "group?" where ? = 1-3
GroupValue - corresponds to your grid value

Your data example would look like this:

ID GroupID GroupValue
1 1 2
1 2 1
1 3 3
2 1 5
2 2 2
2 3 2

Table 2
Group_No - corresponds to your Group #
GroupID - corresponds to your "group?" where ? = numbers 1-3
GroupFactor - corresponds to your grid value

Your data example would look like this:

Group_No GroupID GroupFactor
1 1 0.1
1 2 0.2
1 3 0.68
2 1 0.69
2 2 0.36
2 3 0.33

To get the data in a query you'd create a query like this:

SELECT T1.ID, T1.GroupID, T2.GroupFactor
FROM Table1 As T1 LEFT JOIN Table2 As T2
ON T1.GroupID = T2.GroupID
WHERE ... <your criteria> ...

Create indexes on GroupID in both tables. This will speed up the
query.

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQCrd54echKqOuFEgEQLyKACguiF/vE0i3rauOM9xObTkrLPQkF0AoIhq
i+UvENneUSeE+EBGwjc0T4lb
=2mUU
-----END PGP SIGNATURE-----

 

[Duane Hookom]

The most efficient method would be to normalize your tables. Your table 1
would look like:
ID
GroupNumber
TheValue

Your second table would look like:
ID
GroupNumber
GroupFactor
AnotherValue

 

[WH]

Thanks a lot!!

However, it's better not to change my table1, for each
record, it contains all sorts of other fields. And yes,
you are abs. right, because that table was originally
imported from EXCEL. And that table is really big too...

What I'm trying to do is to create joins between table1
and table2. To find group1 factors, I would join
table1's "group1 number" with table2's "GROUP #". To find
group2 factor, I would join table1's "group2 number" with
table2's "GROUP #". etc. But this way, I'd need to
add "table2" three times into my query. But just as you
said, because my tables are not properly designed, it's
very inefficient to do this.

WH