Pulling Data from 2 tables

[Dubya007]

hey there everyone, i need some code to pull data from two tables in Access97.

My situation is this. i have to gather some info about a certain machine and i have info in 2 different tables. i need to be able to pull the data from the 2 tables, update it if need be and then add the new info back to the two tables. i have tried a copule different appraoches to this but have been reletivly unsucessful. thanx for any help.

[ironhide]

You need to be a little less vague...

But generally

Select * FROM T1 Inner Join T2 ON T1.CommonField = T2.CommonField

[Garratt]

He'd need some type of matching key field for that right though?

Depending on what he's doing it could end up being a Union or even he could open two record sets at the same time... ?

[ironhide]

Yeah, the matching key field is the T1.Field = T2.Field

Assuming that the 2 tables he wants data from are related in some way.

[Dubya007]

the tables are related in a field. they share a common machine number that is how i am getting info on a certain machine from each table. my problem is updating each table so that if info for one is changed or updated then the other one needs to be as well. i haven't worked with this stuff very much so can some one tell me about the UNion and Join things? thanx for all the help

[Dubya007]

i have tried to use the SQL statements and i think that i have them down but i need to know how to put the values that you get from the statements to a VB front end. if you would like i can post code. i did it the way i thought it would be but i got an error. please some one help

[ironhide]

Post the structure of your table, what you're trying to achieve and how you're going about it.

[Dubya007]

Post the structure of your table, what you're trying to achieve and how you're going about it.

do u need the stucture of both tables that i am trying to pull data from or just the one that i am trying to make?

[Ecniv]

Post the structures to both tables, your Sql statements that don't work and what the error(s) are.


Vince

[Dubya007]

i have to take some info from both the tables to make a new one, but the new one has to be able to have more than one entry bc it is a table that ensures that a yearly safety test be done on each machine so there will be more than one entry in the resulting table for each machine.
my table structures are as follows

Testble
RNum-PK(Autonumber)
IDNum-FK
NeedACheck
Supervisor
Date

tblMachines
id-PK
machine_name
deptID
NeedsCheck

------------------------------------------------
my sql statments
SELECT Testble.IDNum, tblMachines.machine_desc, tblMachines.NeedsCheck, Testble.NeedACheck, Testble.date tblMachines.deptID, Testble.Supervisor
FROM tblMachines, Testble
WHERE tblMachines.id = Testble.IDNum

that is what i think it would be i had some other SQL statements but i deleted them so i can't give the error.

[ironhide]

SELECT * FROM tblMachines INNER JOIN testble ON tblMachines.ID = testble.IDNUM

[Ecniv]

From personal experience I wouldn't use an inner join, as it only returns records with matching ids. This might sound good, but if you start pulling statistics, it can leads to discrepancies.

I prefer to use the left join, and where clause filtering on not is null if required. At least you know you have everything in the left table with the left join

Of course, for you - it'd be best if you can run both inner and left joins to compare and make the decision yourself.

Also see second top thread, and you other posting on joins (I replied there too)


Vince

[Dubya007]

this may sound like a dumb question but......how do you know which is the left and which is the right table? is it the order in which thay appear in your sql statement.

[Ecniv]

table1 left join table2 on table1.id=table2.id

table1 is the left side table

It gets tricky after that lol.

(table1 left join table2 on table1.id=table2.id)
left join table3 on table1.id=table3.id

or trickier....


I have to go - finishing 'work'
Good Luck
Experiment

Vince

[Dubya007]

thanx a whole bunch sir