MS access 2007 about field names?

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

Answer Wiki

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

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Chippy088
    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
    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 ?
    69,065 pointsBadges:
    report
  • Voxli
    Thx.. Just wondering, will it turn out like this (did it with some simple editing) This is what i plan to do. sample
    15 pointsBadges:
    report
  • carlosdl
    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.
    69,065 pointsBadges:
    report
  • Chippy088
    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

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following