How do I create a report for our inventory?

[dgaletar]

To start off, I am a serious "newbe" to Access, and have probably made countless mistakes thus far in the design of this database.

I work as a fleet mechanic for a University in DC, and am trying to keep track of the maintenance on our vehicles. To do this I created a pretty simple Access 2010 database.

Everything was going pretty well until I realized that I couldn't go any further without some help... so here I am!

In an effort to maintain an inventory of certain parts for our fleet vehicles (i.e. tires, batteries, oil filters, etc.), I need to count how many of each item we should keep in stock.

Here is what I have created thus far:

1. A main form/table consisting of each vehicles basic info (i.e. truck number, license plate numbers, VIN numbers, etc.). The primary key for this form/table is the truck number that we have assigned to it.

2. A sub-table/form on the main form that hosts the specific "drivetrain " information for each of these vehicles (i.e. engine size, transmission type, seating capacities, etc.).

3. Another sub-table/form on the main form that hosts each vehicles specific parts used.

• The second sub-table pulls all of its data choices from separate tables that host the products used themselves (i.e. tires: table = holds a listing of all of the tire sizes that these vehicles use; batteries: table = holds a listing of all of the batteries that these vehicles use; etc.).

My goal is to be able to print a report that shows us how many of each of those items (i.e. tires, batteries, oil filters, etc.) we should keep in stock (i.e. 10 of our vehicles use a 205/75R/15 tire, so we should keep at least 10 of them in stock).

Can anyone help me to figure out the best way to do this???

Thank you in advance for your assistance.

dgaletar

[Rabbit]

Can you post the table layouts and some sample data? It's difficult to see the table/data relationships from the description alone.

[dgaletar]

Various images posted in separate posts - none of which were visible enough to be any use.

[Rabbit]

Those screenshots are too small to see, can you type it out instead?

[dgaletar]

Um... I can sure try...

The Main table is laid out as follows:
- Column 1: CUA# = Primary Key; A number that we have assigned to each specific vehicle.
- Column 2: Tag# = the license plate for that vehicle.
- Column 3: VIN# - each vehicles vehicle identification number (from the factory)
- Columns 4-8: Year/Make/Model/Trim Level/Seating for each vehicle.
- Column 9: when the next state inspection is due for that vehicle.
- Column 10: the department that uses that vehicle.
- Column 11: the alternate ID used by that department for that vehicle.
- Column 12: when the vehicles license plate requires renewal.


The Service Specs table is laid out as follows:
- Column 1: CUA# = Not the Primary Key for this table (there is none).
- Column 2: Tires = A drop down box that isolates which tire size is used for this vehicle (this info is drawn from the TIRES : Query).
- Column 3: Wheel Torque = Just a number so we know how tight to tighten the wheels on the vehicle.
- Column 4: Type of Oil = A text field that notates which type of oil is used for this vehicle.
- Column 5: Qty of Oil = How much oil this vehicle uses.
- Column 6-8: Oil, Fuel & Air filters used = Each has a drop down box that isolates which filter is used for this vehicle (this info is drawn from the Oil : Query, Fuel : Query or Air : Query).
- Column 9: Brake Fluid = A text box that states which type of brake fluid is used.
- Column 10: Battery = A drop down box that isolates which battery is used for this vehicle (this info is drawn from the Batteries : Query).
- Column 11-12: Tire pressure for front and rear tires.


The Tires table is laid out as follows:
- Column 1: ID = autonumber
- Column 2: Tire Size = A text box to enter new tire sizes.

This table has a specific "Pop-up" form that ONLY allows the user to enter new sizes. It then goes through the query where they are sorted into ascending order, and then it is populated in the "Service Specs" table/form.

The Fuel Filter, Air Filter & Batteries tables/forms will be based off of this table/form.

Does any of this help you at all?

[Rabbit]

That helps. The query you need to create is one that joins from the main table to the service spec table. You then do an aggregate query by counting the rows and grouping by the tire size.

[dgaletar]

I'm sorry if I'm asking too much, but... what? I'm afraid u r talking over my head now. Could u explain in more detail, &/or give me an example?

[NeoPa]

Dgaletar,

If you find the help you're being offered is too complex for you then please take the trouble to explain what you are stuggling with specifically rather than simply asking for all the effort to be done by others. You have put some decent information together, which shows you're prepared to get involved, but please remember this is your problem we're trying to help with. If you're stuck then it's your responsibility to explain where rather than ours to do it all for you. You're not asking too much, but nor are you asking it the right way (if that makes it easier to understand).

Do you understand what is meant by :

Rabbit:
The query you need to create is one that joins from the main table to the service spec table.

Do you understand what is meant by :

Rabbit:
You then do an aggregate query by counting the rows and grouping by the tire size.

Neither of these is dealing with very advanced concepts, but if you specify where you need help we'll do what we can to provide it for you. As this is at a pretty basic level though, we cannot guarantee you will understand unless you have at least some experience with databases and queries.

PS. Actually, looking back, you have certainly shown a good deal of effort, so I assume anything wrong with your posts shows simple inexperience rather than any bad practice. Please understand we are sympathetic of that. Just let us know what you need and we'll do what we can to nudge you along (but don't just ask for it to be done for you of course).

[dgaletar]

Hey NeoPa, thanks for your response. I apologize if it came out wrong, but I was never "just asking for it to be done for me". I was looking for help asking the next question... like your reply did.

Do you understand what is meant by:

Rabbit:

The query you need to create is one that joins from the main table to the service spec table.

No. I am VERY weak when it comes to Queries. I just can't seem to get a grasp on them!

Do you understand what is meant by:

Rabbit:

You then do an aggregate query by counting the rows and grouping by the tire size.

And again, no. This seems harder than the first one. (lol)

Can you now point me in the right direction?

[NeoPa]

Not understanding about JOINs in queries is a problem. I will try to explain, but it's at such a basic level that we are near the point where you just need to go off somewhere and learn the basics before we can communicate. Aggregation is, as you rightly surmise, somewhat more complicated. I'm not sure how well you'll grasp what I say without the basic understanding, but we'll try and see where we get to.

Let's start with the concept of JOINing tables. I've explained this as well as I know how in SQL JOINs. I suggest you start by looking at that. Hopefully it will make sense to you. I'm afraid that, if not, I am unlikely to be able to help without getting you here and in a classroom. There are other resources on the web, but my ability to help will have been exceeded for all practical purposes.

Aggregating is all about GROUPing rows together based on specified criteria. For instance, you may have a table of people who could be GROUPed together on Surname (or family). Such aggregated queries would return a single record for each Surname value, but they could also include counts and sums of various fields within the record. See Aggregate Query Woes for some of the issues related to that.

Please understand, we don't exclude people from asking questions that are too simple, but we may be practically unable to help in some situations if your understanding is too limited to grasp our answers. We like to feel we can respond at many levels of expertise, but there are practical limits to that.

[dgaletar]

"Not understanding about JOINs in queries is a problem. I will try to explain, but it's at such a basic level that we are near the point where you just need to go off somewhere and learn the basics before we can communicate."

No offense but this sounds like what my dad used to say when he tried to teach me the same thing the same way over and over, and then got frustrated when I didn't get it.

I will find someone else who has the ability to show me a different way to learn this stuff.

Thanks for the help!

[zmbd]

Dgaletar,

Certainly no offense is intended by anyone here... nor do we wish to push/put you off; however, the issue lies in the premise that one must have a basic understanding of a common set of tools in order to interact with others when working on a project/task.

What you are asking for requires that you have a basic understanding of the program and database design. And though, it is not beyond many of our abilities to teach you the basics, and indeed this is occasionally done for a specific topic; however, in this case, it is (IMHO) beyond the normal scope of this site to provide the in-depth tutorial covering all of the topics you'll need to master in order to use Access or any other Relational-Database-Management-System.

Another way of thinking about this is that you are asking us the equivalent to teaching you Organic Chemistry at a University level without having taken at least High-school/prep-school General Chemistry let alone the prerequisite Freshman-level General Chemistry class. Then being mad at, and yelling at, the O.Chem. Prof/TA/RA because you don't understand what is being taught then taking a personal offense when the TA suggests that you take a freshman class in Chemistry.

-
There are several very well put together sites that have such tutorials avaiable and if you drop down to the bottom of this page and click on the VBA-insights Sitemap link there is quite a bit of information available for you to learn from... for example, if you click thru, <ctrl><F> and keyword search "Query" there are 14 articles covering some fairly simple topics to stuff that might make the average person's head explode.

[NeoPa]

Z expresses the position I'm coming from very well. I'm not saying it's beyond me to teach at that level. Simply that it's beyond where I choose to offer my services for free here on this site. I wish your attitude surprised me, but unfortunately I've been exposed to that sort of attitude on far too many occasions to be surprised any more. Certainly disappointed though.

dgaletar:
I will find someone else who has the ability to show me a different way to learn this stuff.

You might want to add to that list that they also need the time and the inclination to deal with your level of understanding and attitude. Good luck with that.

[dgaletar]

OK, just to clarify, I do have a "basic" understanding of this project. My complaint is that you were WAY overworking the problem. I found the answer to it in this article:

{Illegal link posted} (& I used VII.5. Count).

Couldn't believe how simple this was to do! Once I found this article, it took about two minutes to finish the work.

I am not trying to be rude, but it seems like you are working really hard to impress your readers with your knowledge and abilities, when simply explaining it in a basic form could accomplish so much more.

Again, I am sorry if I put you off, and it was your response (Aggregate Query Woes) that had me research Aggregate Queries and ultimately find the solution, so... thank you. And best of luck to you also!

[NeoPa]

dgaletar:
My complaint is ...

About my spending my free time trying to help people like yourself? If you believe that is any way appropriate then I can see we have entirely different attitudes to life.

NeoPa:
we are near the point where you just need to go off somewhere and learn the basics before we can communicate.

It looks like the solution you found was done by following the exact advice I gave in my post anyway, as well as one of the links I posted. I can't imagine how harshly treated you must feel.

PS. The link you posted seems like it was a decent resource. It was edited away simply because it is not allowed for members to post links to competing sites in our forums.