If count of one field is greater than two, then add sequential letter to another field

[spujr]

Firstly, apologies if this is vague or if someone answered a similar question elsewhere. I'm having trouble describing what I want which also makes it hard to search for answers!



I want to run an update query that will basically count the values of a given type in Field1, and based on that count, add a sequential letter to Field2.

Example, the table "Old_McDonald" has data like so:

Farm	Animal	Animal_Subrow
illinois	chicken
illinois	sheep
illinois	sheep
iowa	chicken
iowa	goat
indiana	goat
indiana	goat

After I run the update query I want it to look like this:

Farm	Animal	Animal_Subrow
illinois	chicken
illinois	sheep	a
illinois	sheep	b
iowa	chicken
iowa	goat
indiana	goat	a
indiana	goat	b

If there was a 3rd indiana goat in the table it would get "c" under animal_subrow, and so on. So basically if Count of Farm x Animal =< 1 then = blank, if Count of Farm x Animal = 2 then "a" for the first and "b" for the second... etc.

Sorry for the dumb analogy. I can get a Crosstab query to work that has Farm as the row heading, Animal as Column Heading, and then Count of Animal as value:

TRANSFORM Count(Tbl_OldMcDonald.Animal) AS CountOfAnimal
SELECT [Tbl_OldMcDonald]![Farm]
FROM Tbl_OldMcDonald
GROUP BY [Tbl_OldMcDonald]![Animal] & [Tbl_OldMcDonald]![Farm]
PIVOT Tbl_OldMcDonald.Animal;



...and I was thinking of referencing this count value to a simple table like:

ID	Letter
1
2	a
3	b
4	c
etc

But my brain stops there.

Help?
Thanks,
Will

PS, the count will never go beyond 26 (or past the letter z in my table).

[ssanfu]

I can't come up with a way using queries, but with VBA it will take 3 recordsets, 3 DO-UNTIL loops and a FOR-NEXT loop.

[Micron]

Another table or query probably isn't required if you add the count to Chr(95) as long as
- the count is between 1 and 27 and
- is an integer and
- you want lower case letters (in which case the logic is the same but the Chr value would be different.
So 95+2 = "a"

This looks like a pseudo example, which often turns out to be not so good when solutions are proposed. I'm waiting to see how this pans out before offering anything further since my esteemed peer has gone to the trouble of creating a db.

[orange]

spujr,

Is this a learning exercise? Just curious.

Steve--nice job.

[spujr]

Hi all, thanks for your feedback!

I ended up using Steve's vba (double thanks!) to make it work.

Yes, it was pseudo data but I didn't want to explain the real scenario as it might raise unrelated questions (like, "why did you do it that way dummy!?" ). Plus it allowed me to explain the need as simply as possible. There's reason behind the madness.

I did run into a small problem because my real data field "Animal" was not a text but a number. However after I identified the problem I reformatted the table field to a text and no problem.

@orange - well, I did learn something, so in that sense it is a learning exercise. However, I am trying to develop a form that will hopefully make my life easier in data entry where this was needed.

-Will

[Micron]

I'm curious to know if there's any requirement around which record of a set of animals gets the a versus the b? In other words, which Illinois sheep gets the 'a' and which gets the 'b' or does it matter? Your query output example doesn't include any ID or anything that defines the record. They are the same, if not for the letter suffix.

[orange]

Micron,
I agree. It would be nice to know the business and requirement, but it seems Will doesn't want to give "the sheep secret" away.
Sounds like a made up assignment to get new Access/database user/developer to
-learn the benefits of using Google
-join and seek advice from an online forum
-ask questions without divulging "intimate secrets of sheep in Illinois"

Steve solved it and the OP is happy. --->Success!

@Will,

Here is a link to lots of info re Database Planning and Design.

[spujr]

Hi,

Yes, I realized this morning the potential of the mix up of which gets the 'a' and which gets the 'b' and was going to clarify that there would be a additional "ID" key field (every increasing) that would determine the order. However, Steve already answered and it works as it is.

@Orange, what you say is fair and understandable. Though, honestly it is not a big secret and I thought my description using farm animals was the best method to state my question. Though if you really want to know (and maybe it can help another google searcher):

I am creating an app for my cages that have a set number of rows. However, each row can be sub-divided into multiple subrows. Me, being the user, will be adding stuff to the rows (using a form and append queries), but the number of subrows will vary (sometimes none, sometimes many). To account for this I created a form with a Datasheet subform that will auto hide null subrows as you go through each cage. In order to make the datasheet subform to work properly I had to create a crosstab query which will take info from the table that is continuously being appended by the user. Yes, maybe the way I just described is not the best way, but it is working.

And yes, I am still a novice, especially when it comes to vba. Yes, I do use Google as much as possible to try to find other's people solution as much as possible without bothering people. I hope my question here (more so, Steve's answer) can be useful for others.

Thank you for the link and for allowing me on the site.

Cheers,
Will

[Micron]

@orange, that is shear nonsense
FWIW, I decided to try my own hack at this because I'm curious about the chr() thing. That's why I wondered about which was the correct field.

[ssanfu]

Glad it worked...

<snip>I did run into a small problem because my real data field "Animal" was not a text but a number. However after I identified the problem I reformatted the table field to a text and no problem.

You could have modified modified the code. Since the "Animal" data was text, the code was designed for that; text values requires delimiters, numbers do not.
A lot easier to change a very small piece of the code, rather than change the table design.

One line needed to be changed for "Animal" to be a numeric field:

 sSQL = sSQL & " WHERE Farm = '" & f("Farm") & "' AND Animal = '" & a("Animal") & "'"   'Animal as a TEXT field
 sSQL = sSQL & " WHERE Farm = '" & f("Farm") & "' AND Animal =  " & a("Animal")         'Animal as an NUMBER field

AND Animal = ' " & a("Animal") & "'" the single quote, the ampersand and the double quote/singlel quote/double quote were deleted....

[orange]

Will,

Here is a link that may be an interesting read for you.

All about specimens/cages and then some.

Good luck.

[spujr]

Thanks again Steve,

I actually did try that but couldn't make it work and gave up. However, after seeing you do it correctly I tried again and ran into the same problem. It took me a while but what I figured out the problem was I had some bad data in the my table where the "Animal" field contained some Null records (which were in there due to me not entering the data originally correctly). Anyways, the minor oversight taught me a lot more about vba coding!

Much appreciative.
Will

[ssanfu]

Ack!!! So many details....

I had some bad data in the my table where the "Animal" field contained some Null records

So you could
1) add an Error handling routine or
2) you could check to see if "Animal"has a value before opening recordset "r". Or

3) you could modify (easiest) the SQL for the recordset "a" to: (<<--what I would do.)

sSQL = "SELECT DISTINCT tblOld_McDonald.Animal"
sSQL = sSQL & " FROM tblOld_McDonald"
sSQL = sSQL & "WHERE (((tblOld_McDonald.Animal) Is Not Null))
sSQL = sSQL & " ORDER BY tblOld_McDonald.Animal;
Set a = d.OpenRecordset(sSQL)

[Micron]

@ssanfu: Say, any chance you were so quick on the draw because you started out with a 'battery management' db?

Thought I'd share another approach. Interestingly, it just happens to deal with nulls but maybe not in the desired way. I think it also writes the letter to the correct ID, but the data set is quite limited, so not sure.

Explained:
a totals query returns the farm and animal where the count of farm is >=2 and <=26
in a select query, this is inner joined to the table on animal = animal and farm = farm; thus nulls won't be returned
code has 1 rs, 1 loop

there are few notes in the code; should be fairly evident what's going on but if not, happy to explain
Not saying this is better or more suitable, just different. Hopefully something to be learned (if nothing else, it contains error handling)

Old_McDonald1.zip

[ssanfu]

@ssanfu: Say, any chance you were so quick on the draw because you started out with a 'battery management' db?

Hey! You stay out of my backside ...uhhhh.... back stage!
When I need a dB, I frequently grab a saved forum dB from a while ago, make a copy, delete the objects and add the new things. I keep forgetting to delete the dB info. But the dB was empty ... so there!!