• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 212
  • Last Modified:

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

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.

  • 2
  • 2
1 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
wlwebbAuthor Commented:
Why in tblCustomer ????

The transaction data is in tblQBGLData

The "Items" are in tblQBInventory

wlwebbAuthor Commented:
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.

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now