Results 1 to 14 of 14
  1. #1
    Bobwords is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014
    Location
    Minneapolis, MN
    Posts
    28

    Creating report of devices in inventory

    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.

    Click image for larger version. 

Name:	Capture.PNG 
Views:	33 
Size:	79.0 KB 
ID:	16736



    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!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,549
    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.

  3. #3
    Bobwords is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014
    Location
    Minneapolis, MN
    Posts
    28
    Quote Originally Posted by ranman256 View Post
    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:

    Click image for larger version. 

Name:	6921.PNG 
Views:	29 
Size:	25.7 KB 
ID:	16738

    And the output:

    Click image for larger version. 

Name:	6921 output.PNG 
Views:	29 
Size:	42.3 KB 
ID:	16739

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

    Click image for larger version. 

Name:	Capture1.PNG 
Views:	29 
Size:	30.1 KB 
ID:	16740

    Click image for larger version. 

Name:	Capture.PNG 
Views:	29 
Size:	40.0 KB 
ID:	16741

    I figure I'm missing something pretty simple, but I'm not sure just what that is.
    Last edited by Bobwords; 06-09-2014 at 09:00 AM. Reason: posted the same picture twice in a row

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    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.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    rkl303 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2014
    Location
    Denver
    Posts
    6
    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).

  7. #7
    rkl303 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2014
    Location
    Denver
    Posts
    6
    See attachment
    Attached Thumbnails Attached Thumbnails Capture.PNG  
    Last edited by rkl303; 06-10-2014 at 11:00 AM. Reason: Didn't show formula

  8. #8
    Bobwords is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014
    Location
    Minneapolis, MN
    Posts
    28
    Quote Originally Posted by June7 View Post
    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.

  9. #9
    Bobwords is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014
    Location
    Minneapolis, MN
    Posts
    28

    Multi-Query into one Count output

    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.
    Click image for larger version. 

Name:	6921 stock query.PNG 
Views:	23 
Size:	25.6 KB 
ID:	16788
    So this^ gives the output below:
    Click image for larger version. 

Name:	stock query part 2.PNG 
Views:	23 
Size:	48.7 KB 
ID:	16789
    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:

    Click image for larger version. 

Name:	Final in stock querey.PNG 
Views:	23 
Size:	21.2 KB 
ID:	16790

    All this gives for an output is this:

    Click image for larger version. 

Name:	Capture.PNG 
Views:	23 
Size:	20.6 KB 
ID:	16791

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

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    Bobwords is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014
    Location
    Minneapolis, MN
    Posts
    28
    Quote Originally Posted by June7 View Post
    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.

    Click image for larger version. 

Name:	Capture.PNG 
Views:	17 
Size:	20.4 KB 
ID:	16797Click image for larger version. 

Name:	Capture1.PNG 
Views:	17 
Size:	15.8 KB 
ID:	16798

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    Bobwords is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014
    Location
    Minneapolis, MN
    Posts
    28
    Quote Originally Posted by June7 View Post
    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?

  14. #14
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Microsoft access on mobile devices
    By mike_980 in forum Access
    Replies: 1
    Last Post: 12-30-2013, 12:10 PM
  2. Replies: 5
    Last Post: 11-06-2013, 12:21 PM
  3. Creating a repair history for inventory items
    By skiskiacm in forum Database Design
    Replies: 1
    Last Post: 04-23-2013, 07:18 PM
  4. Mobile Devices
    By Drew1 in forum Access
    Replies: 0
    Last Post: 10-25-2010, 11:34 AM
  5. Creating an Inventory Form in Access
    By KIDRoach in forum Forms
    Replies: 0
    Last Post: 09-13-2009, 11:39 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums