Comparing Part Quantities According to Dates

[rochy81]

Hello,



Going to explain this the best I can.

I have a certain amount of parts that come into our warehouse and is then shipped to other facilities. I want to compare the incoming quantity with the out going quantity according to dates. For example:

1/1/09: Incoming part #1111 - 2000 Pcs
1/1/09: Incoming part #2222 - 5000 Pcs

1/10/09: Outgoing part #1111 - 500 pcs
1/12/09: Outgoing part #2222 - 1000 pcs

So I want to list them in a report side by side according to date so compare what comes in and what goes out. Of course we have many more parts and quantities that come and go.

Because of this I want to do a search, for example, I want to search Part number 1111. This then will display all of the incoming and outgoing quantities of parts 1111 for a certain period of time side by side by date it either came in or left.

Please let me know if this doesn't make sense.

Any advice on how to do this?
Thank you!

[rochy81]

An example of what I need is in this screenshot.

[CraigDolphin]

1. Build a parameter query showing your inwards events using a criteria to limit it to just the part you are interested in.

2. Build a second parameter query showing the outward events, also restricted to the part you are interested in.

3. Build a third query that lists all dates when any transaction occured involving the part number you are interested in.

4. Finally, build a query that returns all the dates from 3 (using an outer join), inwards event data from 1, and outwards event data from 2.

Base your report on 4.

You can either use an undefined parameter (causes a prompt to appear for the user to type in the part number), or else create a form with a combo box containing a part number, and have the user select a part from that combo and use a reference to the form/combo as your parameter instead.

[rochy81]

Thanks!
Gonna play around with that.

[rochy81]

I've been playing around with this the whole time and I cant get it to work.

Even getting a 'Type mismatch in expression' error.

Please take a look at the database below.

Thanks for the help!

[rochy81]

Ah, nevermind, got it to work.

Thanks for your help!

[CraigDolphin]

Glad you got it sorted. I could not have opened your db anyways as I do not have AC2007