MS access 2007 about field names?

Voxli

15 pts.

Tags:

Field names

Microsoft Access

I'm making a very Simple database. its has ID, last name. telephone no. Type of OS (Windows/Linux/Mac). MY QUESTION IS, If a person has all the 3 OS, and some has 2 OS, other has 1 OS, How do i make only 1 field name for the the 3 selection, and make a database, a table and query where 1 person has 2 OS and other has 1 or 2 and not choose only 1 OS? I have no problem making a table where 1 can only choose 1 OS(windows, Linux,Mac) , my problem is for other people who has 2-3 OS..

Software/Hardware used:
Windows

Asked: March 7, 2010 5:55 AM Last updated: March 11, 2010 1:17 AM

Answer Wiki

Last updated: March 7, 2010 4:57 PM GMT

Richard Siddaway8,220 pts.
History
Contributors
- Ordered by most recent
- Richard Siddaway8,220 pts.

Thanks. We'll let you know when a new response is added.

Create table1 with UID, last name and telephone
create a uniqur index on UID

Create table 2 with OSID and OSName. It will have rows that look like

OSID OSName
1 Windows
2 Linux
3 mac

create a unique index on OSID

Create table3 that has MID, UID and OSID
They have foreign keys point to UID and OSID

Discuss This Question: 5 Replies

Chippy088 Mar 8, 2010 9:36 PM GMT

Nice solution RichS, however if I understand it correctly the need is for 1 field which indicates 1,2 or 3 OSs present. My simple solution for this would be to only use 1 field, and assign each OS a value, and add them together and store the int in the field. e.g. Windows = value of 1 Linux = 2 MAC = 5 So if all 3 OSs are used then the field value will be 8. The reason these values are used is that if selecting combinations of the 3 OSs the sum will be unique and unambiguous. no 2 OS values added together will add up to the pointer value for the 3rd. (i.e. if 1, 2 or 3 were used then the int could be the 3rd OS,or the 1st and 2nd OSs.)

4,625 pointsBadges:

report
carlosdl Mar 8, 2010 10:18 PM GMT

Although Chippy088's approach would work (if that is what Voxli wanted), I would not recommend a design like that. It will most likely bring complications in the future. For example: How would you write a query to get all users with a Windows OS ? (what are the index usage implications of a query like that ?) What changes to queries/applications would be needed if a new OS is added to the OS catalog ?

84,495 pointsBadges:

report
Voxli Mar 9, 2010 1:20 PM GMT

Thx.. Just wondering, will it turn out like this (did it with some simple editing) This is what i plan to do.

15 pointsBadges:

report
carlosdl Mar 9, 2010 5:48 PM GMT

If you want to do things well, do not try to use just one table for this. A design like the one suggested by RichS is your best option.

84,495 pointsBadges:

report
Chippy088 Mar 11, 2010 1:17 AM GMT

I think that what is wanted is a unique field like i suggested, and also a text field which lists the OS's the user has. 2 fields and a simple solution. The linked table solution is valid, as each user has to be linked with this table, so yes, a 1 table solution is not really viable. as Carlosdl rightly points out. As an answer was not required, only a method to provide a solution, and not knowing how well Voxli knows how to query a DB, the answer is to subtract the sum of the OS's not required and it will give the answer required. Taking my example. if he wanted to find the total number of users with linux then he only need to subtract 6 from each field value. Anything not negative or zero gives the result wanted. The same is relevant for any 2 OS. Subtract the sum of the 2 OSs required from the cell value. I have to admit that it may be more complex than Voxli wants, but imho it is a valid solution. (Looking at the example provided by Voxli, it seems he probably hasn't though of a more complex query solution, in which case it would suffice.) Indexing is relevant, it is a complex query or a simple yes/no query, unless Voxli wants to search users by OS using an index then it will be a totally diferent approach.

4,625 pointsBadges:

report