Link to home
Start Free TrialLog in
Avatar of wlwebb
wlwebbFlag for United States of America

asked on

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:
[GLDataName]
[Item]

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:
tblQBInventory
tblJCInventoryItemGrouping

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.

ASKER CERTIFIED SOLUTION
Avatar of dqmq
dqmq
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of wlwebb

ASKER

Why in tblCustomer ????

The transaction data is in tblQBGLData

The "Items" are in tblQBInventory

Avatar of wlwebb

ASKER

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.

Thanks.
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.