how to make separate lines for checkboxes

[jennypretty]

Hello,

I am using ASP to create a checkbox for 5 values and users can check multiple checkboxes at the same time. I store these values on ms access db table.

If a user check 2 checkbox (e.g. Mexico, Spanish), then the values is stored in the field will be "Mexico, Spanish", separate by "," or comma.

How do I create a query or report that display these values in different line?
Example:
Mexico
Spanish

Or is there any other way to do this?

Thanks for reading.
Jenny.

 

[boblarson]

If a user check 2 checkbox (e.g. Mexico, Spanish), then the values is stored in the field will be "Mexico, Spanish", separate by "," or comma.

ACK! NO!

How do I create a query or report that display these values in different line?
Example:
Mexico
Spanish

Redesign your database so it stores data CORRECTLY! You currently have a non-normalized structure and it will kill you trying to do band-aid fixes to get what you want out of it.

Do a search on normalization and data integrity.

 

[jennypretty]

Thanks.
I think i should create separate tables and join them together.

 

[boblarson]

Not sure of your entities, but this is a simplified example:

Table1
EntityID - Autonumber PK
EntityDescription - Text

Table2
CountryID - AutoNumber PK
CountryDescription - Text

Table3 (Junction)
JunctionID - Autonumber PK
EntityID - Long Integer FK
CountryID - Long Integer FK

 

[jennypretty]

Hello,

I have a long table and don't know which way is the best way to break it down to small tables and join them together.
Each student name can have more than 1 Race
ID Name Race1 Race2 Race3
-- ---- ----- ----- -----
1 a 2
2 b 3 1
3 c 1 2

For Race:
1 = asian
2 = mexico
3 = russian

Please help.

Thanks.

 

[boblarson]

Student
StudentID - Autonumber PK
StudentFName - Text
StudentLName- Text
And more info about the student in more fields

Table2
RaceID - AutoNumber PK
RaceDescription - Text

Table3 (Junction)
JunctionID - Autonumber PK
StudentID - Long Integer FK
RaceID - Long Integer FK

Join StudentID (Table1) to StudentID (Table3) and RaceID (Table 2) to Race ID (Table 3).

 

[jennypretty]

What is the main table?
If I use ASP script to insert data to the main table, then the sub-tables will be inserted automatically, am I right?

In other words, if I insert data to the main table, then all sub- related tables will be inserted automactically?

thanks

 

[boblarson]

No, inserts do not occur automatically. You need to add the information to those tables. Normally this would suggest the use of a main form to add the student data and a subform to add the race (which is the many of a one-to-many).

Also, just so you know, you need to remove those links from your signature as they are not allowed and can be classified as spam.

 

[jennypretty]

Thanks.
I think I should go for one big table and use report and query to manipulate the data.
thanks.

 

[boblarson]

That's up to you, but I'll warn you - (so you can't say you haven't been warned) that if you ever need to aggregate that race information, you are going to be hating the pain that it will cause trying to get that all together if it isn't set up as I've outlined. If you go with a non-normalized format then be aware that it is not easy to pull data in a meaningful fashion from such a format.

 

[jennypretty]

Hi Bob,
ID Name Race1 Race2 Race3
-- ---- ----- ----- -----
1 a Asian russian

How do I create a query to be like this (either in query or report)?
I like to combine three fields (race1, race2, race3) in one field (Race), and break any value of races to the 2nd line, 3rd line, if there is a value...
ID Name Race
-- --- ----
1 a Asian
Russian


Thanks.

 

[boblarson]

That's the part I meant about it being a pain-in-the butt if you use repeating fields. Sorry, I really don't want to tackle that one today. I have enough pain in my life today.

 

[jennypretty]

Sorry to bother you. I'll wait for you tomorrow then.
In Oracle and SQL Server, we can combine a few rows to be columns-like, but in Access, is there any way to do that?

thanks again.
Jenny.

 

[rainman89]

That's the part I meant about it being a pain-in-the butt if you use repeating fields. Sorry, I really don't want to tackle that one today. I have enough pain in my life today.

Lazy

 

[jennypretty]

Lazy, can you help?
thanks.