Hi everyone,

Sorry to ask a specific question with my first post! I've been playing around with Access 2007 for a couple of weeks without asking anyone, and have Googled and searched this forum but I think a new thread is necessary...

The end task is to generate a (hexadecimal) code to indicate which components of a sales package have been selected. I have a table with the following fields (separated by pipe symbols):

SalesID (AutoNumber, from 0 to 19) | SalesPackageName (Text) | IncludedInPurchase (Yes/No)

This information is also in a query, which has an addition column to calculate a "decimal code". This decimal code is calculated using the expression: DecimalCode: Abs([Included])*2^([AnalSalesID] Mod 4). "Abs([Included])" returns a 1 or 0 depending on whether the [Included] field is selected as Yes or No. This binary digit is then multiplied by 2 to the power of ([AnalSalesID] (which is the primary key and acts as an index for the purpose of identifying specific records) Mod 4 because I want to end up with a hexadecimal number (which is in base 16).

This "decimal code" results in a number which represents a number of bytes, and reads 0 (zero) if the [Included] field is set to 'No' and either 1, 2, 4 or 8 if the [Included] field is set to 'Yes'. I hope this makes sense so far.



What I need help with is how to sum the "decimal code" column in groups of four records at a time. For example, if the first four Sales Packages are Included (i.e. the [Included] field is set to 'Yes'), the sum of that group of 4 should be 15 (1+2+4+8), which I will then convert to a hexidecimal number (i.e. F).

I have tried making four more queries (nested?) from the first query, which each have an additional column called "Group", which is calculated using ([AnalSalesID] Mod 4) in order to identify every four records as being either in "Group" 0, 1, 2, or 3. Each of these four queries then has a simple filter (using the Criteria field property) so that each query only displays the data of that group (for example, qryDecimalCode1 displays only the data in Group 0, which is SalesID numbers 0, 4, 8, 12 and 16).

To reach my last stage (the hexidecimal number), I still need to sum the "decimal code" numbers in groups of four. Am I on the right lines?

I'm sorry for explaining things in so much detail... I just thought that perhaps seeing exactly what I am trying to do might help you all to help me! Indeed, you may advise me to change the structure of my database altogether, if I've gone about it in a convoluted way. Particularly, I am aware that there are no relationships between queries, so I am wondering if the link between the initial data in the table and the final hexidecimal number will be sufficiently "dynamic" the way I am going about it? In other words, I would like the final hexidecimal number to change as you select or deselect the [Included] field in the original table or column.

Thank you all SO much in advance if you can help with this!