Truck Services Inc.
Truck Information Group By Date Report for Week3/1/06-3/15/06
**Services Performed by Truck NO: 317
Date Company Comments Ticket# Total Total Truck Hourly Cartage
Hours Weight Rate Paid Paid
3/7/2006 MOD HOMES 031-11909 0 22.85 $5.25
3/7/2006 CITY OF WAUKEGAN 952866 2 24.74 $7.25
3/7/2006 Subtotal
3/13/2006 REPUBLIC SERVICES, INC. 27806 0 23.08 $13.00
3/13/2006 REPUBLIC SERVICES, INC. 27800 0 21.86 $13.00
3/13/2006 Subtotal
Sum
This is a copy of the report before I added the truck1 and truck2 fields -
just had truck# field and it was fine but need to handle split orders.
The
union query seems to work but like I said would like to make a query using
the union query and the ticket entry query(that I used before) grab infor
for
truck#, truckhours from uniou query and other fields from ticket entry
query.
Would want truck 317 to show as an entry even if originally put in as
truck#1 or truck#2 -union query handling fine but when make the combined
query - the result set has too many records????
Thanks,
Barb
Duane Hookom said:
I'm not sure what you mean by "I would like all info for say TRUCK 577 to
fall into the same group even if on one order it
was entered as Truck#1 or Truck#2 - also want its truckhrs1 and/or
truckhrs2
to just be under truckhrs."
We don't know how your truck information is to be displayed or totaled or
what...
--
Duane Hookom
MS Access MVP
I got the union query to work - putting the syntax exactly how you had
it.
However- now trying to use it with an existing report.
I have the main query- that the main form with
truck1 , truck2, truckhrs1,truckhrs2, plus at ton of other calculations
and
info that I would prefer not to have to rewrite into the union query.
Tried making a new query including the existing MAIN query, and the
union
query, also have a trucktable ( with Truck# being the primary key). I
joined
the union query to the truck table via the trucknum field.
I am getting the wrong result set - too many records.- Set properties
of
query to unique records and or values and still not the right records -
REally not sure how to pull union query correctly with existing select
query
- what do I need to watch out for??????
Am I clear enough?
:
Duane,
I have never done a union query before -Is the Truck# field in the
first
line the 1st place this field shows up???
also getting an error on the below code. Your sample (which thank
you!!)
did not have square brackets around the additional fields ex.
TruckHours1 -
do I need them??
Can you see where my error is????
SELECT TicketEntryTable.[Truck1#] as Truck#,
TicketEntryTable.[TruckHours1]
as Truckhours
FROM TicketEntryTable
UNION ALL SELECT [Truck2#] , [truckhours2]
Where [truck2#] is not null;
I am sure I will have some more questions for you.
Thanks for your help,
Barb
:
babs,
You can use a union query to normalize your table structure.
Something
like:
SELECT [Truck#1] as TruckNum, TruckHrs1 as TruckHrs, AmtPdTruck1 as
AmtPD
FROM tblTwoTrucks
UNION ALL
SELECT [Truck#2], TruckHrs2, AmtPdTruck2
FROM tblTwoTrucks
WHERE [Truck#2] is Not Null;
Use this union query to report various trucks together.
--
Duane Hookom
MS Access MVP
Intially I created a report grouped by truck# and sevice date
(called
Truck
Info Grouped by Date). In the detail for each group it shows the
truck
hours, amtpdtruck , etc.
However, a given order can have 1 truck but also the order may be
SPLIT
into
2 trucks max. I had to add fields to the given tables (Truck#1,
Truck#2,
TruckHrs1, Truckhrs2, amtpdtruck1, amtpdtruck2 )and to the forms
to
accommodate if the split was checked YES to enable the field
pertaining to
Truck2. Its all working but now when I go to the report - I would
like all
info for say TRUCK 577 to fall into the same group even if on one
order it
was entered as Truck#1 or Truck#2 - also want its truckhrs1 and/or
truckhrs2
to just be under truckhrs.
Not sure how to handle this - hope I am clear enough???
Thanks,
Barb
