MS Access experts. A little help?

Factual Questions
1

Let me preface this by saying that i am not very experienced at all with Access. I have made only a couple of very basic databases, and am nowhere near experienced enough to take advantage of the program’s full potential. Also, i tried to find the answer to my question in Access Help, but i couldn’t find anything that fit the bill.

So, i have a small database, and some of the inputs involves people’s names. In each individual record, there is at least one person involved, and sometimes as many as four people. So, i have four columns:

Person 1
Person 2
Person 3
Person 4

arranged across the top of the table, and arranged sequentially in my input form.

If an individual record has only one person associated with it, that person’s name goes in the “Person 1” column. If it has two people, i use “Person 1” and “Person 2,” and so on, leaving the extra columns blank for that entry.

Also, when i’m inputting the names, i do it alphabetically for each entry. So, if the record contains John and Steve, it will look like this:



Person 1          Person 2          Person 3          Person 4

John              Steve

but if the record contains Alice, Bruce, John, and Violet, it will look like this:



Person 1          Person 2          Person 3          Person 4

Alice             Bruce             John              Violet

As you can see, most individuals, especially those whose names start with letters that come later in the alphabet, could end up in any one of the four columns, depending on whether they are alone in that entry, or whether they are with other people whose names come earlier in the alphabet. In the above example, John is “Person 1” in the first entry, but he is “Person 3” in the second entry.

My next step was to try to find all records in which John (for example) appears. This was relatively easy. I opened a query in design view, and in the criteria i put “John” under “Person 1,” and then, using the “or” function, put “John” as the criteria in the other three columns. This brought up all records where John was a factor.

Now this is where i need help. I could make a separate query for each individual, but there are a lot of them. What i want to know is whether it is possible to design a macro (something i’ve never done before) that would do this name search for whatever name i plugged in. Essentially, i want to be able to type “Steve” into a box, and have Access bring up all records containing the name Steve in one of the four “Person” columns.

For all i know, this might be extremely easy, and i’m just demonstrating my ignorance of Access. Or it might be completely impossible, and i’m just demonstrating my ignorance of Access. Either way, my ignorance is not in doubt.

Any assistance would be most welcome.

Disclaimer: All names have been changed to protect the innocent. Any resemblance to any person, living or dead, is purely coincidental.

2

The simplist way to proceed along the track you’re taking is to replace the string “John” or “Steve” etc with [who] wherever it appears in your query, this will bring up a prompt box saying who and allowing you to type a name. Replace who with a more descriptive label if you feel like it.

In the long term however your database is designed incorrectly, what happens if you now need to add a fifth person for example, you have to go back through everything and make a whole bunch of changes. What I would suggest in this case is using a seperate table with a joint key of the key of the original table and the name of the person working on the project (or better yet, an ID number, in case multiple people have the same name) and using foreign keys to do the work for you.

I realise learning database design isn’t entirely trivial and apologise if the previous paragraph made little sense but some work now might save you a whole bunch of headaches later, not to mention it’s an extremely marketable skill.

3

counsel wolf, you are awesome. That’s exactly what i was looking for.

Thanks for your other advice also. I was aware of some of the inherent problems associated with the design of my database, but was willing to live with them because i didn’t have time to learn more, and because the database functions adquately for my rather limited needs.

While i think i understand the general principles behind your suggestions, i don’t yet know enough to put them into action. I think i might get an Access book or two out from my university library and do some systematic reading and learning. I find third-party books often explain computer programs better than the manufacturer’s instruction manual.

I’ll try to remedy my lack of knowledge about database design, but for the moment you’ve definitely solved my short-term problem. Cheers.

4

Your conceptual structure of the table is not correct. Remember that Access is a relational database and allows you to build multiple tables that are related and linked to each other. This is a much more efficient way, considering data normalization and other issues, to do what you’re trying to do.

Were I building the database, here’s what I would do:

Two (at least) tables, structured this way.

Group Table
fieldname and type
GroupIDNumber Integer Primary Key
Description Text
other fields as necessary
Person Table
fieldname and type
GroupIDNumber Integer Primary Key
PersonIDNumber Integer Primary Key
Last Name Text (Indexed seperately for an alphabetical view of the list)
First Name Text
other fields as necessary

Once you have the two tables built, go to the Menu item ‘Tools’ then ‘Relationships’. Link the two tables on ‘GroupIDNumber’ and click on the box that says “Enforce Referential Integrity.”

Now you can build forms and queries that do exactly what you want without having to hunt for names through any one of four fields. It’s faster, easier, more efficient…though you’ll have to do a little bit of hunting through the ‘Help’ on database design.

Hope this helps.

5

Just a quick note here that restricting yourself to Access is in my opinion a bad idea. Might I suggest getting a book about SQL and database design in general rather than Access in particular? If you need a free training tool, mySQL is a free database you can practise on. Their website is here and digging around I find they link to a free book about SQL here. I’m at a loss to recommend a book on the subject of database design, perhaps somebody else has an idea?
Once you know how databases work in general, return to Access and learn its ins and outs. In the opinion of both myself and many other database workers it’s not a great program by any stretch.

6

I’ll urge you to follow the opposite of counsel wolf’s suggestion.

mySQL is great database. I use it a lot. But Access is great for learning on. You’re definitely going to appreciate it as a learning tool when you get beyond the “use Access as a replacement for Excel” stage.

Right now you don’t know SQL. You don’t need to at this point – the visual query builders in Access will build the queries for you. You can then see the SQL and learn how it works. At some point, you’ll have to build an SQL only query. So, you’ll get SQL knowledge with experience.

Access is also great in that it keeps relational databases in a single, easy to move file. I’d love to get into FileMaker, for example, since it actually runs on my preferred Mac platform. But it’s not elegant (and lacks VB).

Stick with Access until you know enough to move on to something that’s text only. Then I do recommend mySQL – it runs everywhere.

7

You have some good points so if it’s not too far from the OP I’ll defend my suggestion.

I have three main reasons for suggesting a generic SQL approach over using Access to learn.

Firstly the mere fact that it’s generic means that the skills are more easily transferrable to other products - mySQL, Oracle, SQL server and so forth. Learning the logic that lies beneath Access’s graphical front end will stand you in good stead in many other places. This issue is the most important if you work as a computer professional. Solely concentrating on Access may lead to a kind of ‘cargo cult programming’ where you’re doing things without really understanding the basic logic behind how they hold together. This can lead to stretching methods beyond their breaking point instead of using another appropriate method that a more general and systematic approach may reveal to you.

Secondly even if you learn the interface and how to program VB really well, you still don’t know anything about good database design. The mistake made in the OP is a classic example of the kind of thing that a basic intro to normalisation and the foreign key/relationship idea would prevent. Nothing in the Access interface is going to help here. It is my experience that books on other databases spend more time on this subject. In addition being closer to the ground as in mySQL forces you to think about and understand these issues.

Thirdly, Access has many flaws as database products go, it’s really a hybrid database and front end developer. Some of the things I would consider essential are missing as a result of trying to develop a product that everyone can use. Triggers being what I miss most, with proper transactional handling and concurrency not far behind. (these may be better in modern versions, I’ve not used Access day-to-day for a while)

That said, my perspective is that of someone who uses, designs and adminsters databases as part of my daily grind, so I may be overcomplicating the issue.

8

Thanks for all the advice, folks.

If i understand you all correctly, SQL seems to be a sort of language that databases use (or that makes up a database) to store and relate data.

If that’s true, then i understand the logic of starting with SQL in order to learn the way the language actually works, rather than using a GUI from the beginning. In fact, when i decided a while back that i wanted to learn how to make webpages, i started by learning HTML rather than by going straight for a GUI program like Dreamweaver or Frontpage.

Once i made the switch to Dreamweaver, if the program gave me a result that i didn’t like, i often knew enough HTML to be able to go in and fix the problem by changing the code itself, rather than by trying to use the Dreamweaver controls. Is this the sort of benefit you’re suggesting i will get by learning SQL.

To tell you the truth, right now i really don’t need a much greater knowledge of database construction, because i use the things very rarely. I’m studying for a Ph.D. in American history, and my goal is to be a university professor of history, so it’s unlikely that databases will form an integral part of my career.

Still, i think it’s good to know this stuff, and i like tinkering with new things, so i think i might get a book on SQL from the university library and have a bit of a look. At the same time, i’ll keep plugging away in Access, trying to improve my knowledge and my abilities.

Thanks again for the help, everyone.

9

counsel wolf’s advice is correct for a database designer. Mhendo is clearly not one. So I would, for the time being, disregard what the wolf is saying.

You want poor Mhendo coding SQL in pure text? He doesn’t even know what foreign key is! That’s like saying the best way to learn html coding is to use notepad.

Mhendo, the parameter approcah on your query is exactly what I would have recommended. How great is that feature? But to further along your knowledge, try this:

Open your query in design view. Now drop down the “view” button on your toolbar. Pick “SQL” view. This will show you the text of the SQL statement. All queries in Access are single SQL statements, easily viewed (or written) using SQL view. See all that text in there? That’s what the wolf wants you to have to type in manually.

Stick with Access until you understand relationships and SQL. You’ll get the added bonus of exposure to VB.

Here’s another tip for you:

Add a new field to your query (select a blank column on the right) and type in:
People: Person1 & Person2 & Person3 & Person4

(assuming your person fields are named Person1, Person2, etc…)

Now in the criteria line under the “People” field put:

Like John

Neato. To add commas between names, use:

People: Person1 & (", " + Person2) & (", " + Person3) & (", " + Person4)

instead.