Sum fields from two tables in one query (1 Viewer)

[DenverGuy]

I have two tables:

Budget
Actuals

I have these fields in both tables:
MONTH, Organization Code, Account, SubAccount, AMOUNT

I want to Compare the results in a single query. However the amounts aren't correct and after a full day I cannot find the answer. any help would be greatly appreciated.

SELECT [Budget].Month AS [MONTH], [Budget].[Organization Code], [Budget].Account, Sum([Budget].AMOUNT) AS [Budget Amount], Sum([Actuals].AMOUNT) AS [Actuals Amount]
FROM [Budget], [Actuals]
GROUP BY [Budget].Month, [Budget].[Organization Code], [Budget].Account, [Budget].SubAccount
HAVING ((([Budget].SubAccount)="K08"));

Thanks
DenverGuy

 

[MarkK]

Imagine how much easier your life would be if you had one table with these fields:

MONTH, OrganizationCode, Account, SubAccount, AMOUNT, [COLOR="Red"]IsBudget[/COLOR]

IsBudget would be a boolean field, and it would be true for . . . maybe no explanation required.

SELECT [Month], OrganizationCode, Account, Sum(IIF(IsBudget, AMOUNT, 0)) As Budget, Sum(IIF(IsBudget, 0, AMOUNT) As Actual
FROM YourTable
WHERE SubAccount = "K08"
GROUP BY [Month], OrganizationCode, Account

See how the IIF() can now be used to isolate values from the same field, and tease them apart into different fields?

Put all data with the same structure in the same table. Tell it apart by the value of its fields.

 

[plog]

You have a few issues. The biggie is that this data shouldn't be in seperate tables. Whenever you have multiple tables with the same structure, that data should be in one table that has an additional field to differentiate it. So you would put all this data in one table with a field that lets you know if the row is for budget data or actual data. I would make that a Yes/No field and name it 'IsBudget'. All the records that are true are budget rows, false means actual.

Also, 'MONTH' is a poor choice for a field name because it is a reserved word in Access (http://support.microsoft.com/kb/286335) and makes coding and querying a little harder.

Lastly, you used the word 'compare' in your post, but your query just seems to put the two amounts together--no subtraction, delta calculation, etc.. Can you post some sample data of what you would like the end result to be?

 

[plog]

That's scary. Right down to the name of the new field.

 

[MarkK]

Lol. Great minds.
Cheers,

 

[DenverGuy]

Thanks for trying to help me out, I truly appreciate it. I will try to do my best to clarify.

The biggie is that this data shouldn't be in separate tables. Whenever you have multiple tables with the same structure, that data should be in one table that has an additional field to differentiate it

I agree, however, the data is coming from different sources (Budget system & Accounting System). So, I am trying to combine the data into one table.

I am not sure the IsBudget suggestion will work. That or I don't understand it. It's not really an OR scenario. Every combination of Month/OrganizationCode/Account has a Budgeted Value AND an Actual value. This is essentially a variance query...(once I get the aggregation to work, I'll add the Delta as a new field).

What should I change the 'MONTH' field to? sMonth?

Thanks

DenverGuy

 

[plog]

the data is coming from different sources (Budget system & Accounting System).
I am not sure the IsBudget suggestion will work. That or I don't understand it. It's not really an OR scenario

It absolutely is an OR situation--the data either comes from the Budget source OR the Accounting source. So the IsBudget field flags it as such. However, if you don't have control and just get an export every so often that might change the approach.

the data is coming from different sources...Every combination of Month/OrganizationCode/Account has a Budgeted Value AND an Actual value

I will take that bet everyday and lay odds. Never trust assumptions about data; yours, another's and especially "common knowledge". Everytime you get new datasets you should test this to make sure.

Since the data isn't under your control, can you better explain the process? How do you get this data--linked to it, dumped on a network directory every so often, go to a web app and download it on a schedule? Also, what is the ultimate end product. Honestly, it sounds like you are asking us to help you with an intermediary step that probably isn't necessary.

 

[DenverGuy]

Hey PLOG, Thanks for the quick response. Honestly I thought this was going to be a super easy answer and I was going to kick myself for not finding it on my own. I guess it is more complicated than I thought. It seems so straightforward, I am wondering if somehow I have complicated it.

It absolutely is an OR situation

So perhaps I don't understand this approach.

I would make that a Yes/No field and name it 'IsBudget'.

Where would I add this field, the budget table? Then I would mark it as true?
I have some control, currently we import the data from a csv file from the respective systems. Ultimately I will set these table to update via ODBC links from their respective sources.

I will take that bet everyday and lay odds. Never trust assumptions about data

I think I was a bit haphazard with the word EVERY. I understand what you mean. Data can be marginalized in a number of ways.

You are right, this is an intermediary step. I attached a file that shows what I am looking to achieve. I have a series of reports that we'll be using this "approach" to help us analyze trends.

Thanks
DenverGuy

 

[plog]

No file was attached. An embeded screenshot should work.

Now that we know that the control of the data tables are outside your control, the IsBudget field is probably not the way to go. Of course, you scare me with the phrase '..set these table to update via ODBC links' -specifically the term 'update' ("Ultimately" always makes me shake my head as well).

If you have access to the tables and are able to link to them, then most likely you should just build your queries off those tables. At this point I know neither the starting nor ending point of your data. Can you post something so I know the beginning and end points? The structure of those linked tables, including field and table names, and then the end result.

 

[DenverGuy]

Hey Plog,

I guess the newer excel version is not a valid datatype to upload and I didn't quite realize that initially.

Whats wrong with ODBC connections? or the word ultimately? haha

I really have no idea how to answer your last question.

We have a number of tables, many from other systems. I am trying to consolidate these into some useful queries. Actually I am taking this over from someone to clean/enhance the DB for an number of analytical purposes. I have a strong excel background and I am pretty competent, I just never had to use access until now. So this has been frustrating as you might be able to tell.

I am open to any suggestions.

I spent the day building union queries to change the data from having the 12 months as 12 different amount fields. To a single month field and a single amount field. Hey.

Thanks again.

DenverGuy

 

[plog]

Nothing wrong with ODBC connections--in fact a linked table using an ODBC is the preferred method. It would remove the export/import step which means less time spent and the less potential for user error. The word that concerned me was "update". It can mean a lot of things--I didn't know if you were actually moving data around from table to table. "Ultimately", in my experience, is a business term that means "we should do it this way from the start, but instead here's an empty promise we won't fulfill".

If your data sets have more than 1 record for each OrganizationCode, MONTH and Account you should create a sub query for each that GROUPS BY those fields and sums by the Amount.

For the query you want you should create a query in design view, bring in both your data sets (budget and actual) that are unique by OrganizationCode, MONTH and Account and then link them both by those fields. Bring down OrganizationCode, MONTH, and Account from one table, the Amount field from both tables and then make a calculated field for DELTA that subtracts one Amount field from the other.