Equip check in check out qry PROBLEM

[Hackcess2007]

I have learned quite a bit from reading all your posts but I am having a problem I can't figure out. I made a database to track our equipment in and out of our warehouse by date, barcode number, equipOutIn and crew chief. I made three qrys. the first is all transactions with criteria of the equipOutIn field "OUT'. The second qry is the same but set to "IN". The third qry i joined the first two qrys by barcode join properties equal. I added three columns in the qry, the first is Current Statics: IIf([OUTqry.TransactionDate]>[INqry.TransactionDate] Or [INqry.EquipOutIn] Is Null,"OUT","IN") The second is Return Date: IIf([Current Statics]="In",[INqry.TransactionDate],"") The third is Returned By: IIf([Current Statics]="In",[INqry.EmployeeFirstName],""). This will work great if i check one piece OUT and the IN. but the next time I check the same piece OUT and back IN is distorts and adds multiple entries. I have 2000 pieces of equipment so duplicate and unmatched qrys are not practical. My curiosity has outreached my smarts i'm afraid. Some fresh ideas will be much appreciated THANK YOU VERY MUCH.

 

[plog]

I'd really need to see some data to help you. Can you post 2 sets:

A. Starting sample data from your table. Include table and field names and enough data to cover all cases.

B. What you expect to be output from your query when you use the data in A.

 

[Hackcess2007]

Hi Pog Thanks for the reply. I have attached 3 pics. first shows data in the qry, second shows the qry structure, and the last shows report data. In the report Alexis first line shows the piece of equipment, when it went out and barcode number. The statics column shows the equipment came in and the date and by who. The problem, every time the equipment is check back out it makes more duplicate entries. I think it is the IIF statement it will keep going back to all dates and not the very last transaction. When I first figured it out i would test it be checking a pies OUT and the IN and it worked perfect.(just like the first line on the report. I failed to work multiple ins and outs with the same barcode number and now I have a big problem. I be happy to attach the file but it is to big.

 

[Hackcess2007]

Here are the reports Thanks

 

[plog]

I'd really need to see some data to help you. Can you post 2 sets:

A. Starting sample data from your table. Include table and field names and enough data to cover all cases.

B. What you expect to be output from your query when you use the data in A.

 

[Hackcess2007]

I can do that. I will need a little time put together and then I'll post. Thanks again

 

[Hackcess2007]

Hi plog,
I did try to upload but it said the security token was missing. any thought how i can add that to my database?

 

[jdraw]

Hackcess,

It would be helpful to readers if you could describe a typical day in your "business" in plain English. This will help readers understand your issue/opportunity in context.
If you could post a jpg of your relationships window that would also be useful.

You may have a database structure issue, but we don't know the details of your set up.

Your basic process is similar to a library book borrowing/returning situation.

Book Borrowers Loans Returns

There is a free generic library model here.

Good luck.

 

[Hackcess2007]

Thanks for the insight. I been contemplating a few things on this database. After reading a similar post I may do the following to track the equipment movement. Add a numerical field and give equip out a value of 1 and equipment in a value of -1. Then sum on the value. I have tried it on a small scale and it works. Not sure though. It seems the more i read the more ideas i get.

 

[jdraw]

Keep reading. I'm sure you will always find more ways to do things.
Do some testing and make sure what ever you decide works for you.
Good luck.