Fields in Design View not visible in Datasheet view?

[WLHagen]

Hi all - and thanks in advance for all assistance.

I'm in the process of creating a database for a software users group for financial institutions (FIs). At the present time, I have 3 tables, one of which is giving me problems, and I'm not sure if I should "scrap it and start over" or if "this can be fixed"

My "Institutions" table has various information regarding the bank or credit union - Institution ID (the primary Key - a non-meaningful auto-number), Institution Name, whether they are a bank or CU, and "Yes/No" boxes for if they use the 5 different software programs that our users group encompasses. *(there are other fields, but these are the ones that are germane to my problem).

When I look at the table in Design view, I can see all of the fields. When I switch to Datasheet view, I cannot see the columns for the aforementioned 5 "Yes/No" checkbox columns for the 5 software programs. I checked to see if the fields were hidden, they were not. Tried scrolling over to see if they were to the right of the viewing pane, they were not. Tried adding a new record to see if I would get that information then - no dice.

Can this be fixed? or do I start over? Granted, I don't have a LOT of records (25) YET.......

Again, thanks in advance!

 

[HiTechCoach]

What version of Access?

If Access 2007/2010 are you using a Multi Value Field?

Your five checks box ore really a poor design. It violate the rules of data normalization.

You should use a child/related/sub table that has a separate record for each
item.

To properly fix your issue your first need to redesign your table by splitting into at least two tables.

See: Database Normalization Resources

 

[WLHagen]

Hi, sorry I didn't post that I have Access 2007 at this time.

I read your post about splitting the table, but I'm not sure how best to do that regarding the 5 different softwares that the UG supports. The 5 are not "mutually exclusive" - but the FI's can use any combination of the 5 softwares, so there are myriad combinations there. For example, my FI uses 3 of the 5, there are others that only use 1, or 2... or 4.... or all 5. That's why I thought that check boxes would be the best solution for tracking the software product(s) used. Would a Multi-Value field allow me to choose multiple values?

 

[HiTechCoach]

I would NOT use the new bug (some might consider it a feature) that allows a Mult -Value field. I would only using it is you must for comparability with Sharepoint.

The 5 are not "mutually exclusive" - but the FI's can use any combination of the 5 softwares, so there are myriad combinations there. For example, my FI uses 3 of the 5, there are others that only use 1, or 2... or 4.... or all 5. That's why I thought that check boxes would be the best solution for tracking the software product(s) used.

That sounds like how you would design a spreadsheet not a relational database.

If the database is proerply design and you need to add additional softwares then you shoudl not have to make any design cahgnes. The way you are proposing will require major changes to the datsbe just to add one additional software item. The method I am recommending would not require any design changes. uyou woudl only have to add data to a table.


By adding a record for each software the Institutions has you can have any combination you need. You simple add a records as needed.

Example:

Table Institutions
tblInstitutions
- InstitutionsID Autonumber - primary key
... other fields

table Institutions Software
tblInstitutionsSoftware
- InstitutionsSoftwareID - Primary Key autonumber
- InstitutionsID - this is a forgien key to link back ot eh table Institutions
- SoftwareID - his is a forgien key to link back ot eh table Software
- DateStarted - when they started usubng teh software
- Date Ended - when they stopped using eh software
- AddedAdded (default to Now() )

Relationship: tblInstitutions (1) -->> (many ∞)tblInstitutionsSoftware