Dynamic query based on "formulas" stored in table

[nate11000]

Hi,

I have a table that looks something like this:


Company Product Sales
1 A 5
1 B 2
1 C 7
2 A 8
2 B 1
2 C 2


I want a query to produce a table showing Products A though D which is
the sum of Products A and B. Result:


Company Product Sales
1 A 5
1 B 2
1 C 7
1 D 7
2 A 8
2 B 1
2 C 2
2 D 9


I have many rows and many summations and the current union query and
supporting queries are pretty messy and not easily changeable. What
I'd like to do is have a third table which would hold the summed
product names and the formula used to get to it (Product D, A + B) and
have a query that can be dynamically based off of that table. Do I
need to use VBA? Does anyone have a better idea? Any help or even a
good search term would be appreciated!


Thanks!
Nathan

 

[ajetrumpet]

You might have to use code; or more than one query. Not sure you could get the addition done in one step. DSUM will not work, because the data has to grouped, and Domain functions don't support that. I would use query after query, even if you have to go one small step at a time. It will at least save you the hassle of figuring out the coding.

 

[gemma-the-husky]

you can create your table easily enough with a maketable query, which should do what you want

i shouldnt think you need vba for it, but this depends on how you want to automate this (and similar procedures)

often you only need vba to make things robust etc. in this case, vba could automate this process for you (but perhaps you could do this with a macro also)

 

[nate11000]

Thanks for the replies!

The problem with doing it in small steps is that there are so many of them that I have a whole slew of extra queries, with a union at the end, and then a maketable on the union. The second a definition changes or a new summation is added, I end up digging through the pieces again.

 

[m.shakeebahmed]

it depened how often you need to run this?? because with VBA it is a straight forward case of having a recordset and add on increment.... however it will not be certatinly the most effeicient solution as you have mentioned it is a heavy populated table....

 

[ajetrumpet]

So, what do you want to do Nate? You know now, what you can't do. Everyone has made that clear.

 

[gemma-the-husky]

there is only a couple of steps

1. do a maketable query (thinking about it, you may even be able to get your D figures with a simple totals query) to create your table with the D data

2. then union the main table with the totals query


job done surely

 

[nate11000]

Thanks for all the replies. Since I have 33 "core" codes and 20 "calculated" codes (calculations based on addition and subtraction), continually maintaining make tables and a large union query didn't seem feasible without a lot of grief. I ended up writing a macro to make a new table with the "core" parts as a base, read in the formulas, union the pieces, and append the results to the final table.

It works as I was hoping. It wasn't as difficult as I expected, but being my first Access VBA project, it wasn't THAT easy, either.

If anyone else needs help with a similar project, please let me know. I'd be happy to share what I've worked on.

Thanks again to everyone who replied!