Query with missing infromation in a table?

[Poindexter]

I would like to run a query that will report the ridership on certain bus routes in a single day. I have tables with the boardings and alightings for all buses under various situations. However, not all of the bus routes that I am interested in are present is in each situation (table). So when I run a query to see how many riders are on the selected routes it only returns the routes that are in all of the tables. Is there a way that I can have a query return the ridership for the bus routes I care about for the tables where there is data and than report a blank (or something else other than a number) for the tables where the route is not present?

 

[dkinley]

You will need to specify criteria in the query to limit the records returned. So if a route in the table was called Route A, then the query criteria on that field would be "Route A". All the other records would be ignored and you would get the current count of riders on that Route.

Here are some other links to help you along with examples of queries with criteria and fancy ways of accomplishing your goal ...

http://www.techonthenet.com/access/index.php
http://www.sienaheights.edu/personal/csstrain/Access2007.htm
http://www.functionx.com/vbaccess/

-dK

 

[Poindexter]

I am sorry, I think I may have explained it wrong. I am able to reduce it to only the routes I want by having it reference a table with the selected routes as you suggest. The problem is that if one of those routes does not appear in one of the tables with ridership than that route does not appear in the resulting query output even for the situtations where the route exists.

 

[neileg]

First you need to produce a dataset that has every possible route ID in it. This could be a table you set up, or could be a union query selecting all the route IDs from your boarding tables. Then use a left join to join this to your boarding tables. Access defaults to an inner join which requires matching records on both sides but a left join will give you all of the records from the left hand table (hence the name) and any from the right hand table that match. You can edit the join the the query design view by right clicking the join line. Type 2 in Access is a left join.