Alphabetic order in related fields

[georgp]

Not sure, if this is the right place to post...

I have a database with 5 fields and the form with 5 textbox controls: [topic1], [topic2], [topic3], [topic4], [topic5] as well as other text controls.

Two questions:
1. The user can enter 1-5 topics in whatever order in a new record and I want that they are ordered alphabetically, e.g.

user input:
[topic1]:window, [topic2]:bed, [topic3]B, [topic4]:home, [topic5]empty)

should be stored as
[topic1]:bed, [topic2]B, [topic3]:home, [topic4]:window, [topic5]empty)

2. If, however the user enters a combination (in unordered form) which is already available in the database (in ordered form), than the other text controls should pull up the information already related to this combination.

Have no idea where to start.... Any suggestions?

Thanks, gp

 

[JeremyNYC]

George,

You'll want to start with your table structure. You have five repeating fields, and repeating fields are generally seen as a no-no in database design.

Presumably there will be lots of sets of five, right? What you'll want to do is have one table that records the thing that holds those five things together: the user's name, or something like that.

Then make another table to hold those sets of five things. But here's the trick: it's only got two fields: the user name (or whatever that unifying field is) and a field to hold a topic. Now, the user can enter as many topics as he or she wants (more on contolling the number of topics later).

Open up the relationships window (check help files for stuff you don't know how to do, just so I don't have to type it all out) and make a relationship between the two tables, connecting the UserName field in both tables. You can do all the cascading stuff if you want, but just be sure to read up on them.

To display this, you'll have a form that shows the username (again, or whatever). On that form, you'll have a subform that shows the topics. This will be a list of all the topics. The recordsource for this subform will be something like: SELECT UserName, Topic FROM tblTopic. Once you put the subform on the main form, Access should automagically filter the topics so it only shows the ones appropriate for that Username.

There's a great article on the theories behind why you structure your tables like this, and I recommend it so often that I've got a copy of it on my website. Check out the Fundamentals of Relational Database Design link. It will be well worth your time.

Jeremy

PS: Actually, I lied. I'm not going to go into how to limit things to five topics. ONe o the great things about this mehthod is that you cn have six, nine, or as many topics as you want. One of the drawbacks is that you have to do a little work to limit things to some number of topics. If you really need to limit things to five, post back. First I'll try to talk you out of it, and then I'll show you how to do it<G>.

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[georgp]

Jeremy,

thanks a lot for your extensive reply. Actually, I am aware of your excellent webpage (it is great that you guys share some of your knowledge with us) including the article on normalization (you may have seen that I have done several downloads previously including this article) and as a matter of fact I have started with the approach you recommend some months ago(and use it in some related areas, see below).
However, finally I ran into a lot of problems in retrieving the data (I guess that is one of the major problems - extensive normalization may result in complex retrieval), so I wanted to try a 'less normalized' way. To give you an idea: I am talking about chemical substances, which are made and characterized in several ways, resulting in minor differences, and which are made from other substances, which have suppliers, different lots, prices, purities, etc. These substances are then used in various formulations, which are tested in at least 30 different aspects, supplied to customers, tested there, with reports, etc. All this is somewhere connected and different people have different focus and different questions about the data.
In such a complex world minor data entry errors may confuse conclusions and a major point of my interest is to avoid any input error, which results in myriads of look up tables, etc. (that is where my question comes from).
So I agree, it is quite possible that my table or even my project structure is inadequate and I understand that your approach would make it easy to arrange the data as I am asking for.
So your answer is kind of confirmation that I have either to give up some of the above requests or delve into more complexity due to increased relational db design.
Anyway - to answer your question: there will be never more than 5 'topics' and never less than two.
If you have an idea to share, I would appreciate it.
And thanks for your interest.
Regards, georgp

 

[JeremyNYC]

Georgp,

OK, well it sounds like you have a decent bit of the theory down. Now it's a matter of whether or not you want to do all the work that will come with normalization. It _is_ more work to make a fully normalized database, and to enter and retrieve information. But one of the big payoffs is accuracy in data. Lookup tables might be more work, but they insulate you from spelling errors and typos and abbreviations and multiple entries and...well, you get the point. My thought is that a database for a purpose such as you describe, will only be worth the trouble if it is fairly well normalized; one less normalized might end up doing more harm than good. This, of course, is a decision you are in a far better place than I to make. But from out here, that's my thought.

If you're going to be doing a lot of data collection, it's probably worth your time to learn all the things that will be required of you to get this database right. If not, I would say the options are to use spreadsheets or index cards or some other filing system, or to get a database built for you.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[georgp]

Jeremy,

thanks again for your comments. I fully agree that normalization is the preferred solution.
I will review the db design in a more simple model. May be, I overlooked something when I started. Sometimes I fail to see the individual tree because the forest is too big.

I actually came from spreadsheets and was fascinated by the idea to have all data normalized in one relational system. That resulted in an Access/Excel combination working reasonably well, but meanwhile is somewhat slow due to data transfer.

So I try to get everything work in Access, which may be an unreasonable approach, but I also believe that Access offers a more 'professional' (and controlled?) user interface than Excel.

Regards, georgp