Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2020
    Posts
    2

    Report table with compound primary key

    I have a table that utilizes a compound key for parts tracking. Below is an example table:

    Set Part Type Status
    A Orange Out for repair
    A Apple Installed
    A Banana Scrapped
    B Orange Installed
    B Apple Sold
    B Banana Ready for Installation

    My "Set" and "Part Type" are used for my Primary Key. I would like to develop a report that outputs a table summarizing the status of all parts. Example report below:



    Set Oranges Apples Bananas
    A Out for Repair Installed Scrapped
    B Installed Sold Ready for Installation

    Does anyone have any ideas what the best approach would be for this?

    By way of my own background, I am VERY VERY new to Access, and database design in general. I have developed very complex and vba-intensive excel spreadsheets in the past, so it's not like this is my first time in front of a computer. However the learning curve to Access has proven steep and difficult, so thanks in advance for your patience .

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    make Q1 to pull the data you want,
    use the QUERY WIZARD, and create a CROSSTAB QUERY, choose your source query (Q1 above)
    it will ask you to pick
    Row items: SET
    col. items: PART TYPE
    the intersection value: STATUS

    save this xtab query.

  3. #3
    Join Date
    Oct 2020
    Posts
    2
    Awesome! this worked mostly great!

    For others who find this post, Access (in my case, Office 365) defaults to a "count" calculation, which didn't work for my purposes. However if I changed the "Total" to either "First" or "Last" and set the crosstab to "Value", it did exactly what I wanted.

    Thanks again!

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

Similar Threads

  1. Complex Query and Compound Primary Key
    By zashaikh in forum Queries
    Replies: 10
    Last Post: 04-21-2017, 03:09 PM
  2. Compound IF trouble #error
    By wcrimi in forum Programming
    Replies: 7
    Last Post: 11-21-2015, 03:36 PM
  3. Trouble with Compound Query
    By bxdobs in forum Queries
    Replies: 3
    Last Post: 02-20-2015, 11:42 AM
  4. Replies: 4
    Last Post: 12-18-2011, 05:55 PM
  5. Creating Compound summations etc.
    By ohthesilhouettes in forum Queries
    Replies: 2
    Last Post: 06-19-2011, 12:29 PM

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