Generate a report for every record matching criteria 1

[GoNoles]

I have an Access database tracking users, their contact information and the software they use. One table (tblUserProfile) contains their personnel/contact information. Another table (tblAppInventory) contains information about every software application they use. Both tables have a related 'Email' field. This is the unique ID for each user (and primary key in tblUserProfile). So, they will have one user profile (categorized by their email address) with multiple software applications (associated to the user by thier email address).
It has been a long time since I've done a lot with relationships, queries, etc and I am at a loss on how to getnerate a report listing all applications for each user. The ultimate goal is to conduct a personal survey on hardware, software, network connections, etc.... So, I need to query the tblUserProfile by base locations and building numbers, then generate a separate report for each application they use.
Any help would be most appreciated!!!!
Thanks and have a great day,
GoNoles

 

[randysmid]

Hi,
Here is the overall strategy I would use.
First, go into the Query painter and create a new query using the two tables you mentioned. Be sure to show how the tables are linked together by clicking on the field (email?) to be used, and then sliding the cursor over to the corresponding field in the other table.
Second, to make sure that you are getting the correct record set, you will need to right click on the line linking the two tables together and select "Join Properties". You want to decide how the records will be selected. The default option is to select only those records where rows will be selected if they match up in both tables. However, you may want it to select all people records (tblUserProfile) and then only those records from tblAppInventory that match. That way, you can see all of the people.
Third, you can test the query by clicking on the exclamation mark to run. If it retrieves the information you want, simply go ahead and save the query.
Fourth, go to the Report painter and create a new report using the query you just created as the recordsource.
Fifth, to get the report to break every time the person changes, you can set the "Sorting and Grouping" options (found under the "View" menu item). With this, you can create a header for the person that will include their name, and any other info you desire. You can also make the report writer create a footer for the person as well.

This should really get you on your way.
If this tip has been helpful, be sure to click on the "Mark this post as a helpful/expert tip!".

Randy Smith
California Teachers Association

 

[GoNoles]

Thanks so much!!! After days of pulling out my hair, I read your advise and within a few minutes.....wahlah, it works!!!
Thanks, you're a life saver!!!!!
Michelle (GoNoles)