Summing values in multiple columns if certain criteria are met

[grego9]

I have a table that has the following data in it

"Deal Number" "Profile 1 Amount" "Profile 1 Date" "Profile 2 Amount" "Profile 2 Date" all the way through to "Profile 10 Amount" "Profile 10 Date"

The profile date could be anything from today to a date in 10 years in time and can be different for each deal number (so Profile Date 1 is not the same for all deals)


I want to create a query that can sum the (profile) amounts into half yearly buckets. So I would get the following:

Deal Number Half 2 2010 Amount Half 1 2011 Amount Half 2 2011 Amount


Where the Half 2 2010 Amount is a sum of all the Profile Amounts (from 1-10) where the Profile dates (from 1-10) are between #1/7/2010# and "31/12/2010"

I then want to do the same for each half year up to 2020

Are there any quick/clever ways of doing this - I am stumped!

Thanks for your help

 

[highandwild]

It seems to me that your data needs to normalised instead of having fields containing similar data repeat themselves in the same record.

Keep your tables long and thin rather than short and fat.

e.g.
DealNumber,ProfileNumber,Amount,ProfileDate

so you would potentially have ten records instead of one.

Once you have done this then there are many more options open to you, in terms of reporting on the data.

Is this possible?

 

[grego9]

I can see the logic in that. Unfortunately I can't change the way I pull in the source data. Unless you know of a clever way of converting the format I have to the one you suggest I think I may have to find another way!

 

[highandwild]

So....Normalise the data by means of 10 queries to produce a table form which the analysis can be carried out.

Remember to create this table from scratch each time you want to do the analysis if the data is being updated.

All of the fields in all of the records may not have a value so build that into the queries.

 

[grego9]

being a bit slow here! what would the queries look like and what type of query would it be.


I think I can figure out what to do once I have got the final table - but I am bit puzzled as to how I get there!

 

[highandwild]

Are you able to post a database containing the table with the data in it?

 

[grego9]

I would love to - but the data is a bit sensitive I'm afraid. I was looking at the structure and figure that I could do 10 Append Queries into a master table. Each query would have an If statement so that it only posted the details for each profile (1-10) into the master table. I would obviously then need to change the column headings so that they were more generic eg profile number profile amount profile date rather than profile 1 amount, profile 1 date etc.

I will need to clear this table out each time I run the query - but I shouldbe able to figure this out.

Thanks for helping to think through this one!

Let me know if you would do if differently!

 

[highandwild]

The data is only sensitive in the context of your organisation.
The number is just a number, the amount an amount and the date just a date. All data that I can generate.

So...

Create a new table with these fields in it
Deal Number
Profile Amount
Profile Date
same field types as in the existing table.

Create an append query which appends
Deal Number"
"Profile 1 Amount"
"Profile 1 Date"
to the new table where the amount and the date are not empty

Create another append query which appends
Deal Number"
"Profile 2 Amount"
"Profile 2 Date"
to the new table where the amount and the date are not empty

and so on for the 10 sets of fields.

No need for an If statement or changing the column headings in the query. Just make sure that you append the right and not the same data more than once.

Create a query that deletes all data from the new table.

Create a macro which runs the delete query and then the other 10 querties in sequence although the order in which you run them is not
crucial.

Run this macro each time you need to do any analysis on the data and there you are.

I am assuming that the profile number is insignificant.

 

[grego9]

Perfect - thanks for your help. I cuoldn't see how to do this at the start - but I can really see the logic now.

thanks for your help

 

[highandwild]

I'm glad that you can see the logic now.

Managing data is all about applying techniques and rules but they take years to learn and understand.

I'm off to the beach now so you are on your own....