Two tables one query

  • Thread starter Thread starter DJ Finnegan
  • Start date Start date
D

DJ Finnegan

OK, lets see if I can get this without confusing everyone.

I have table 1 which list a series of records where one field is related to
multiple records in another table.
I need to see all the records from table one (which i am sorting by this
related field) but also need to see the related records in table two.

Clearer if i tell you table one record shows a bank id, table two lists bank
id plus several contacts at bank.

I have tried several things after reading in forum but I must be doing
something wrong.
 
Use a join between the two tables in a query.

This query will return records from table 1 only where there are matching
records in table 2:

SELECT [table 1].*, [table 2].*
FROM [table 1] INNER JOIN [table 2]
ON [table 1].FieldWithRelatedValue =
[table 2].FieldWithRelatedValue;


This query will return all records from table 1 even if there is no matching
record in table 2:

SELECT [table 1].*, [table 2].*
FROM [table 1] LEFT JOIN [table 2]
ON [table 1].FieldWithRelatedValue =
[table 2].FieldWithRelatedValue;
 
Ken, your the best man but I don't think I gave you enough info...The first
table creates a listing of everything pending for multiple bank ids, the
second table may contain the bankid several times but each contact would need
to be listed. I have a picture of the report in my mind..something like:

Bank id from tbl1 where =tbl2 Tbl2 bank name table2 contact

table2 contact
_____________________________________

CUSIP etc records from tbl1

Is that any clearer.....thanks alot dude for all your help...you're my hero!
Ken Snell MVP said:
Use a join between the two tables in a query.

This query will return records from table 1 only where there are matching
records in table 2:

SELECT [table 1].*, [table 2].*
FROM [table 1] INNER JOIN [table 2]
ON [table 1].FieldWithRelatedValue =
[table 2].FieldWithRelatedValue;


This query will return all records from table 1 even if there is no matching
record in table 2:

SELECT [table 1].*, [table 2].*
FROM [table 1] LEFT JOIN [table 2]
ON [table 1].FieldWithRelatedValue =
[table 2].FieldWithRelatedValue;

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


DJ Finnegan said:
OK, lets see if I can get this without confusing everyone.

I have table 1 which list a series of records where one field is related
to
multiple records in another table.
I need to see all the records from table one (which i am sorting by this
related field) but also need to see the related records in table two.

Clearer if i tell you table one record shows a bank id, table two lists
bank
id plus several contacts at bank.

I have tried several things after reading in forum but I must be doing
something wrong.
Click to expand...
Click to expand...
 
Show what the two tables' fields are/would be. I'm puzzled by your statement
that table 2 contains multiple records for each bankid, but that you want to
show all the bankid values from table 2? which table contains just a single
record of info/data for each bankid?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


DJ Finnegan said:
Ken, your the best man but I don't think I gave you enough info...The
first
table creates a listing of everything pending for multiple bank ids, the
second table may contain the bankid several times but each contact would
need
to be listed. I have a picture of the report in my mind..something like:

Bank id from tbl1 where =tbl2 Tbl2 bank name table2 contact

table2 contact
_____________________________________

CUSIP etc records from tbl1

Is that any clearer.....thanks alot dude for all your help...you're my
hero!
Ken Snell MVP said:
Use a join between the two tables in a query.

This query will return records from table 1 only where there are matching
records in table 2:

SELECT [table 1].*, [table 2].*
FROM [table 1] INNER JOIN [table 2]
ON [table 1].FieldWithRelatedValue =
[table 2].FieldWithRelatedValue;


This query will return all records from table 1 even if there is no
matching
record in table 2:

SELECT [table 1].*, [table 2].*
FROM [table 1] LEFT JOIN [table 2]
ON [table 1].FieldWithRelatedValue =
[table 2].FieldWithRelatedValue;

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


DJ Finnegan said:
OK, lets see if I can get this without confusing everyone.

I have table 1 which list a series of records where one field is
related
to
multiple records in another table.
I need to see all the records from table one (which i am sorting by
this
related field) but also need to see the related records in table two.

Clearer if i tell you table one record shows a bank id, table two lists
bank
id plus several contacts at bank.

I have tried several things after reading in forum but I must be doing
something wrong.
Click to expand...
Click to expand...
Click to expand...
 
Hey Ken...ok
Table1
Bankid CUSIP Record Pydate (each cusip has bankid associatedbut in report
i'm thinking to use as group)
Table 2
Bankid Contact Phone (every contact has associated bankid)

In final report i would like to group by bank id display list of contacts
and phone numbers

Then details would contain cusips etc from tbl1

Hey..Thanks for all your help to both me and everyone else..I really have
learned alot from you...whether you know it or not...and i appreciate your
responses in this forum.

Ken Snell MVP said:
Show what the two tables' fields are/would be. I'm puzzled by your statement
that table 2 contains multiple records for each bankid, but that you want to
show all the bankid values from table 2? which table contains just a single
record of info/data for each bankid?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


DJ Finnegan said:
Ken, your the best man but I don't think I gave you enough info...The
first
table creates a listing of everything pending for multiple bank ids, the
second table may contain the bankid several times but each contact would
need
to be listed. I have a picture of the report in my mind..something like:

Bank id from tbl1 where =tbl2 Tbl2 bank name table2 contact

table2 contact
_____________________________________

CUSIP etc records from tbl1

Is that any clearer.....thanks alot dude for all your help...you're my
hero!
Ken Snell MVP said:
Use a join between the two tables in a query.

This query will return records from table 1 only where there are matching
records in table 2:

SELECT [table 1].*, [table 2].*
FROM [table 1] INNER JOIN [table 2]
ON [table 1].FieldWithRelatedValue =
[table 2].FieldWithRelatedValue;


This query will return all records from table 1 even if there is no
matching
record in table 2:

SELECT [table 1].*, [table 2].*
FROM [table 1] LEFT JOIN [table 2]
ON [table 1].FieldWithRelatedValue =
[table 2].FieldWithRelatedValue;

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


OK, lets see if I can get this without confusing everyone.

I have table 1 which list a series of records where one field is
related
to
multiple records in another table.
I need to see all the records from table one (which i am sorting by
this
related field) but also need to see the related records in table two.

Clearer if i tell you table one record shows a bank id, table two lists
bank
id plus several contacts at bank.

I have tried several things after reading in forum but I must be doing
something wrong.
Click to expand...
Click to expand...
Click to expand...
 
The query for your data would be this:

SELECT [Table 2].Bankid, [Table 2].[Contact Phone], [Table 1].CUSIP ,
[Table 1].[Record Pydate]
FROM [Table 2] LEFT JOIN [Table 1] ON
[Table 2].Bankid = [Table 1].Bankid;

Then in the report you can group by Bankid from the above query.

(Thanks for your kind comments!)
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



DJ Finnegan said:
Hey Ken...ok
Table1
Bankid CUSIP Record Pydate (each cusip has bankid associatedbut in report
i'm thinking to use as group)
Table 2
Bankid Contact Phone (every contact has associated bankid)

In final report i would like to group by bank id display list of contacts
and phone numbers

Then details would contain cusips etc from tbl1

Hey..Thanks for all your help to both me and everyone else..I really have
learned alot from you...whether you know it or not...and i appreciate your
responses in this forum.

Ken Snell MVP said:
Show what the two tables' fields are/would be. I'm puzzled by your
statement
that table 2 contains multiple records for each bankid, but that you want
to
show all the bankid values from table 2? which table contains just a
single
record of info/data for each bankid?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


DJ Finnegan said:
Ken, your the best man but I don't think I gave you enough info...The
first
table creates a listing of everything pending for multiple bank ids,
the
second table may contain the bankid several times but each contact
would
need
to be listed. I have a picture of the report in my mind..something
like:

Bank id from tbl1 where =tbl2 Tbl2 bank name table2 contact

table2 contact
_____________________________________

CUSIP etc records from tbl1

Is that any clearer.....thanks alot dude for all your help...you're my
hero!
:

Use a join between the two tables in a query.

This query will return records from table 1 only where there are
matching
records in table 2:

SELECT [table 1].*, [table 2].*
FROM [table 1] INNER JOIN [table 2]
ON [table 1].FieldWithRelatedValue =
[table 2].FieldWithRelatedValue;


This query will return all records from table 1 even if there is no
matching
record in table 2:

SELECT [table 1].*, [table 2].*
FROM [table 1] LEFT JOIN [table 2]
ON [table 1].FieldWithRelatedValue =
[table 2].FieldWithRelatedValue;

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


OK, lets see if I can get this without confusing everyone.

I have table 1 which list a series of records where one field is
related
to
multiple records in another table.
I need to see all the records from table one (which i am sorting by
this
related field) but also need to see the related records in table
two.

Clearer if i tell you table one record shows a bank id, table two
lists
bank
id plus several contacts at bank.

I have tried several things after reading in forum but I must be
doing
something wrong.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top