Calculating the sum of every four records.

[Alanlives]

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!

 

[HAMMAMABUARQOUB]

What I need help with is how to sum the "decimal code" column in groups of four records at a time.

here is the question: do u sum the groups of four records all at one time or only group by gtoup! that's what i didn't get!
sorry!

 

[Alanlives]

Hi,

Thanks so much for your reply.

I need to create a Sum value for each group of four in the column, rather than ending up with one total value for the whole column.

For example, if the first eight values in the column are 1, 2, 4, 8, 1, 2, 0, 0... I want to calculate the sum of the first four values as 15, and the sum of the second four values as 3, and so on for the rest of the column.

Does that explain it better? Thanks for bearing with me!

 

[HAMMAMABUARQOUB]

Does that explain it better? Thanks for bearing with me!

yes a little,,, but my question was... if you d the calcilating of the (Fours) at one time.. i mean if you wan the result to display the sums of these (fours) groups at one time or the first, then the second or that you select...
coz if at one time, you need to reconstruct the tables into
1- Sales 2- Sales package(such that it takes the fours) if it's possible

or if you need the to sum the groups one after another, use a form which RecordSource comes from one group at a time, sum the filds you wan then display the sum or stor it in a table
i am sorry and thanks to u to for bearing my questions!!

 

[Atomic Shrimp]

I think it might help a bit if you could explain the real-world, non-technical side of it all - I'm having trouble getting my head around the descriptions of the data - because it sounds like you've chosen a really complex way to model it.

What does all this look like to the user? What real-world data does it represent and why are you (seemingly) trying to cram multiple values all into one field as a hex value?

 

[Alanlives]

Thanks for your reply, Atomic Shrimp. I've had that thought myself.

Okay, I am producing a piece of software that is sold as a base product plus any combination of additional features that the user requires. Think of it like a 'pick 'n' mix' with software options. Incidentally, these options are described in the database by the 'SalesPackageName' and 'SalesID' fields that I mentioned in my first post.

I want to use the database to keep track of 'who purchased what' - in other words, the different combinations of software options that each customer has bought. The database will (ideally) produce a license code which indicates to me who has bought what.

The trouble is, there are nearly 250 different options that a customer could choose. So, in order to prevent the license string from being 250-or-so characters long, I have been converting the binary choice (1 or 0, whether or not a given option is included) into a hexidecimal number.

Does that make more sense?

Thank you both for your help.

 

[David R]

The trouble is, there are nearly 250 different options that a customer could choose. So, in order to prevent the license string from being 250-or-so characters long, I have been converting the binary choice (1 or 0, whether or not a given option is included) into a hexidecimal number.

Why does it matter how long the license number is, if the database is producing/storing it? In any case, can't you just get the database itself to store the individual boolean choices, and reverse the process to get the sums you want?

It seems like you're making things needlessly complicated for yourself.

 

[Alanlives]

Hi David R, thanks for your contribution.

I should have mentioned, the license string has to become a string of hex values in order to be compatible with my license key generation software. Although I understand there are easier ways to do the job, I'm afraid it is a case of tailoring the database to meet the requirements of another program, and indeed staying true to the way in which things have been done before in Excel (I have made the desired type of license strings in Excel before, but I now need a database that can produce lots of these license strings all at once with an unintimidating front-end (i.e. a form view) that a salesman can use).

 

[David R]

Right, what I'm saying (I hope!) is that you can have the salesperson tickbox the options, then the database does some crunching and spits out the license code, neat as you please. I think we're saying the same thing two different ways.

I worked up what I think is a query field that will demonstrate, since I apparently need more caffeine before I can talk theory this morning:

LicenseNumber: Right(Format(Abs([item1]+[item2]*2+[item3]*4+[item4]*8),"00"),2) & Right(Format(Abs([item5]+[item6]*2+[item7]*4+[item8]*8),"00"),2)

 

[Alanlives]

Here is a screenshot of what I have done in Excel, with the formulae used in red. Could you help me recreate this in Access?

Thanks again to everyone for your input so far - I'm immensely grateful!

 

[Atomic Shrimp]

That does make a lot more sense now I know what you're trying to do. My suggestions:

Rather than trying to convert them to bit values four bits at a time, why not just give each option its own unique decimal value - ie. option E is 16, F is 32, etc. - then you can just add them all together in decimal, and convert that whole decimal number into a hex string (which should end up looking the same as via your intended method anyway - it's just a hex representation of binary yes/no bits)

Also: Have you thought of a method to validate the license key you issue? - I mean, self-validate it - to prevent your users ignoring the key you send them, and just entering FFFFFF... and getting every single feature enabled. It could be as simple as a checksum digit

 

[Atomic Shrimp]

To elaborate on that a bit, let's look at the first 8 options: if they're numbered:
Option A = 1
Option B = 2
Option C = 4
Option D = 8
Option E = 1
Option F = 2
Option G = 4
Option H = 8

And the customer has all of them, then the first block of 4 is 1+2+4+8 = 16 (Hex: F) - and the same is true of the second block of 4 - so the first two digits of the license is FF

Now, if we just number the options:
Option A = 1
Option B = 2
Option C = 4
Option D = 8
Option E = 16
Option F = 32
Option G = 64
Option H = 128

And the customer still has all 8 options, then we just add up 1+2+4+8+16+32+64+128 and get 255 (hex FF) - so we go directly to the composite hex license key, without needing to break things up into quads.

 

[Alanlives]

Atomic Shrimp,

Thank you! What a great suggestion. This is certainly an easier way to do things in Access rather than faffing around with the groups of four. I can't believe I didn't think of it myself!

The only trouble is that the eventual license string comes out backwards... I don't suppose I really need to explain why this is (or perhaps you can work it out), but it's to do with byte-ordering (see http://en.wikipedia.org/wiki/Endianness)

Anyway, although I agree that your suggestion is a better way to calculate a license string, Access will produce a string that is in reverse order from what the license key generation program is used to. I don't suppose there is an expression that I could use in Access (either at the concatenation stage or earlier) that would simply reverse the order of the resultant string, is there?!

As for validating the license key, thanks for this helpful thought but not to worry - the string is (heavily) encrypted before being issued to customers, and then decrypted at their end. In other words, they never actually see the license string so they couldn't enable features themselves.

Thanks again to Atomic Shrimp and to everyone else for any and all help you can offer - it's much appreciated.

PS. David R, thanks a lot for the code you suggested... I'm yet to try it but I'll let you know how that goes (to be honest I don't understand all the syntax you've used, but I'm learning fast and will try using the code ASAP!)

 

[Atomic Shrimp]

Anyway, although I agree that your suggestion is a better way to calculate a license string, Access will produce a string that is in reverse order from what the license key generation program is used to. I don't suppose there is an expression that I could use in Access (either at the concatenation stage or earlier) that would simply reverse the order of the resultant string, is there?!

Sure is - it's called StrReverse() - and will do exactly what you need here.
The function exists in Access 2000 upwards.

As for validating the license key, thanks for this helpful thought but not to worry - the string is (heavily) encrypted before being issued to customers, and then decrypted at their end. In other words, they never actually see the license string so they couldn't enable features themselves.

Cool - glad to see you thought of that already.

 

[Alanlives]

StrReverse() ... genius! Thank you very much for all your help.

As a final thought... what's the best way to "automate" the process from start to finish, as David R helpfully summarised it:

you can have the salesperson tickbox the options, then the database does some crunching and spits out the license code

As it stands, I have:

Table1: "SalesID" (pk), "SoftwareOptionName" and "Included?" (Yes/No) fields.
Query1: which calculates a decimal value based on whether or not a given software option is included:

DecimalCode: (Abs([Included])*2^([SalesID]))

Query2: this sums the "decimal code" into one value.
Query3: this converts the sum of the "decimal code" into a hex number, and reverses the string order (thanks to Atomic Shrimp!)

I bet there's an easier way to do this... but it seems that you need to have a new (sub)query for each new 'stage' of calculation. Please tell me I'm wrong!

 

[Atomic Shrimp]

I'm pretty sure you can replace all three of those queries with a single select query, or just a series of select queries, each one based on the previous one. (that way, they will run when you need them, as opposed to make-table queries, which must be run in preparation for something.

I don't know the structure of your table(s) - I'm assuming it will be something like:
tblcustomers:
CustID (autonumber PK)
Customer name, etc

tblSalesOptions
SalesOptionID (autonumber PK)
OptionName (text)
OptionValue (Integer)

tblCustomerSales
Customer (integer foreign key - lookup to tblCustomers)
SalesOption (integer foreign key - lookup to tblSalesOptions)
Selected (yes/no)

Then to calculate a list of licence keys for each customer, you'd have something like:

SELECT tblcustomers.CustName, Sum(tblSalesOptions.OptionValue) AS DecimalKey, Sum(StrReverse(Hex([OptionValue]))) AS HexKey
FROM (tblCustomerSales INNER JOIN tblcustomers ON tblCustomerSales.Customer = tblcustomers.CustID) INNER JOIN tblSalesOptions ON tblCustomerSales.SalesOption = tblSalesOptions.SalesOptionID
WHERE (((tblCustomerSales.Selected)=True))
GROUP BY tblcustomers.CustName;

-This assumes that tblsalesoptions contains a long list of SalesOption/customer combinations, some of which are checked, some not. A more efficient way to work it would be only to include those combinations of sales options and customers that are valid and dispense with the yes/no field altogether, but this might be more tricky to implement in the user interface.

 

[Alanlives]

Hi again,

I've been playing with the database trying to make it smaller, neater and simpler. In so doing I have discovered a little problem with my hex calculator...

If the resultant hex number ends in zero(s), Access is removing them from display when it runs the query (and calculates the hex number). I guessed that this has something to do with the hex "number" actually being displayed as text(?) (because of the letters A to F that can appear in it).

Since the resultant hex string should always be six characters long, (the letter "P" as an indicator followed by the five-digit hex string) I've tried using the the Format and Input Mask properties (I guess the latter is definitely wrong since the record value is being calculated from another query), but I just can't seem to display the zeros.

Am I attempting the impossible?

Thanks, Alan

 

[Alanlives]

Sorry, it's been a long day.

(Partially) ignore that last post... I got confused. If I were to calculate each number as five characters (with a "P" in front of them, like so)...

"P" & #####

... how would I go about it? If the hex number is less than five characters long, can I make Access display zeros to make the string five characters long?

Examples of desired outcome (hex string is also reversed using StrReverse):

Decimal input: Hexadecimal output:
127 ---------> F7000
8319 --------> F7020
78079 -------> FF031

 

[Alanlives]

Solved!

(Is there a way to mark this whole thread as solved, or is than an admin / moderator task?)

PS. For the benefit of those who are seeking a similar solution, I ended up with this:

 NewField: "P" & StrReverse(Right("00000" & Hex(Sum([TableOrQueryName].[FieldName])),5))