Access - How to Get Summary Totals that include an Item that is Blank

Posted on 2011-05-13
Last Modified: 2012-05-11
Hello Experts,

I have attempted to get the desired result multiple ways in Access 2007 but alas I don't know enough to get it to do what I want.

I have a dump of a General Ledger system's (GL) data that I am using Access to summarize some data for me.  The export from the GL system is always in the same layout that I then import into Access.

The two fields with important data that I am using to get summaries for from the GL system are:

Whenever the user is keying in information to the original GL system they generally assign the item being purchased to an [Item]  But they don't always assign it.  So on a lot of occasions that [Item] field is blank.

Whenever the cost/expense that is being keyed in into the GL system if it is a cost/expense associated with a "Job", then the system puts the "CustomerJob" name in the field [GLDataName} and it always puts who the expenses is paid to in a field called [SourceName]
I have created a table called tblCustomer so that whenever the field {GLDataName] has an entry that is associated with a Job I can summarize by that name.  

I have created tables called:

I brought all of the "Items" that are used in the field "Item" into that table tblQBInventory and then assigned each to tblJCInventoryItemGrouping

What I am trying to summarize is All costs for a Job by Job Name and InventoryItemGrouping  whenever the expense is associated with a Customer.  I can get it to work if the user input an {item] in the appropriate field.  But if they left that field "Blank" then it ignores it.  Is there a way to get my query to include "blank" [Item].

I tried adding an Inventory [Item] where the name field was blank and that didn't work.

Question by:wlwebb
    LVL 42

    Accepted Solution

    Recommended solution:
      1. In tblCustomer design, change the Item column to required, and zero length not allowed.
      2. Load a placeholder constant like "unknown" for the missing data.  Ideally you can do that in the import
      3. add an item for "unknown" and assign it to a group
    Alternative solution:
      1. create qryCustomer like this:
      select *, nz(item,"unknown") as DerivedItem from tblCustomer
      2.  add an item for "unknown" and assign it to a group
      3.  Use qryCustomer instead of tblCustomer in your report.  Be sure to join on DerivedItem column instead of the Item column

    Author Comment

    Why in tblCustomer ????

    The transaction data is in tblQBGLData

    The "Items" are in tblQBInventory


    Author Closing Comment

    Ahhh "I see said the blind" newbie.

    Used the tblQBGLData instead of customer since that is where the data is.  

    I think I have it now.

    LVL 42

    Expert Comment

    My confusion, as I didn't see any mention of tblQBGLData in the question.  Just guessed that your detail was in tblCustomer.  Glad you were able to run with it.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Suggested Solutions

    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now