Beginner's query question (1 Viewer)

[Montemontgo]

I run a data base with famous people. Now, celebrities win all sorts of prizes. Therefore in a text field called "Prizes won" I put in strings like [without (#)]

(1) Grammy1998, Oscar2003
(2) Bambi1985, Goya1992, Grammy2001
(3) Oscar1991, Grammy1996, Grammy2003

Which query would yield all Grammy winners sorted by year of win i.e.
(3)
(1)
(2)

 

[Štef1]

Hello Montemontgo!
Es I can see from your question,
you wrote more data in one field.
you have to learn something about
DATABASE NORMALIZATION.

 

[ColinEssex]

You should be using 2 tables.

One for the persons demographic data with an AutoNumber as a unique identifier. Then link to the second table (using the original AutoNumber generated earlier) which contains the prizes, keep the prize and year in seperate fields. and have each prize won as a new record.

One celebrity can have many prizes.

Col

 

[Montemontgo]

Thanks Štef. You're right, I'm not quite a top shot at Database Normalization.

Thanks Colin. Of course, with my unprofessional way I have much less data stored. And, I have no difficulties in extracting all Grammy winners with a query that states in the prize field
[Como "*Grammy*"] (sorry, that's in spanish) The only thing missing is that they are not sorted by year. So I take it that with my system it will not be possible to have them sorted.
Of course I have quite an extensive table with about 500 different prizes too. If I have you right, you suggest to multiply these records so that each prize in each year has a separate record, making it about (for say 20 years) 10'000 prize records. Then I could assign each celebrity to a specific prize. (And the sorting problem would be solved - maybe?) This seems to me a lot of work to solve this tiny little problem.

By the way, my collection comprises nearly 100'000 people and if you let me know your birthday I shall present to you about 200 celebrities with the same date of birth. On my home page.

 

[ColinEssex]

Hmmm, I see that you may have a problem. Stef and I are correct with our answers though.

However, to overcome your problem you will need to identify where in the string the award is you need - then go to the next comma (,) and come back 4 characters to get the year and put the year in a seperate field in the query to sort on.

You can only sort on the year when its in a field on its own as it needs to be numeric - you have it as a string at the moment.

Col

ps - popped into Denia last year for a day, very nice. I liked the restaurants leading up to the Plaza at the top of the road!

 

[Montemontgo]

Thanks Colin, I'm already very much at ease since you accept that I have a small problem with these prizes - and even more at ease that it aint got an easy solution. Indeed, your last comment comes close to my own thinking and while my old human brain would have no problem in sorting the Grammy winners according to year of award, there seem to be no simple logical machine instructions to do the job. In my opinion the year doesn't have to be numeric, as text strings are equaly well sorted. In sleepless hours I shall continue contemplating this issue. For the moment many thanks.

Next time when you're down here at the Costa Blanca give us a call and we shall meet at the restaurant. www.telefonica.net/web/montemontgo

 

[Štef1]

Hello Montemontgo!
I made a short example how I should make it.
There are two tables, and two forms.
frmPerson is a main form, and frmPrizeSubform
is a subform. One person you enter only one time,
and all of his prizes in subform. You can see three
queryes, only for example.

 

[Montemontgo]

Hola Štef. (hola is spanish for hello)
Extremly nice educational work, I must say - thanks a lot Štef.
The one thing that's new for me is subforms - and I like that very much. However in this way I will end up with a prize table of about, let's see - 6 Nobel prizes since 1901, Oscars, Hollywood walk of fame stars, Olympic Gold medals in over 100 sports since 1896, Tennis grand slams, Canonizations of all the popes ... it goes on and on - say 1000 different prizes x 50 years in average + all these multiple award winners (ughh..), well let's say close to 100'000 prize records. (small ones though) Should I worry about that? My main person table today comprises about 86'000 records but only about 6000 persons have won prizes so far. Will my data base be slowed down with this new feature? Nevertheless, I like your solution so much that I shall try it out.

One small detail more: Would you agree, that the year of prize doesn't need to be numeric and doesn't need to have its own field. Since I never will have to do any calculation with these years I get all I want by just having one single prize text field with entries like
"Grammy 1967"
"Grammy 1968" (and so on)
All desired extracting and sorting will work fine. Yes? No?
Glad to hear from you.

 

[Štef1]

Hola Montemontgo!
1) Access97 is limited to 1 Gigabyte, in Access2000
it is 2 Gb. I suggest you to learn something about
"Compact", "Repair" and "Splitter" (Access Help).

2) The field "Year" my be Alpha field, it is up to you,
but it must be a separate field. If you put a prize
and year together you'll have a problem with
queries.
good luck!

 

[Montemontgo]

Hola Štef
You're right. You're right. You're right.
I've got Access2000. A first try with 100'000 prize records didn't slow down the scrawling of forms. It still advances 15 forms per second when I stay on the page down key. So I'm completely convinced of the normalization technique now and I'm going on well with the convertion work.
Thanks again.
John
www.telefonica.net/web/montemontgo

 

[Montemontgo]

Štef, are you still around? (or anybody for that matter)
Well, everything works fine with these subforms with prizes. I have now nice querries like: All Nobel prize winners in chemistry etc. The querry I still have not been able to accomplish is:
List the persons with multiple Olympic gold medals ordered to number of Medals. I think it's got someting to do with "Group" and "Count" but how exactly it goes I can't figure out.