Need help with a report

[TIDMAN]

Hi there all,
I have a DB (Access 97) with a report based on a search i have 3 suppliers when i run my search i search by supplier and date,
what i need to know is if i creat a table with the PO numbers 1 for each supplier how can i get the perticular supplier PO number to show up in the that same suppliers report.

Any help here would be very much appreciated.

Thanks in advance.

TIDMAN

 

[llkhoutx]

Use a query (as the record source for your report) in which you use a right join on the supplier table to get his PO. Use a right join in the event that there's no Supplier PO, otherwise you'll omit record(s).

 

[TIDMAN]

Thanks for your reply, can you tell me what a right join is please, sorry i dont know much about access just learning right now.

Thanks again.

 

[llkhoutx]

Tables:

tbNames
NameID
sLast
sMiddle
sFirst
sStreet
ZipcodeID (actual zipcode)

tbZipCode
ZipCodeID (actual zipcode)
sCity (city for zipcode)
sState (state for zipcode)

There are three types of joins, equi-, right-, and left- for queries:

Equi-joins: one field is always the same in all tables in the query. (Most common situation) Equi-joining the two will give all records in the Name table only if an corresponding record is in the Zipcode table.

Right-join: Field in in domiant table, but not necessarily in sub-serviant table. (common situation) Ex. Tables of names right-joined with table of zipcodes with some zipcodeIDs missing in the zipcode table. Right-joining the two will give all records in the Name table regardless of where or not a record is in the Zipcode table.

Left-join: Field is in sub-serviant table but not necessarily in dominant table. (Least common situation) Left-joining the two tables will give only records in the Name table which have a record in the Zipcode table.

To create a join use the QBE (query by example) window and add tbNames and tbZipcode to the window. Drag ZipcodeID from one table to ZipcodeID in the other table. Right click on the line drawn between the two ZipcodeIDs and select "Join Properties" from the window which pops ups - try each of the three different choices provided to see how the various joins work. Note that an arrow will point right or left on those type joins.

Be sure to have one zipcode which exists in one, but not both tables.