Problem with join...I think

[Bean]

I am creating a database for billing and payroll. Employees are paid
by deliveries and at times hourly. I am having problems extracting
all payroll by driver in my query and am hoping someone will help me
out here. Here are some more details.

I have the following tables:
Drivers:
Driver information and DriverCode (Primary Key)

Deliveries:
DispatchDate, ManifestNumber (Primary Key), DriverCode

Manual_Payroll:
Hours, ExtraMiles, DriverCode (Primary Key), DispatchDate
(Secondary Key)

Each driver can make more than one delivery per day but may get misc.
pay without making a delivery with a limit of one record per day per
driver for misc pay.

Trying to pull this information with one query with joins between
DriversriverCode and DeliveriesriverCode
DriversriverCode and Manual_PayrollriverCode
DeliveriesispatchDate and Manual_PayrollispatchDate
only pulls the information for drivers that receive misc. pay then
lists the misc. pay for each delivery made.

If I try change the join type Access complains about ambiguous outer
joins.

I can pull the information correctly in a separate queries but still
can't get a new query from those queries to work right.

Will someone nudge me in the right direction. Please go easy on me,
I'm quite obviously a newbie.

Thanks.

 

[peregenem]

Trying to pull this information with one query with joins between
DriversriverCode and DeliveriesriverCode
DriversriverCode and Manual_PayrollriverCode
DeliveriesispatchDate and Manual_PayrollispatchDate
only pulls the information for drivers that receive misc. pay then
lists the misc. pay for each delivery made.

If I try change the join type Access complains about ambiguous outer
joins.

SELECT *
FROM (Drivers INNER JOIN Deliveries
ON Drivers.DriverCode = Deliveries.DriverCode)
LEFT JOIN Manual_Payroll
ON Deliveries.DriverCode = Manual_Payroll.DriverCode
AND Deliveries.DispatchDate = Manual_Payroll.DispatchDate

 

[Van T. Dinh]

From your description, the Table Deliveries and Table Manual_Payroll are not
related except for the fact that they share the DriverCode and the
DispatchDate. If you combine 2 joins in your Query, you link , in effects,
the Table Deliveries and Table Manual_Payroll indirectly. This, more or
less, creates an "indirect partial Cartesian" join between the Table
Deliveries and Table Manual_Payroll which is not what you wanted.

OTOH, what are you going to use the Query for? This sort of data
combination is probably more suitable using Form/Subform combination or
Report rather than trying to do it on a single Query...

Check Access Help on Union Query and see if you can use Union Query.