I have a database for football inventory. I need to print out receipt for what the student did not turn in. First I want to make sure that I have my relationship set up correctly because I cannot seem to get the receipt to work properly. So here is what I have:

Table: Student

StudentID (Primary Key)
LastName
FirstName
Grade

Table: Inventory

StudentID (Linked with Students StudentID)
Shirt
Short
Helmet
Shoulder Pads
Practice Jersey
Practice Pants
Knee Pads
Belt

Table: Receipt

StudentID (Linked with Inventory)
LastName
FirstName
Shirt 
Short
Helmet
Shoulder Pads
Practice Jersey
Practice Pants
Knee Pads
Belt

 

I don't fully understand what you are doing, but it seems you are set up incorrectly.  I think you should be more set up like an invoicing solutions where Receipt table equals a join table between Inventory and Student.  Receipt represents 'line items', and adding inventory is like adding line items, by lookup or calculation.  This could simply be a portal on the Students table.  Then reporting would be easier.

 

Also: "I cannot seem to get the receipt to work properly." is extremely ambiguous. What exactly do you mean by this?

2 hours ago, TaiChi56 said:

First I want to make sure that I have my relationship set up correctly

That's hard to tell without understanding what your solution is designed to track in real life. For example, are you sure you need multiple "Inventory" records for each student? IOW, do you issue the same kit to the same student more than once? I also suspect that tracking the return of the items could (and should) be done using the same record that recorded the initial issue.

Yes, I am not sure either. I attached my database to show you what I am attempting. See if I am on the right track please. Thank you. 

 

Football_Inventory upload Copy.fmp12

Edited January 3, 20179 yr by TaiChi56
It had a password.

The file needs to have User ID and Password?

I thought I fixed that. Will it let you go to guest account and login?

I just checked it. You can log in as Guest. I gave guest full access rights. Thank you. 

 

Edited January 3, 20179 yr by TaiChi56
Checked on file then answered.

Though a number of issues still remain, it is clear that the original relationship was incorrect.

See attached screenshot of the original and suggested revision.

Thank you for that. I understand what you did. Not getting the inventory to show up under the students table though. I am working on that now. 

I think you need to NOT work on it and figure out what pieces you need. As mentioned by Comment. Are you tracking individual pieces rather than strictly a kit?

What happens if somebody loses/breaks something and you issue a new one of those items. You can't do that now. You only have a complete kit or nothing.

And for Comment's benefit, here is a very simple replication as an fp7 version of what is set up so far.

Football_Tai_Mod.fp7

This is equipment that was issued to a football player. If they do not return it then I want to give them a receipt showing what they owe. If the lose it or break it then they still owe money. 

That is not an answer to the question.

Sorry. Yes we are tracking individual pieces not a kit. The student may not receive the same piece the next year as he progresses. If one is broken and lost then we replace it with another one. I hope that answers it. 

So this really has almost everything in common with a standard invoicing system. Customers, products, orders, order line items, prices. And as with invoicing systems, it is sometimes common for orders to consist of a standard group of products.