Joins in queries

[geoffD]

My database deals with transporting people in Vans between cities. The vans are assigned a van #.

tabels are
tbl_trips: (passenger trip) with fields Trip_Date, Van# inbound, Van# outbound, Number of passengers, Passneger name, and others
tbl_Van_Capacities with fields Van #, Num_passenger_seats


I am trying to make a form that will show, by date, the van # and available seats inbound and available seats out bound.

Each trip has a least one passenger but sometimes more as the passenger may have an aid traveling with them.

I have made queries to sum the total passengers per vehicle per date inbound and and other for the return (outbound). I can even calculate the available seats!

The problem is to join them by date when there may both in and out bound loads, only inbound or only outbound loads.

My problem is that there is no join paramater that allows for missing dates on EITHER side.

I tried a union query but I end up with the same date and vehicle on separate lines ( one for inbound and one for outbound)


I'll try almost any suggestion. Thanks

 

[wizcow]

Geoff

Your on the right track with the Union Query.

It sounds to me like you need something like this...

SELECT[Date],[VanNo],[Out],0 As[In]
FROM[QryOutBound]

UNION SELECT[Date], [VanNo],0 As[Out],[In]
FROM[QryInBound]

If there is no Inbound the first part of the query will assign a 0 in the In column.
Same goes for the Out column is the second part of the query.

Change the field names to fit your db.

Good Luck!
Tom

 

[geoffD]

Well, That is close. But I still get two lines.

ie.

Date Car No. Available In Available Out
2003/11/12 13 0 2
2003/11/12 13 3 0


When what I want is

2003/11/12 13 3 2

hope the formating of the note stays.

thanks

 

[geoffD]

Hey I got it to work thanks. By adding the zero value I could then run another query with the union as the source and just sum the values. Thanks for the help.

sometimes all you need is some one elses ideas to provide the spark!

 

[wizcow]

geoffD

I guess I forgot to mention the second query.
Glad you got it.

Tom