 |
28-Jul-2006, 08:10 AM
|  | Guest | | | | | | | | | | Data Entry in Crosstab Format
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 Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/10198-data-entry-in-crosstab-format.html
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 Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10198
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? Got anything to share on This Topic? Why not share your immediate thoughts/reaction with us! Login Now! or Sign Up Today! to share your views... Gurfateh! |
28-Jul-2006, 08:10 AM
| | Guest | | | | | | | | | | Re: Data Entry in Crosstab Format
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
"pom15595" wrote in message
news:0BB05981-7802-467E-91BF-5F41D84B763B@microsoft.com...
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10198
>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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10198
> 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?
 |
28-Jul-2006, 08:11 AM
| | Guest | | | | | | | | | | Re: Data Entry in Crosstab Format
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" wrote:
> 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 --
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10198
> 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
>
> "pom15595" wrote in message
> news:0BB05981-7802-467E-91BF-5F41D84B763B@microsoft.com...
> >I want to design a table with associated queries & forms that will allow
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10198
> >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?
>
>
> |
28-Jul-2006, 08:11 AM
| | Guest | | | | | | | | | | Re: Data Entry in Crosstab Format
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
"pom15595" wrote in message
news:77C5669F-F129-497B-9116-ADD801A65ECB@microsoft.com...
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10198
> the fields will be linked / input from other tables in the database).
>
> "Jeff Boyce" wrote:
>
>> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10198
>> any
>> further. (or maybe, look into using a spreadsheet instead of a
>> relational
>> db).
>>
>> Regards
>>
>> Jeff Boyce
>> Microsoft Office/Access MVP
>>
>> "pom15595" wrote in message
>> news:0BB05981-7802-467E-91BF-5F41D84B763B@microsoft.com...
>> >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?
>>
>>
>>
|
28-Jul-2006, 08:13 AM
| | Guest | | | | | | | | | | Re: Data Entry in Crosstab Format
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 Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10198
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" wrote:
> 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
>
> "pom15595" wrote in message
> news:77C5669F-F129-497B-9116-ADD801A65ECB@microsoft.com...
> > 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" wrote:
> >
> >> 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
> >>
> >> "pom15595" wrote in message
> >> news:0BB05981-7802-467E-91BF-5F41D84B763B@microsoft.com...
> >> >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?
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10198
> >>
> >>
> >>
>
>
> |
28-Jul-2006, 08:13 AM
| | Guest | | | | | | | | | | Re: Data Entry in Crosstab Format
?!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
"pom15595" wrote in message
news 24A64D4-2DE0-4A3F-BB63-30781E0403A1@microsoft.com...
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10198
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10198
> 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
>
| 
Support Us! Become a Promoter! | | Gurfateh ji, you can become a SPN Promoter by Donating as little as $10 each month. With limited resources & high operational costs, your donations make it possible for us to deliver a quality website and spread the teachings of the Sri Guru Granth Sahib Ji, to serve & uplift humanity. Every contribution counts. Donate Generously. Gurfateh! |
 (View-All)
Members who have read this thread : 0
| | There are no names to display. |
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests) | | | | Tools | Search | | | | | Display Modes |  Linear Mode |
Posting Rules
| You may not post new threads You may not post replies You may not post attachments You may not edit your posts
HTML code is On
| | | |  | » Active Discussions | | | | | | | | | | | | | | | | | | | | | | | Panjabi Yesterday 17:56 PM 12 Replies, 264 Views | | | | | | | | » Books You Should Read... | | | |
