Data Entry in Crosstab Format

[Guest]

I want to design a table with associated queries & forms that will allow data
entry in the following form:

Name Project No Hours for Week Ending
5 May 06 12 May 06 19 May 06 etc
Dave 1234 10 5 10 etc
Dave 2345 5 5 5 etc
John 1234 20 15 20 etc

The “Nameâ€, “Project No†and “Hours for week ending†are column headings.
The “5 May 06â€, “12 May 06â€, “19 May 06â€, etc are the also sort of column
headings.

The above format is a perfect crosstab query output from the following table:

Name Project No Week Ending Hours
Dave 1234 5 May 06 10
Dave 2345 5 May 06 5
etc

The problem that I have is that ideally I would like the data entry to be in
the form of the crosstab output. Also the dates for the week ending will
continue into the future.

I have tried playing around with a couple of queries / forms to give the
impression of the data entry in the crosstab form, but with the data getting
posted back into data table.

Does anyone have any suggestions? Effectively if the crosstab query could
be editable / updatable then that should sort the issue?

 

[Jeff Boyce]

How your data is stored in a table does not necessarily constrain how it
will be displayed/formatted in a report. A design that uses "weeks" as
column headers/field names is not a well-normalized database table --
rather, it's what you'd need to do if you were using a spreadsheet.

I'd recommend you look into the topic of normalization before proceeding any
further. (or maybe, look into using a spreadsheet instead of a relational
db).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

How your data is stored in a table does not necessarily constrain how it
will be displayed/formatted in a report. A design that uses "weeks" as
column headers/field names is not a well-normalized database table --
rather, it's what you'd need to do if you were using a spreadsheet.

I'd recommend you look into the topic of normalization before proceeding any
further. (or maybe, look into using a spreadsheet instead of a relational
db).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

To clarify my question - I understand the normalisation principles - I am
expecting the data store table to be more of the form of the:

Name Project No Week Ending Hours
Dave 1234 5 May 06 10
Dave 2345 5 May 06 5
etc

table that I noted (which I hope satisfied normalisation principles). The
first table I included in my note was to try and indicate what I would like
the input to look like (ie this is probably going to be a form).

Also for other reasons I want to include this info in a database (some of
the fields will be linked / input from other tables in the database).

 

[Guest]

To clarify my question - I understand the normalisation principles - I am
expecting the data store table to be more of the form of the:

Name Project No Week Ending Hours
Dave 1234 5 May 06 10
Dave 2345 5 May 06 5
etc

table that I noted (which I hope satisfied normalisation principles). The
first table I included in my note was to try and indicate what I would like
the input to look like (ie this is probably going to be a form).

Also for other reasons I want to include this info in a database (some of
the fields will be linked / input from other tables in the database).

 

[Jeff Boyce]

I guess I'm just not understanding.

If you will be going to all the effort of entering data in a "crosstab"
format, why do you need to convert it to a different (normalized) format for
table entry, then re-display it in crosstab format?

I'll ask again... if you are constraining data entry to a "crosstab" format,
why not just use a spreadsheet? What would having the data in the table in
a normalized form allow you to do that a spreadsheet wouldn't?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

I guess I'm just not understanding.

If you will be going to all the effort of entering data in a "crosstab"
format, why do you need to convert it to a different (normalized) format for
table entry, then re-display it in crosstab format?

I'll ask again... if you are constraining data entry to a "crosstab" format,
why not just use a spreadsheet? What would having the data in the table in
a normalized form allow you to do that a spreadsheet wouldn't?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Sorry about this:
The reason I want all this in a database is that the person(s) and project
no(s) come from other database tables and I would like to use this as a means
for ensuring that the users of the system keep the project nos table data up
to date.
With regard to cross tab format etc - I dont care what format it is in, but
for each person they will record a multiple no of projects and for each of
these there will be different hours recorded against w/e dates. That is why
I suggested that the data could be recorded in a table of form:

Name Project No Week Ending Hours
Dave 1234 5 May 06 10
Dave 2345 5 May 06 5
etc

The thing is I don't really want to have people having to enter the data in
this form, but I cannot think of a way via queries or forms of having them
enter the data in a "nicer" format that is updatable (this is where the cross
tab format came up - I like how that is laid out, but you cannot enter data
in a crosstab query).

I hope that clarifies what I am trying to do and why.
Thanks for your patience and hopefully brilliant answer to how I can achieve
this.

Cheers

 

[Jeff Boyce]

?!Brilliant?! as in bright & shiny?! Hah!

One approach to getting the data entered you are describing may require
considerably less data entry than you might be thinking about.

If the persons who can have project time entered against them are listed (in
a table of their own), you can use a combo box on a form to let the data
entry person pick one, rather than have to enter the name each time.

If the projects against which time can be entered are listed (in a table
....), you can use a combo box on a form to let the data entry person pick
one, instead of typing it in.

If the "Week Ending" information can be listed on a table, you can use a
combo box on a form to let the data entry person pick one ... and you can
set the default value to last week's week-ending date so the data entry
person doesn't have to enter a date at all.

That leaves you with the # of hours (are you using an integer, forcing whole
number hours, or a "decimal" number?) to enter.

You could do this all on a form ...

Good luck!

Jeff Boyce
Microsoft Office/Access MVP