Querying returned products.

[back2basic]

Finally got my DB straight so I am not using calculated fields however can't figure out the best way to handle the Combo Box so I do not show closed records ( Item checked out and item returned).

Please see the attached table of materials checked out and materials returned. Will someone please help setup a query so that if an item has been returned and matches the item checked out, Both the original check-in and the matching return records are filtered and not displayed in the Combo Box.

Also, I am a little worried about partial returns as when an employee only returns half the amount.

Really kinda stuck here.

Thank you in advance

 

[plog]

I think your structure isn't quite right yet. While you should be storing allocations and returns in the same table, you shouldn't be doing it with seperate fields. Instead of Date_allocated and Date_returned fields you simply should have a AssignmentDate field and then another field that tells you if that assignment is a return or an allocation. Same goes fro the two 'Quantity_' fields you have.

Also, you mentioned 'closed records'. I think I understand what you want to do, but with your database its not possible. My guess is that one record of your table is associated with another record of your table, for example Assets_ID=68 and Assets_ID=51. If that's true, there's no way your database knows that. You have no data linking Assets_ID=68 to Assets_ID=51. You need to explicitly make that connection by either adding a new table or by adding a field in your current table to hold corresponding Asset_ID values. Most likely this will be a whole new table.

 

[back2basic]

But this presents a problem because I need to know when a product was returned and how much for two reasons: 1: because the amount of time a piece of material was out is how much we charge for the usage and 2: because some materials in large quantity are not all returned at once.

I think I understand what you are saying in the second paragraph: Asset_ID 51 was material checked out from the Materials table and Asset_ID 61 was the same piece of material returned using the Assigned_Assets table. I know it is the exact same material because (Assigned_Assets and Materials) are linked through the Material_ID. This field is hidden from your view. I have attached my relationship table so you can see that.

Thank you

 

[plog]

No that' doesn't do it. You know that the same material was allocated and returned in 51 and 68 respectively, but with your definition you cannot definitively say those transactions are related.

Since all items aren't always returned at the same time, this is going to require another table. I would keep the Material_Assignment table, but remove the Date_Returned and Quantity_Returned fields. Then I would make a new table called Materials_Returned with these fields:

Return_ID (autonumber, primary key)
Assets_ID (number, foreign key to Material_Assignment)
Return_Quantity (number)
Return_Date (date)

This table would link to Materials_Assignment via the Assest_ID fields with a 1 to many relationship between Materials_Assignment and MAterials_Returned.

 

[back2basic]

Got it Thank you. Let me chew on this and make the changes. Be back in a few days with another post. Hopefully all will be working.

 

[back2basic]

Plog, Not sure I understand what is going on here. Now that I have removed the Date_Returned and Quantity_Returned fields and now have a look-up field that indicates if the material is Allocated and Returned which by-the-way works for both deleted fields, why do I need the "Return_Quantity (number)" and the "Return_Date (date)" fields in the new table. Isn't this going to be redundant data? I am guessing in this scenario you want me to write the Date and Quantity in both tables...Assigned_assets and Materials_Returned?

Please correct me if I am wrong here.

 

[plog]

Disregard my initial post. That was made without a full understanding of how materials got returned and how you wanted to track them. All data in Materials_Assignment will be assignments and return data will all be in that new table

 

[back2basic]

Plog, Can't get it to work. The fields are not displaying like I need them to in the Returned_Materials form. I have a mess.

Dale

 

[plog]

Forms are something you work on after you have the structure correct. What do your tables look like?

 

[back2basic]

Will a snap shot of the relationships give you enough information? A snap shot is attached. As you can see I added the Return_Materials Table and linked it to the Material_Assignment via Asset_ID(FK) to Asset_ID(PK). This Obvious, I hope, because only materials assigned or "checked out" can be returned and I want to select the materials returned from the Material_Assignment table of records.

The problem is and something you may have noticed, so I will go ahead and explain, I linked Employee_ID to the Material_Return table likewise I did the same for Material_ID.

Why? I thought that naturally I should be able to see the employees.last and employees.first name and the material_name via the link that Material_Assignment has to the Employees and Materials table and that Materials_Returned is linked via Asset_ID but I can't and do not know why?

Am I way off base here, all this information was easy to setup and see in the Material_Assignment table.

Thank you so very much for taking your time to haggle through my problem and ignorance.

 

[plog]

Materials_Returned shouldn't have a relationship to Employees. In that same vein, Materials_Returned shouldn't have a Material_ID field. That data is already available via the Material_Assignment which captures the employee data and the material data. Neither of those fields should be in Materials_Returned.

 

[back2basic]

Okay, the new relationship is attached and I understand this was your original suggestion. It is clear to me and I thought the same.

But, when I do this and create a form....and insert a Combo Box should I only select the items listed in the Material_Assignment table as the row source?

Because, when I do this I can not see the "deep" details of the Material_Assignment record like the employee.last, employee.first and material_name information.

If that information is already is linked to the new Material_Return Record via Material_Assignment, Asset_ID link and then the respective link that Material_Assignment has to the Employees and the Materials table this is fine and good, I do not need this information in the Return_Materials table however I do need to see it in the Combo Box so the user can select the appropriate record.

 

[back2basic]

Sorry missed the attachment.

 

[plog]

But, when I do this and create a form....and insert a Combo Box should I only select the items listed in the Material_Assignment table as the row source?

What form are you creating? You need a form for every table. If you want to see data on a form that isn't directly in the table the form is based on, I would use a DLookup.

 

[back2basic]

What form are you creating? You need a form for every table. If you want to see data on a form that isn't directly in the table the form is based on, I would use a DLookup.

I understand and do have a form for every table. Now, finally I hope, I am creating a form from the Materials_Returned table to enter returned materials and populated the Materials_Returned table? The thing is I want to show the user of the form the Last name, First name, Material _Name, Date_Allocated as well as Quantity_Issued via an unbound Combo Box. This makes it very easy to select from the records of Materials_Assigned.

This was easy to do when I created the form for Materials_Assigned via ( in this case a bound Combo Box)

But when I create the Combo Box and populated the row source of the CB....No Assigned_Materials records are shown????. The only way I have been able to show the fields I want in the CB has been (in the past) to include those fields in the Material_Returned table.

 

[plog]

I'm not exactly sure how you are trying to make your forms work together, but I would do it one of two ways--both an offshoot of the Materials_Assignment form.

1. On the Material_Assignment form have a button called 'Items Returned', this would pop up a small form that allows the user to enter the date and quantity returned.

2. On the Material_Assignment form I would have a sub-form based on the return table that allows the user to put in the data.

Both these methods don't need to display all the data on the return form because the Material_Assignment form has that data and is visible.

A third way, as I mentioned before is to use Dlookups. Given just a Material_Assignment id you can use a dlookup to retrieve all the other data from all the other tables.

 

[back2basic]

Plog, I hope we can get on the same page hear. Please understand if I seem direct it is only because I am frustrated at my lack of ability and understanding to get this done the way I want to:


The best and easiest way and most sensible ( for the user that is), certainly not for me creation wise that is, is the following:

1) The user should be able to select from a pull down list "Combo Box"....Whatever at this point a list of the materials that are outstanding or assigned. This list should shrink as materials are returned.


The user should not be able enter the materials being returned from scratch because (A) he will get it wrong and (B) that material and all of its pertinent information was already entered and a record created when it was checked out in the Material_Assignment table. By selecting one of the materials already listed, what is being returned is exactly what is checked out and a good reference to auto-fill the fields. All I want from the user is the Date and quantity being returned. I already know what it is, by who, how much, and when. I am simply trying to re-use what I already know.

 

[plog]

That combo box is possible. It would be based on a query which determines which materials are outstanding. There might be an issue of duplicates if you are only displaying material names in it. If 2 different people take out same material, or the same person taking the same material on different dates that material would show up twice and the user wouldn't know which one it is for.

Who is entering data into this sytem? You personally? A person in charge of inventory? Or individual borrowers?

 

[back2basic]

That combo box is possible. It would be based on a query which determines which materials are outstanding. There might be an issue of duplicates if you are only displaying material names in it. If 2 different people take out same material, or the same person taking the same material on different dates that material would show up twice and the user wouldn't know which one it is for.

Great, I understand, but that is the reason for showing as much about the record in the Return_Material Combo Box as possible. and moreover precisely why I linked Material _ID and Employee_ID to the Material_Assignment table. All of the information is there but unless I embed Material_ID and Employee_ID in the Return_Materials table, the CB will not show the material_name, Employee_last, employee_first, in fact, any information at all in the CB. This is the row source of the Return_Materials Combo Box as of now: There are three record in the Material_Assignment table but the CB is empty?? I know this is a big Row source but there is no reason, that I know of, why it does not show this information in each record. Now keep in mind, getting back to your other idea of putting a button for "return materials" in the Material_Assignment form. This is essentially what I was doing however with the switch board instead of a button when I originally posted.

SELECT Material_Assignment.Date_Allocated, Materials.Material_name, Material_Assignment.Quantity_issued, Materials.Serial_Number, Employees.Last, Employees.First, Material_Returned.Asset_ID FROM Materials INNER JOIN ((Employees INNER JOIN Material_Assignment ON Employees.Employee_ID = Material_Assignment.Employee_ID) INNER JOIN Material_Returned ON Material_Assignment.Assets_ID = Material_Returned.Asset_ID) ON Materials.Material_ID = Material_Assignment.Material_ID;

Who is entering data into this sytem? You personally? A person in charge of inventory? Or individual borrowers?

This data will be entered by other people ( perhaps two) that stock and issue material. After it get working, I will not be entering this data.

 

[plog]

I didn't dig too deep into your query, but I see nothing but INNER JOINs in there. You are going to need a LEFT JOIN from Material_Assignment to Material_Returned to find out what materials haven't been returned yet.

If you can post your database I can produce that query for you.