Basic stock control problem

[my_brain_hurts]

Hi All


I imagine that what I’m going to ask is a very basic stock control question (albeit the stock may be unusual) but please go gently on me guys as I’m very much an Access newbie - this is my first project (and possibly the last).

The background - I work in a lab environment and some of the tools of our trade are radioactive isotopes. I’m setting up a simple database to track usage of vials of different types of isotopes by various members of staff.

So far I have two tables – OUT (to deal with all the isotope withdrawals and user details) and NEW VIALS (into which all fresh vials of isotope stock are entered). There are a number of fields in each but the important ones are:
OUT (input via a form):
-vial number
-quantity withdrawn
-name
-lab number
-date.
NEW VIALS:
-vial number
-isotope
-description
-starting quantity.
I have a query [ALL WITHDRAWALS] containing many of those fields and with the vial numbers related. This query is the basis for my main report that details all usage grouped by vial number.

The problem - I want to set up a second report that presents the details of all withdrawals of a given isotope between defined dates. It will sum the amount of isotope withdrawn and the amount remaining in each vial (both easily done by defining criteria in the query). However, I also want it to calculate the total amount of isotope remaining across all vials of a certain isotope and so far have been unable to fix it. At the moment this query is taking its fields from the [ALL WITHDRAWALS] query.

For example
If I want to query how much of the isotope 32P is withdrawn between dates A and B I run my [ISOTOPE] query which pops up dialogues asking for:
-Isotope?
-Start Date?
-End date?
The result might be:
Two vials, each containing 32P isotope have had withdrawals made in the defined period. They each have two records:
Vial 10 = 100 withdrawn (its starting quantity = 1000)
Vial 10 = 150 withdrawn (same vial so starting quantity =1000)
Vial 20 = 20 withdrawn (starting quantity = 500)
Vial 20 = 60 withdrawn (starting quantity again = 500)
I can do the sums on this in the report:
Vial 10 = 250 withdrawn (amount remaining = 750)
Vial 20 = 80 withdrawn (amount remaining = 420)
However, when I try to calculate the total amount of isotope 32P remaining I get 3000-330=2670 ie all of the starting quantities are added together when really I only want the starting quantity from the first record of each vial.

Hopefully this makes some sort of sense. I’m sure there must be an easy way to do this but any advice would be greatly appreciated. Thanks.

 

[MSAccessRookie]

Can you show the query? It appears you are summing the Starting quantities when you do not need to. Something like this might work:

Select [NEW VIALS].Isotope, [NEW VIALS].[starting quantity] - sum([OUT].[quantity withdrawn])
From [NEW VIALS] Inner Join [OUT] On [NEW VIALS].[vial number] = [OUT].[vial number]
Where [OUT].[COLOR=green][B]date[/B][/COLOR] Between [COLOR=red][B]{StartDate}[/B][/COLOR] and [COLOR=red][B]{EndDate}[/B][/COLOR]
Group By [NEW VIALS].Isotope, [NEW VIALS].[starting quantity]

GREEN = Field name should be changed since date is a reserved word
RED = Replace these items as required

 

[my_brain_hurts]

Can you show the query? It appears you are summing the Starting quantities when you do not need to. Something like this might work:

Select [NEW VIALS].Isotope, [NEW VIALS].[starting quantity] - sum([OUT].[quantity withdrawn])
From [NEW VIALS] Inner Join [OUT] On [NEW VIALS].[vial number] = [OUT].[vial number]
Where [OUT].[COLOR=green][B]date[/B][/COLOR] Between [COLOR=red][B]{StartDate}[/B][/COLOR] and [COLOR=red][B]{EndDate}[/B][/COLOR]
Group By [NEW VIALS].Isotope, [NEW VIALS].[starting quantity]

GREEN = Field name should be changed since date is a reserved word
RED = Replace these items as required

Hi MSAccessRookie

Thanks for getting back to me so quickly

Query is:

SELECT [All Withdrawals Query].[Vial Number], [All Withdrawals Query].[Amount Withdrawn], [All Withdrawals Query].Isotope, [All Withdrawals Query].Name, [All Withdrawals Query].[Date (dd/mm/yyyy)], [All Withdrawals Query].[Lab Number], [All Withdrawals Query].Description, [All Withdrawals Query].Units, [All Withdrawals Query].ID, [All Withdrawals Query].[Starting Quantity]
FROM [All Withdrawals Query]
GROUP BY [All Withdrawals Query].[Vial Number], [All Withdrawals Query].[Amount Withdrawn], [All Withdrawals Query].Isotope, [All Withdrawals Query].Name, [All Withdrawals Query].[Date (dd/mm/yyyy)], [All Withdrawals Query].[Lab Number], [All Withdrawals Query].Description, [All Withdrawals Query].Units, [All Withdrawals Query].ID, [All Withdrawals Query].[Starting Quantity]
HAVING ((([All Withdrawals Query].Isotope)=[Which isotope do you want to track?]) AND (([All Withdrawals Query].[Date (dd/mm/yyyy)]) Between [start date?] And [end date?]));

Will be offline until tomorrow but will try out your suggestion and get back to you. Thanks again

 

[MSAccessRookie]

[COLOR=black][FONT=Verdana][B][COLOR=blue]SELECT[/COLOR][/B] [B][COLOR=seagreen]AWQ[/COLOR][/B].[Vial Number],[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]  [B][COLOR=#2e8b57]AWQ[/COLOR][/B].[Amount Withdrawn],[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]  [B][COLOR=#2e8b57]AWQ[/COLOR][/B].Isotope,[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]  [B][COLOR=#2e8b57]AWQ[/COLOR][/B].Name,[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]  [B][COLOR=#2e8b57]AWQ[/COLOR][/B].[Date (dd/mm/yyyy)],[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]  [B][COLOR=#2e8b57]AWQ[/COLOR][/B].[Lab Number],[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]  [B][COLOR=#2e8b57]AWQ[/COLOR][/B].Description,[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]  [B][COLOR=#2e8b57]AWQ[/COLOR][/B].Units,[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]  [B][COLOR=#2e8b57]AWQ[/COLOR][/B].ID,[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]  [B][COLOR=#2e8b57]AWQ[/COLOR][/B].[Starting Quantity][/FONT][/COLOR]
[FONT=Verdana][COLOR=black][B][COLOR=blue]FROM[/COLOR][/B] [B][COLOR=blue][All Withdrawals Query][/COLOR][/B] As [/COLOR][COLOR=#2e8b57][B]AWQ[/B][/COLOR][/FONT]
[FONT=Verdana][COLOR=black]GROUP BY [B][COLOR=#2e8b57]AWQ[/COLOR][/B].[Vial Number],[/COLOR][/FONT]
[COLOR=black][FONT=Verdana]  [B][COLOR=#2e8b57]AWQ[/COLOR][/B].[Amount Withdrawn],[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]  [B][COLOR=#2e8b57]AWQ[/COLOR][/B].Isotope,[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]  [B][COLOR=#2e8b57]AWQ[/COLOR][/B].Name,[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]  [B][COLOR=#2e8b57]AWQ[/COLOR][/B].[Date (dd/mm/yyyy)],[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]  [B][COLOR=#2e8b57]AWQ[/COLOR][/B].[Lab Number],[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]  [B][COLOR=#2e8b57]AWQ[/COLOR][/B].Description,[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]  [B][COLOR=#2e8b57]AWQ[/COLOR][/B].Units,[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]  [B][COLOR=#2e8b57]AWQ[/COLOR][/B].ID,[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]  [B][COLOR=#2e8b57]AWQ[/COLOR][/B].[Starting Quantity][/FONT][/COLOR]
[FONT=Verdana][COLOR=black]HAVING ((([B][COLOR=#2e8b57]AWQ[/COLOR][/B].Isotope)=[Which isotope do you want to track?]) AND [/COLOR][/FONT]
[COLOR=black][FONT=Verdana]  (([B][COLOR=#2e8b57]AWQ[/COLOR][/B].[Date (dd/mm/yyyy)]) Between [start date?] And [end date?]));[/FONT][/COLOR]

I have reformatted the query and replaced [All Withdrawals Query] with AWQ for each column to make it easier to read, and now I have a few observations:

1. You are SELECTing your information FROM something called [All Withdrawals Query]. Is this another Query, or is it an unusual name for a Table? If it is a Query, we may need to evaluate it as well.
   • Show the Inner Query if there is one.
2. This query as written does not require a Group By Statement, since it is not grouping any of the columns. I expected to at least see the column AWQ.[Amount Withdrawn] being Summed, Counted, or otherwise Grouped By some method.
   • Change the Select Statement from AWQ.[Amount Withdrawn] to Sum(AWQ.[Amount Withdrawn])
3. Some of the Column names contain Non-Alphanumeric Characters like "(", ")", "/", and Spaces. These types of Column/Table/Query Names should be avoided if it is possible to do so.
   • Change Column/Table/Query Names to have only Alphanumeric Characters if it is possible to do so.

RED = Issue to Consider Resolving
GREEN = Modification to Consider and Evaluate

 

[my_brain_hurts]

Hi

Thanks for your help with this. In the end it turned out that I'd been trying to put too much into one query. An extra query did the trick and it's working now as I'd hoped. Many thanks once again for taking the time to respond to my post