Inventory?

[Ms Kathy]

it's just like attaching a jpg . . . here you go.

 

[plog]

Sorry about that. I disregarded the 1 to many relationship between Lids and Transactions. This is going to take a sub-query. Here's that SQL:

SELECT T_Transactions.Trans_Lids, T_Transactions.Trans_Totes
FROM T_Transactions
WHERE (((T_Transactions.Trans_Return_Date) Is Null))
GROUP BY T_Transactions.Trans_Lids, T_Transactions.Trans_Totes;

Name the above query 'UnavailableItems', it lists all Lids and Containers that have not been returned. Then using that query you can find out which Lids and Containers are available. This is the query to find out which lids are available:

SELECT T_Lids.Lid_AutoID
FROM T_Lids LEFT JOIN UnavailableItems ON T_Lids.Lid_AutoID = UnavailableItems.Trans_Lids
WHERE (((UnavailableItems.Trans_Lids) Is Null));

You can make a similar one for Containers. I tested both in your database so the above code should work.

 

[Ms Kathy]

I changed the name of the query to Q_Unavailable_Items. It works fine.

I saved the available lids query as Q_Available_Lids. It also works fine.

The available totes query pulling wrong information and I can't figure out why. The totes that are NOT available are 27 and 1. But they show up as being available on this query. What shows up as unavailable is totes 4 and 8. ????

 

[plog]

In Q_Available_Totes you've incorrectly joined T_Totes to Q_Unavailable_Items. You need to join it by Trans_Totes, not Trans_Lids.

 

[Ms Kathy]

That did it!

Now I have to figure out how to use these queries in a form . . .

 

[plog]

I think I mentioned it a few posts back. The form would be based on T_Transactions and the queries would be the Row Source for drop downs. That way users can only select Lids/Totes that are available.

 

[Ms Kathy]

Do I make a "drop down" from a text box? It seems the only options are "combo box" "list box" . . . . "text box"?

 

[plog]

combo box is what you should use. You should be able to right click on a text box and convert it to a combo box. There might even be a wizard that you can use.

 

[Ms Kathy]

I understand. I will now see if I can figure this form out. Thank you!

 

[Ms Kathy]

I still haven't figured this out. How do I create a combo box on a form that will allow me to make multiple selections from the available containers?:banghead:

 

[plog]

You can't make multiple selections in a combo box. Tell what the form is based on. Most likely you will need a subform.

 

[Ms Kathy]

the transactions table

 

[plog]

That doesn't make sense. The transactions table is at the top of your hierarchy--it is not on the many end of any one-many relationships. So what are you selecting many of?

If its totes and containers then that makes it a new record. Each tote/container permutation is one record and you don't select many of them, you create a new record for each permutation.

Can you explain how this system is to work from a user's perspective?

 

[Ms Kathy]

Essentially there will be one transaction (created by one employee, shipping to one customer) that includes many totes and many lids. We want to know which employee performed the transaction; to which customer it went; and which specific containers/totes and lids went to that customer.

 

[plog]

Your database isn't structured for that. Right now 1 transaction = 1 tote/1 lid.

If you described it accurately in your last post you will need a seperate table for tote/lid permuations of a transaction. It would minimally look like this:

T_TransactionContainers
Fields:
TC_TransID: foreign key to T_Transactions.Trans_AutoID
TC_Tote: foreign key to T_Totes.Tote_AutoID
TC_Lid: foreign key to T_Lids.Lids_AutoID
Pickup_Date
Return_Date

All those corresponding fields in T_Transactions (excluding Trans_AutoID) would be removed. Also, Transaction_Weight might go in that new table as well--I assume its the weight of the container correct? Comments may go in the new table as well.

 

[Ms Kathy]

I will work on this.

 

[Ms Kathy]

I'm wondering if I even need the Transaction table . . . It would seem the T_TransactionContainers would be fulfill what I need?

 

[plog]

I believe you do because there is data to capture at the transaction level. I believe both the Employee and Customer data should be at that level.

 

[Ms Kathy]

So does this look right?

 

[plog]

Yes. Is there anything else you want to capture at the Transaction level? The date it initialized? Expected completion date?

Now for your form. You will have a form based on Transactions which will allow you to input a customer and and employee. Then beneath that you will have a subform based on TransactionContainers which will allow you to enter multiple records into that table. In that subform will be the drop downs we've discussed.