Report that Pulls from two Forms - Lending Library

[Surferboy1500]

Hello,

I'm using the lending library template to make a lending library.

My particular library will require users to donate (be the original owner) before they can check-out assets.

Can someone explain to me or link an explanation that shows me how to make a report that pulls information by user with both 1) the assets from the "original donor" and 2) any assets that are checked-out to that user.

I'm trying to take the donated assets less the check-out assets to come up with how many net assets donated or check-out.

I've attached the database here. Any help would be greatly appreciated. Thanks!

[Surferboy1500]

Saturday bump

[June7]

Use a UNION query to start with (name it like DonationsAndOut). This will get records from both tables into common columns. Then use this query as the basis for subsequent manipulations of data. Join it to the Contacts table to get contact info (names) and Assets table to get Asset details. Build a report with grouping and sorting and summary calcs.
SELECT "Donation" As Category, ID As AssetID, Owner As UserID FROM Assets
UNION SELECT "Out", Asset, [Checked Out To] FROM Transactions WHERE [Checked In Date] Is Null;

There is no wizard or designer for UNION query. Must type in the SQL View editor. Open the query designer then select SQL View from from the ribbon.

[Surferboy1500]

When I make the SQL query above and then try to make a report, it says that the tables/queries are not connected. I used relationships to connect AssetID to ID on the assets table and UserID to ID in the Contacts table but it still gives me this error message. I'm hoping to present this report tomorrow, so any help would be GREATLY appreciated. Thanks in advance.

[June7]

I am not familiar with that error message. I know join with UNION query is possible, I do that. Post the report's RecordSource SQL statement for analysis. It should be like:
SELECT DonationsAndOut.UserID, [Last Name] & ", " & [First Name] As MemberName, AssetID, Coral, Category
FROM Assets RIGHT JOIN (Contacts RIGHT JOIN DonationsAndOut ON Contacts.ID = DonationsAndOut.UserID) ON Assets.ID = DonationsAndOut.AssetID;

Include as many fields as desired from Assets and Contacts.

[June7]

Attached is the Lending Library db with the two queries I described. I will remove it after you respond or in a few days.

EDIT: Purpose served, file removed.

[Surferboy1500]

I've pulled the DB and will open it at work today. You can remove it though. Thanks,
Josh

Edit: I've opened it up. Hopefully my last question. I used your join to add a bunch of fields and make the report and it worked!

Is there a way to add donations and subtract the "out" assets? I was thinking if there is a way to sumif on that field, I could get two sumif's and then subtract one field from the other. Or do you know an easier way to do that to get a count total? Thanks again, you've bee a huge help.

[June7]

Attached is an extract of the Lending Library with a report I built to demonstrate what you ask for. Showing is easier than describing. Will remove after you respond or in a few days.

EDIT: Purpose served, file removed.

[Surferboy1500]

THANKS SO MUCH! And with that this thread has been solved!