Query based on a Totals Query and a Table

[coffeeman]

Hello all,

I have a Table that we receive a material into with a quantity or number of items received in.

Everday I enter into a Form the daily production. By selecting the material used, I made a query to show the TOTAL amount used during a selected date range.

This all works fine.

I want to be able to make a report that can show how much of a particular material that I have left. Basically, I want it to take the TOTAL material that has been used (NO DATE RANGE WILL BE REQ'D for this query) and subtract it from the QUANTITY RECEIVED from the MATERIAL RECEIVING TABLE. This way I can know how much of a particular item is available TODAY.


THANKS!

 

[HiTechCoach]

See:

Inventory Control: Quantity on Hand

Hope this helps ...

 

[coffeeman]

That is a lot of coding for my knowledge.

I have a sample or part of the database that I can show you. I am able to run a query based on 2 other queries that will show me the amount of material (FILM) used since received in using a 3%shrinkage calculation.

My problem is, it only shows the results for film that has been USED. It doesnt show film that has been received in, but not used.

For example if I have received a new film in and it has not been used, the query will not show that received amout as ON HAND.

Take a look at the sample database if you don't mind.

The query that has STEP 3 on end of it is the final query that runs and gives me the total remaining of FILM that has been USED.

Eventually I want a report that can show the total per lot code of FILM that is available as of TODAY.

The table labeled "Film: Receiving Log" is the table that we enter new film into and has the original quantity received of the FILM.

The table labled "Daily Production" is the table that shows the amount used on a given day for a product and all of the film associated with it.

I also have a command that I select manually that I can make a lot code of a material as COMPLETED and it will ignore those COMPLETED lot codes in the calculation of current Film ON HAND.

Sorry if a little wordy. Just wanted to get all info down.

Thanks for all of the help!!

 

[coffeeman]

Has anyone had a chance to look at this?

I am still having issues with showing the received quantity that has not been used yet.

If anyone can help, I would really appreciate it.

Thanks!!