Creating report of devices in inventory

[Bobwords]

Hello!



I'm totally new to building databases with Access, and could use some help generating a report that, I think, should be pretty easy to get, I just don't know how to go about it. I have an inventory I manage, and I want a simple report to show exactly how many of each device type I have in inventory, pulling from 3 different tables (one for each device type). I use the "date shipped" field to track if the device is in inventory, meaning if it is blank, the item is currently in inventory.



What I would like to do, is create a report that says:

x number of device type A
x number of device type B
x number of device type C

Followed by something showing:

In the past 30 days x number of device A have been shipped (x in the past year)
In the past 30 days x number of device B have been shipped (x in the past year)
In the past 30 days x number of device C have been shipped (x in the past year)

Not sure if it's possible to do graphs, but I'd love to do something that looks a little nicer with graphing for the report. Thanks for any help you can offer!

[ranman256]

Build a query (from the one shown) the does the counting you want.
Then use the REPORT WIZARD to auto-build the report.
(that's the fastest way).
Then analyse what got built.

[Bobwords]

Build a query (from the one shown) the does the counting you want.
Then use the REPORT WIZARD to auto-build the report.
(that's the fastest way).
Then analyse what got built.

Well, I'm having an issue trying to query all 4 tables at one time now. I built 3 different queries for the RMA table vs inventory (how I'm checking if the device is shipped), but I can't just combine all the data for the report (so far as I can tell).

Here is the setup for one of the individual queries:



And the output:



But when I try to run the query against all three tables at one time, I get nothing for an output:





I figure I'm missing something pretty simple, but I'm not sure just what that is.

[rpeare]

From the look of it you're splitting each one of your different 'categories' into a different subquery to list the non-shipped items, then trying to combine them all into a single query, is there a reason you're doing it that way or am I reading your screenshots incorrectly. There should be a method to do this in a single query without having to run all those subqueries if there's a way to define which items go in which bucket, though it may also be you actually have a 6921 table, I can't really tell. The other thing you could try is this. All your links in the final query require a value in both tables to be present for a link to be valid. if the table RMA Tracker is your 'master list' in other words it contains every single piece of equipment and your subtables/queries contain only a portion of that list you can try to connecting them via LEFT JOIN in other words double click the links between the RMA Tracker table and the subtables/subqueries and select the option that points FROM RMA Tracker TO the subtable/query. Then it will show you a list of every item i the RMA tracker that has a null MAC_ADDRESS and all the associated data from the subtables. Just be warned, that if you have multiple records in each of the subtables/subqueries you will end up with multiple rows per piece of equipment in your final query.

[June7]

Are these 1-to-1 relationships? Perhaps just need to change join type of each link from INNER to LEFT (or RIGHT, not sure which).

Or maybe do separate aggregate queries for each of the device tables then join those queries to a master dataset of MAC values.

Or perhaps do a UNION of the 3 device tables then do a CROSSTAB query.

[rkl303]

1st I think your Serial Number should be your Primary Key if it is the most unique element.
From what I gather you should have 3 tables(or 4).

Required
An Inventory Received Table Containing the Serial(/MAC address if it is as unique to the serial) (your Primary Key), Date Received
An Inventory Shipped Table Containing the Serial(/MAC address if it is as unique to the serial)(your Primary Key), Date Shipped
A table to identify the Serial/Mac Address(PK) to a product number (Item A, B, C)

Optional
Product Description Table Containing Product Number(your Primary Key), Vendor, Product Description, etc)

Then to calculate inventory available you use a left or right join on the received table and shipped table to the Serial/Mac to Product Number Conversion table so that the Product Number Table will always show and create a count of each serial and do the math (although if you insist on mentioning every serial/mac in the shipped table you will need to filter on the not null dates).

[rkl303]

See attachment

[Bobwords]

Or perhaps do a UNION of the 3 device tables then do a CROSSTAB query.

I've been trying to make a cross tab Query work, but it still seems to be out of my grasp. I think I found what might work for pulling the device inventory, I'm just having one problem with it still. I'll post what I've done so far.

[Bobwords]

Ok, so in trying to make this simple (I have down how to do basic queries pretty well now) I figured I could take 3 queries and combine them all into one counting query output. So I did the following:

For each device type, I create a simple query showing what MAC address' don't show as duplicates in the RMA tracker table. Again, the RMA tracker is the sheet we use for 100% of the billing, and tracking our shipping.

So this^ gives the output below:

So, what I was trying to do is get an output showing me what the count of each of these queries was. So I did the following:



All this gives for an output is this:



Any idea what I'm doing wrong here? Thanks for all the help so far by the way.

[June7]

What you have with "Count" is just a text value. You need to use Count aggregate function. Aggregate functions will be available for a Totals (Group By) query. With query in design view click the Totals button from ribbon. Access Help has more guidelines.

[Bobwords]

What you have with "Count" is just a text value. You need to use Count aggregate function. Aggregate functions will be available for a Totals (Group By) query. With query in design view click the Totals button from ribbon. Access Help has more guidelines.

So I added the additional criteria, but it still seems like it isn't pulling the data. Even when I'm not trying to get totals the query isn't giving an output, so I feel like I have something wrong in the basic design in the query.

[June7]

You are probably using INNER JOIN which requires related records in every table for records to display. Apparently the same Mac_Address values are not in all three tables. With this approach, need to join the 3 tables to a master dataset of ALL Mac_Address values.

You have separate tables and really should be one table with another field for the 7942, 6921, 7962 values. Then a CROSSTAB query should get you the desired output.

[Bobwords]

You are probably using INNER JOIN which requires related records in every table for records to display. Apparently the same Mac_Address values are not in all three tables. With this approach, need to join the 3 tables to a master dataset of ALL Mac_Address values.

You have separate tables and really should be one table with another field for the 7942, 6921, 7962 values. Then a CROSSTAB query should get you the desired output.

That makes sense. Is there an easy way to do a query that will put all three of these tables into one combined table? My thought was building a query that can add the MAC address' of each table to a combined table. Any idea on exactly how to do that?

[June7]

If you don't have a master table of all MAC address, can create one with a UNION query. See my comments in post 5.