locker database

[Guest]

I am creating a school locker database. I am trying to create a report that
will list all the locker numbers and combos and then list the names of the
assigned students. I would like the report to display the all the locker
numbers whether they are assigned or not. I have created one report that
shows all the assigned lockers and another report that shows all the
unassigned lockers, but I can't seem to get the information into one report.
Any help would be very much appreciated.

 

[tina]

well, you've given us almost nothing to go on, here. i can only guess that
you have a table of lockers which includes each locker's combination. and a
table of students. and a linking table of specific students assigned to
specific lockers. i'm guessing also that in the report's underlying query,
the tables are linked by Inner Joins. if all that is the case, try linking
from the lockers table to the linking table (in the query) using a Left
Join, and from the linking table to the students table again using a Left
Join.

hth

 

[Guest]

Yes, I have all three of the tables you listed. I have played around with
the join types but can only get results displayed that exclude the unassigned
lockers. I am a complete beginner at Access and am learning by finding
queries that seem to do what I need and then adapting them but I haven't been
able to find one that is suitable for this. My thought was that I could
create the query and then create the report from the query. Would there be a
better way to go about it?

 

[John Spencer]

That is good. Create a query that shows the data you want and then create a
report based on the query.

Try posting the SQL of the query you have (Hint open the query in design mode
and then: Menu: View: SQL). You should have a query that looks something like
the one below

SELECT Lockers.LockerID
, Students.LastName
, Students.FirstName
, Lockers.Combo
FROM (Lockers Left Join StudentLocker