Sorting/Grouping on calculated field....? HELP!

  • Thread starter Thread starter Joe Flyer
  • Start date Start date
J

Joe Flyer

Hello all,
I have three tables that are providing information for a report:
1. Labor
2. Vehicle
3. Customer

Each table has one value that relates particular records in each table to
each other. (Unfortunately, I did not design the database, I am simply
trying to extract information from it) In the labor table is Veh_No , which
relates to a record in Vehicle. In Vehicle, I match Veh_No to retrieve
Cust_No, and then proceed to Customer to extract Customer last name. The
report works flawlessly using Dlookup in calculated fields to get me the
information that I want.

However, in order to sort/group based on Customer last name, I need to
perform the lookups in the reports Record Source, and I can't remember how!
My belief is that I will need one query to get me as far as Cust #, and then
the actual query for the report will reference the first query. I don't
know SQL well, and I can't get a dlookup to work properly in the design view
of the underlying query.

Tips, tricks, advice? Thank you!
 
Joe said:
I have three tables that are providing information for a report:
1. Labor
2. Vehicle
3. Customer

Each table has one value that relates particular records in each table to
each other. (Unfortunately, I did not design the database, I am simply
trying to extract information from it) In the labor table is Veh_No , which
relates to a record in Vehicle. In Vehicle, I match Veh_No to retrieve
Cust_No, and then proceed to Customer to extract Customer last name. The
report works flawlessly using Dlookup in calculated fields to get me the
information that I want.

However, in order to sort/group based on Customer last name, I need to
perform the lookups in the reports Record Source, and I can't remember how!
My belief is that I will need one query to get me as far as Cust #, and then
the actual query for the report will reference the first query. I don't
know SQL well, and I can't get a dlookup to work properly in the design view
of the underlying query.
Click to expand...


Instead of trying to fix things up in the report, create a
query that contains all the required data. This is normally
done by adding all three tables to the query design grid,
making sure that the lines connecting the tables are between
the appropriate fields and then dragging the required fields
down to the field list.

Once you get the query selecting the required fields, the
report is just a matter of laying out and formatting the
data.
 
Marsh,
Thanks for the reply....I did fiture out a work-around for this
instance, but I am still unclear on the "right way". I did like you said,
and created a query with all the info, but then I got 5 copies of every
record. What gives with that?
 
Joe said:
Marsh,
Thanks for the reply....I did fiture out a work-around for this
instance, but I am still unclear on the "right way". I did like you said,
and created a query with all the info, but then I got 5 copies of every
record. What gives with that?
Click to expand...


5 duplicates of each record in the query??

I guess that would depend on the Join fields and their
values. Would you post the queries and some sample data.
 
Back
Top