Table design and easy UI ideas

[Seth Schrock]

I have a chart that is currently on paper, that I would like to store in a database. And I want it to be normalized, but I can't figure out how to get an easy to edit UI to go with the normalized tables. Here is the chart:

Immunization                            |   Date   |   Date   |   Date  |   Date  |   Date  |   Date 
Diphtheria Tetanus and/or Pertussis
Poliomyelitis
Measles
Mumps
Rubella
Hepatitis B

A crosstab query would display the information, but it isn't updateable. I would like to make the entry form very close to this as it is easy to just tab through the fields and enter the dates, but I can't think of a way to do so while having the data normalized. Does anyone know any tricks to make this possible?

[Rabbit]

What I've done before was to embed one form multiple times as subforms, with each instance linked to a different row.

[Seth Schrock]

So would I have to have a field for position number so that the subforms can pull the correct records?

[Rabbit]

Yes, I used a query or DCount() to number the rows

[Seth Schrock]

If I use a query or DCount() to number the rows, won't that make the query not updateable?

[Rabbit]

Surprisingly no, I was able to able to update the fields from the original table.

[Seth Schrock]

Strange. I used the DCount() method and the query says unupdateable, but it lets me update it.

Now here is another question. If I'm creating subforms for each column of dates, would it be better to not normalize this portion of the database since I have lost the benefit of not having to redesign the FE if I want a seventh column of dates? I guess if I didn't normalize it, it would be more difficult to count how many the immunization shots the person had had.

[Rabbit]

I would keep it normalized to make future queries easier