473,320 Members | 1,832 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

How do I create a report for our inventory?

dgaletar
72 64KB
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
Jan 11 '13 #1
15 4700
Rabbit
12,516 Expert Mod 8TB
Can you post the table layouts and some sample data? It's difficult to see the table/data relationships from the description alone.
Jan 11 '13 #2
dgaletar
72 64KB
Various images posted in separate posts - none of which were visible enough to be any use.
Jan 11 '13 #3
Rabbit
12,516 Expert Mod 8TB
Those screenshots are too small to see, can you type it out instead?
Jan 11 '13 #4
dgaletar
72 64KB
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?
Jan 11 '13 #5
Rabbit
12,516 Expert Mod 8TB
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.
Jan 11 '13 #6
dgaletar
72 64KB
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?
Jan 11 '13 #7
NeoPa
32,556 Expert Mod 16PB
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).
Jan 12 '13 #8
dgaletar
72 64KB
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?
Jan 13 '13 #9
NeoPa
32,556 Expert Mod 16PB
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.
Jan 14 '13 #10
dgaletar
72 64KB
"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!
Jan 14 '13 #11
zmbd
5,501 Expert Mod 4TB
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.
Jan 14 '13 #12
NeoPa
32,556 Expert Mod 16PB
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.
Jan 14 '13 #13
dgaletar
72 64KB
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!
Jan 14 '13 #14
NeoPa
32,556 Expert Mod 16PB
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.
Jan 14 '13 #15
NeoPa
32,556 Expert Mod 16PB
dgaletar:
you were WAY overworking the problem
When you accuse me of WAY overworking the problem, you should appreciate that all I was trying to do was to explain the (very straightforward) solution already suggested by Rabbit. My contribution was merely to attempt to translate that into something that you could understand as it seems you were unable to follow the suggestion as posted (in ordinary database parlance). After my first attempt you stated you could neither understand aggregation nor the simple joining of tables together in a query. In the circumstances, finding any wording to try to explain these simple concepts to you was surely a job well done. Very few would even have attempted it (or hadn't you noticed).

I have no complaint about anything you've posted. Frankly, if you want to embarrass youself in the eyes of the world it is no worry of mine. I simply correct anything that I feel is totally insupportable when I see it.
Jan 14 '13 #16

Sign in to post your reply or Sign up for a free account.

Similar topics

8
by: Donna Sabol | last post by:
First, I should start by saying I am creating a database to be used by some very impatient, non-computer literate people. It needs to be seameless in it's operation from their point of view. I...
2
by: Jean-Claude Bertrand | last post by:
Hello to all .Net programmers! I am a consultant and I create very small application for small enterprise. Most of the time in my application, the number of users is under 5! I still program...
1
by: tbehmand | last post by:
Hi I am getting the following message when I click on 'Create report in Design view', does anyone know how to fix this. "There was a problem retrieving printer information for this object. The...
1
by: alem | last post by:
Hi all help me how to create Report in SQL Server or SQL Query Analyzer. thank you in advance
6
by: alem | last post by:
Hallo pls help me how to create Report in Visual Basic.
1
by: SantyBhi | last post by:
Plz , any one can explain how to create report in c#asp.net without using Crystal Report.
0
by: bdmir | last post by:
Hello, I have a text file(tab delim) with data and I want to create report. Here is the content of text file: HD John Smith j.smith@testmail.com CT service 1 5$ CT service2 1 6$ CT service3 4...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.