Stock usage Query totals

[mick2000]

Good afternoon,

I have built a simple production tracking DB to track orders from placement through to dispatch by courier. As I am a novice in Access, I have built most of what we need okay, however 1 query / report eludes me.

A reporting function of the DB is to monitor stock usage. As the business makes tailored car mats, reports show how many of each carpet, rubber types ordered, types and colours of trim and logos etc. This will aid in future stock planning.

The query / report that is a problem is the Retaining Clip aspect of our product. We stock and utilize various retaining clips for all makes and models of vehicles. These are inserted into the car mats as appropriate. Each order imported to the DB shows the type of clip & the number of those clips for that car mat pattern. The Clips are named, by Alphabet for ease of production ie A A+Screw B C and so on. This is noted in the Clip Type field of the production database & subsequent Material Query. However, the next field related to the clip is the No of Clips, this can be anything from 0 to 10 depending on the vehicle make and model.



The Query is date related, giving the the number of each material type used each date or date range. While I can get the query to show the number of times the Clip Type appears, I am not able to get it to total the no of Clips of each type used.

I have attached screen shots of both Query and Report. I am sure I am just making a newbie, simple mistake but have spent a long time trying to suss it out.

Any help or guidance would be greatly appreciated.

Regards

Mick

[orange]

Duplicate while editing.. see next post

[orange]

Try putting SUM instead of Group By in the "NO Of Clips" column in the query criteria. If that doesn't work, then we need to see some sample data in order to mock up your situation and do a little experimenting.

[mick2000]

Thanks Orange, but had tried that still no evail. How do I upload Sample data? I take it that will be DB, query & Report but not sure how to upload.

Your help is greatly appreciated.

[orange]

Do a compact and repair.
Create a zip file and add your db to be posted.
When you create the post, there is a Go Advanced button at bottom of the editing area, click Go Advanced
There is a Manage Attachments button, click on it and you'll get a dialog asking the file name to attach, browse to it, upload, then save your post.

[mick2000]

Thanks for the idiots guide. I needed it. lol

Dbase attached. The areas concerned are:

Clip Type Totals - Daily Report & Material Query - Daily.

Hope this what you need.

Thanks again.
Mick

[orange]

I have looked at your query
It seems you are wanting to Sum the NoOfClips, but that field is defined as short text.
There are values like "VEL" and "no clips" that are strings, and, while they are valid stings, they can not be summed.

When I use this query:

SELECT  Production.[Process Date],Production.[CLIP TYPE] AS ClipType, Sum(Production.[NO OF CLIPS]) AS ClipsUsed
FROM (SELECT [Process Date],Production.[CLIP TYPE], Production.[NO OF CLIPS] 
FROM Production
where Isnumeric(Production.[NO OF CLIPS]) and Production.[Process Date] = #4/6/20#
)  
GROUP BY Production.[Process Date],Production.[CLIP TYPE];

I get this result

Process Date	ClipType	ClipsUsed
06-Apr-20	A	36
06-Apr-20	A+Screw	198
06-Apr-20	B	42
06-Apr-20	C	291
06-Apr-20	D	330
06-Apr-20	F	54
06-Apr-20	G	70
06-Apr-20	H	52
06-Apr-20	I	65
06-Apr-20	J	32
06-Apr-20	K	27
06-Apr-20	L	44
06-Apr-20	METAL	66
06-Apr-20	N	33
06-Apr-20	O	76

But notice that I had to supply a specific date. When i tried to use a parameter, [Enter Date] and supplied 4/6/20 I got no results???

I did a summary of Clips used between 4 Apr and 9 Apr using this query

SELECT  Production.[CLIP TYPE] AS ClipType, Sum(Production.[NO OF CLIPS]) AS ClipsUsed
FROM (SELECT [Process Date],Production.[CLIP TYPE], Production.[NO OF CLIPS] 
FROM Production
where Isnumeric(Production.[NO OF CLIPS]) and Production.[Process Date] between #4/6/20# and # 4/9/20#
)  
GROUP BY Production.[CLIP TYPE];

and got this result

ClipType	ClipsUsed
A	82
A+Screw	379
B	84
C	820
D	922
F	380
G	152
H	92
I	128
J	80
K	62
L	94
METAL	90
N	63
O	158

Your tables are not normalized. This will become an issue at some time and will cause you to create workarounds.
Names with embedded spaces will also lead to syntax errors - correctable, but better to avoid them.

I don't know enough of your environment or requirement to offer more.

I recommend you review your requirements and start by Normalizing your tables. They are the base of a successful database.
Hope this helps.
Good luck.

[mick2000]

Thank you. It will take me a little time to read through, understand, make changes and get back to you.

Thanks for the help thus far.

Mick

[orange]

If you are trying to get total Clips used, then you do not need all the Grouping. I think your requirements have to be described in more detail for you to be able to focus on specific things. It's a big project for a "novice" and I would say better to get the basics - requirements, Normalization, naming convention -understood and in place. Make a test model and work your requirements/design/test cases against the model.
The links in my signature may be helpful to you.