Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Data Entry in Crosstab Format

Discussion in 'Information Technology' started by pom15595, Jul 28, 2006.

  1. pom15595

    pom15595
    Expand Collapse
    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?
     
  2. Loading...


  3. Jeff Boyce

    Jeff Boyce
    Expand Collapse
    Guest

    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" <pom15595@discussions.microsoft.com> 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?
     
  4. pom15595

    pom15595
    Expand Collapse
    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" 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" <pom15595@discussions.microsoft.com> 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?

    >
    >
    >
     
  5. Jeff Boyce

    Jeff Boyce
    Expand Collapse
    Guest

    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" <pom15595@discussions.microsoft.com> 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" <pom15595@discussions.microsoft.com> 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?

    >>
    >>
    >>
     
  6. pom15595

    pom15595
    Expand Collapse
    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" 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" <pom15595@discussions.microsoft.com> 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" <pom15595@discussions.microsoft.com> 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?
    > >>
    > >>
    > >>

    >
    >
    >
     
  7. Jeff Boyce

    Jeff Boyce
    Expand Collapse
    Guest

    ?!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" <pom15595@discussions.microsoft.com> wrote in message
    news:D24A64D4-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
    > 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
    >
     

Share This Page