displaying multiple fields in a report

[laurat]

I would like to create a summary report that will show the major aspects of a record. However these major aspects come from three different tables and I know I need to somehow join them in a query (I tried a Union query but couldn't quite figure it out). Here are the fields:

Table a: ManComplete
BlueTagged

Table b: SourceReady

Table c: Shipped

The records are identified by a SONum which is the primary key in each table so of course I would like to display that also. Thanks for any help!!

 

[IfButOnly]

Create a new query in design view.

Add the three tables.

Click on the SONum in the first table and drag it over the SONum on the second table to create the link. Do the same with the third table. All are now linked.

Drag the fields you want displayed to the design grid and VOILA.

If it is the SQL you are after for a VBA Procedure - do as above, then go into SQL view, copy the code, and paste into your procedure.

 

[laurat]

no luck

Well I must be doing something wrong because my query has no records in it. First I started a query and showed the three tables, a - b - c. Then I drug SONum from table a to table be and from table be to table c. Then I chose the fields I wanted. My question is since SONum is in all three tables, from which table do I choose it from? I am a little confused I guess. Thanks for the help.

 

[IfButOnly]

Laurat, It doesn't really matter where the SONum comes from at this point - the dragging between tables is only creating a join.

As you are not getting data back, the problem is likely to be in the type of join and the relationship of data between the tables. For example, if one of the tables does not have any data in for a particular SONum then that may be why you are not getting the data back.

Click on the join line between each of the tables, and select Join Properties (you may need to right click, can't remember). Change the type of join to what is most appropriate for your application.

If you are still having problems, simplify. I'm fairly new to this so if I have a problem, I would start the query off with just one table - make sure that works - add the second table and set up the join - make sure that works and so on. That way, if you have the problem after attaching the second table it is easier to understand and to explain.

Give it a go, and let us know if you still have a problem.