| Computers Forum Index » Computer - Databases - MS Access » Columns to Rows... |
|
Page 1 of 1 |
|
| Author |
Message |
| Mohabi... |
 Posted: Wed Oct 21, 2009 3:43 pm |
|
|
|
Guest
|
Any suggestion on the following would be greatly appreciated:
I was working on a database in MS Access and I have encountered an
issue I need your assistance with. I have a table for which i would
like to convert the data from Column to Rows. Except for the Month
field in Date format all fields are in text format. Even though Pivot
is good representation of what I want but it does not work the way I
need it. I want to be able to modify the data in Pivot but it does not
allow me to. So far I have tried Cross tabs but this gives me blank
fields if I use GroupBy or missing data if I use First.
Here is an example of my table:
Type Name Month
A PersonA Jan-10
A PersonA May-10
A PersonA Jun-10
A PersonB Apr-10
A PersonC Apr-10
B PersonD Jan-10
B PersonD Feb-10
B PersonA Mar-10
B PersonA Apr-10
B PersonB May-10
B PersonB Jun-10
B PersonC May-10
B PersonC Jun-10
B PersonE Jan-10
C PersonF Mar-10
D PersonG Jan-10
D PersonG Feb-10
D PersonG Mar-10
D PersonG Apr-10
D PersonG May-10
D PersonG Jun-10
D PersonH Jan-10
D PersonH Feb-10
D PersonH Mar-10
D PersonH Apr-10
D PersonH May-10
D PersonH Jun-10
D PersonI Jan-10
D PersonI Feb-10
D PersonI Mar-10
D PersonI Apr-10
D PersonI May-10
D PersonI Jun-10
E Personj Jan-10
E Personj Feb-10
E Personj Mar-10
E Personj Apr-10
E Personj May-10
E Personj Jun-10
Want to convert into a table like this:
Type Jan-10 Feb-10 Mar-10 Apr-10 May-10 Jun-10
A PersonA PersonB PersonA PersonA
A PersonC
B PersonD PersonD PersonA PersonA PersonB PersonB
B PersonE PersonC PersonC
C PersonF
D PersonG PersonG PersonG PersonG PersonG PersonG
E PersonH PersonH PersonH PersonH PersonH PersonH
E Personi Personi Personi Personi Personi Personi
I tried Cross tab query - but this gives me the result for each name
as a separate row if I use Group by. Or even though there is a spot
available on the previous row for the same type for some reason it
puts the data into the 2nd line for which I later have to manually cut
paste the data into the upper row.
I cannot use pivot feature of Access 2003 because I cannot edit it.
Your suggestion would be highly appreciated.
Thank you |
|
|
| Back to top |
|
|
|
| paii, Ron... |
| Posted: Wed Oct 21, 2009 9:42 pm |
|
|
|
Guest
|
"Mohabi" <bmohabi at (no spam) gmail.com> wrote in message
news:9a890b22-0263-43c2-a6cd-d41eb0003685 at (no spam) 33g2000vbe.googlegroups.com...
Quote: Any suggestion on the following would be greatly appreciated:
I was working on a database in MS Access and I have encountered an
issue I need your assistance with. I have a table for which i would
like to convert the data from Column to Rows. Except for the Month
field in Date format all fields are in text format. Even though Pivot
is good representation of what I want but it does not work the way I
need it. I want to be able to modify the data in Pivot but it does not
allow me to. So far I have tried Cross tabs but this gives me blank
fields if I use GroupBy or missing data if I use First.
Here is an example of my table:
Type Name Month
A PersonA Jan-10
A PersonA May-10
A PersonA Jun-10
A PersonB Apr-10
A PersonC Apr-10
B PersonD Jan-10
B PersonD Feb-10
B PersonA Mar-10
B PersonA Apr-10
B PersonB May-10
B PersonB Jun-10
B PersonC May-10
B PersonC Jun-10
B PersonE Jan-10
C PersonF Mar-10
D PersonG Jan-10
D PersonG Feb-10
D PersonG Mar-10
D PersonG Apr-10
D PersonG May-10
D PersonG Jun-10
D PersonH Jan-10
D PersonH Feb-10
D PersonH Mar-10
D PersonH Apr-10
D PersonH May-10
D PersonH Jun-10
D PersonI Jan-10
D PersonI Feb-10
D PersonI Mar-10
D PersonI Apr-10
D PersonI May-10
D PersonI Jun-10
E Personj Jan-10
E Personj Feb-10
E Personj Mar-10
E Personj Apr-10
E Personj May-10
E Personj Jun-10
Want to convert into a table like this:
Type Jan-10 Feb-10 Mar-10 Apr-10 May-10 Jun-10
A PersonA PersonB PersonA PersonA
A PersonC
B PersonD PersonD PersonA PersonA PersonB PersonB
B PersonE PersonC PersonC
C PersonF
D PersonG PersonG PersonG PersonG PersonG PersonG
E PersonH PersonH PersonH PersonH PersonH PersonH
E Personi Personi Personi Personi Personi Personi
I tried Cross tab query - but this gives me the result for each name
as a separate row if I use Group by. Or even though there is a spot
available on the previous row for the same type for some reason it
puts the data into the 2nd line for which I later have to manually cut
paste the data into the upper row.
I cannot use pivot feature of Access 2003 because I cannot edit it.
Your suggestion would be highly appreciated.
Thank you
Making the assumption that your table is keyed such that you can never have
more the 1 person of a type on a given day. Use the cross tab query a source
to a 2nd query to group by type and append all the null strings with the one
record with a person on each day. Note you will want to add a Where clause
to the cross tab query to limit the number of days to keep the result under
255 columns. |
|
|
| Back to top |
|
|
|
| Mohabi... |
| Posted: Mon Nov 02, 2009 7:54 pm |
|
|
|
Guest
|
Thanks for your response. I am coming back after long time again. I
have no blanks and at any time there can be a maximum of 20 types. So
I am not worrying about exceeding 255. The blank issue is occurring
even in one type. So let say:
Record 1: Type A has PersonA from Jan to March and May to Jun in 5
rows. (Does not have April)
Record 2: Type A has PersonB in April only in 1 row
When I cross tabing the above it is giving me two rows(Person Name is
grouped). However I would like the above two rows in one record
because on Record on Apr has a blank spot whereas Record 2 has April.
So squeezing this into one record will look better for my report.
Because this is not happening the report I am running really does not
look good when I have like 200 records.
Any suggestion on a get around would be much appreciated.
On Oct 21, 12:42 pm, "paii, Ron" <n... at (no spam) no.com> wrote:
Quote: "Mohabi" <bmoh... at (no spam) gmail.com> wrote in message
news:9a890b22-0263-43c2-a6cd-d41eb0003685 at (no spam) 33g2000vbe.googlegroups.com...
Any suggestion on the following would be greatly appreciated:
I was working on a database in MS Access and I have encountered an
issue I need your assistance with. I have a table for which i would
like to convert the data fromColumntoRows. Except for the Month
field in Date format all fields are in text format. Even though Pivot
is good representation of what I want but it does not work the way I
need it. I want to be able to modify the data in Pivot but it does not
allow me to. So far I have tried Cross tabs but this gives me blank
fields if I use GroupBy or missing data if I use First.
Here is an example of my table:
Type Name Month
A PersonA Jan-10
A PersonA May-10
A PersonA Jun-10
A PersonB Apr-10
A PersonC Apr-10
B PersonD Jan-10
B PersonD Feb-10
B PersonA Mar-10
B PersonA Apr-10
B PersonB May-10
B PersonB Jun-10
B PersonC May-10
B PersonC Jun-10
B PersonE Jan-10
C PersonF Mar-10
D PersonG Jan-10
D PersonG Feb-10
D PersonG Mar-10
D PersonG Apr-10
D PersonG May-10
D PersonG Jun-10
D PersonH Jan-10
D PersonH Feb-10
D PersonH Mar-10
D PersonH Apr-10
D PersonH May-10
D PersonH Jun-10
D PersonI Jan-10
D PersonI Feb-10
D PersonI Mar-10
D PersonI Apr-10
D PersonI May-10
D PersonI Jun-10
E Personj Jan-10
E Personj Feb-10
E Personj Mar-10
E Personj Apr-10
E Personj May-10
E Personj Jun-10
Want to convert into a table like this:
Type Jan-10 Feb-10 Mar-10 Apr-10 May-10 Jun-10
A PersonA PersonB PersonA PersonA
A PersonC
B PersonD PersonD PersonA PersonA PersonB PersonB
B PersonE PersonC PersonC
C PersonF
D PersonG PersonG PersonG PersonG PersonG PersonG
E PersonH PersonH PersonH PersonH PersonH PersonH
E Personi Personi Personi Personi Personi Personi
I tried Cross tab query - but this gives me the result for each name
as a separate row if I use Group by. Or even though there is a spot
available on the previous row for the same type for some reason it
puts the data into the 2nd line for which I later have to manually cut
paste the data into the upper row.
I cannot use pivot feature of Access 2003 because I cannot edit it.
Your suggestion would be highly appreciated.
Thank you
Making the assumption that your table is keyed such that you can never have
more the 1 person of a type on a given day. Use the cross tab query a source
to a 2nd query to group by type and append all the null strings with the one
record with a person on each day. Note you will want to add a Where clause
to the cross tab query to limit the number of days to keep the result under
255 columns.- Hide quoted text -
- Show quoted text - |
|
|
| Back to top |
|
|
|
| paii, Ron... |
| Posted: Tue Nov 03, 2009 6:17 pm |
|
|
|
Guest
|
I think you need to group by Type not Person with dates as columns and
person in the rows
Type January February March April May June
A PersonA Person A PersonA PersonB PersonA PersonA
"Mohabi" <bmohabi at (no spam) gmail.com> wrote in message
news:ec8661c1-b6d7-468e-9289-1a3c610b6e8d at (no spam) 37g2000yqm.googlegroups.com...
Thanks for your response. I am coming back after long time again. I
have no blanks and at any time there can be a maximum of 20 types. So
I am not worrying about exceeding 255. The blank issue is occurring
even in one type. So let say:
Record 1: Type A has PersonA from Jan to March and May to Jun in 5
rows. (Does not have April)
Record 2: Type A has PersonB in April only in 1 row
When I cross tabing the above it is giving me two rows(Person Name is
grouped). However I would like the above two rows in one record
because on Record on Apr has a blank spot whereas Record 2 has April.
So squeezing this into one record will look better for my report.
Because this is not happening the report I am running really does not
look good when I have like 200 records.
Any suggestion on a get around would be much appreciated.
On Oct 21, 12:42 pm, "paii, Ron" <n... at (no spam) no.com> wrote:
Quote: "Mohabi" <bmoh... at (no spam) gmail.com> wrote in message
news:9a890b22-0263-43c2-a6cd-d41eb0003685 at (no spam) 33g2000vbe.googlegroups.com...
Any suggestion on the following would be greatly appreciated:
I was working on a database in MS Access and I have encountered an
issue I need your assistance with. I have a table for which i would
like to convert the data fromColumntoRows. Except for the Month
field in Date format all fields are in text format. Even though Pivot
is good representation of what I want but it does not work the way I
need it. I want to be able to modify the data in Pivot but it does not
allow me to. So far I have tried Cross tabs but this gives me blank
fields if I use GroupBy or missing data if I use First.
Here is an example of my table:
Type Name Month
A PersonA Jan-10
A PersonA May-10
A PersonA Jun-10
A PersonB Apr-10
A PersonC Apr-10
B PersonD Jan-10
B PersonD Feb-10
B PersonA Mar-10
B PersonA Apr-10
B PersonB May-10
B PersonB Jun-10
B PersonC May-10
B PersonC Jun-10
B PersonE Jan-10
C PersonF Mar-10
D PersonG Jan-10
D PersonG Feb-10
D PersonG Mar-10
D PersonG Apr-10
D PersonG May-10
D PersonG Jun-10
D PersonH Jan-10
D PersonH Feb-10
D PersonH Mar-10
D PersonH Apr-10
D PersonH May-10
D PersonH Jun-10
D PersonI Jan-10
D PersonI Feb-10
D PersonI Mar-10
D PersonI Apr-10
D PersonI May-10
D PersonI Jun-10
E Personj Jan-10
E Personj Feb-10
E Personj Mar-10
E Personj Apr-10
E Personj May-10
E Personj Jun-10
Want to convert into a table like this:
Type Jan-10 Feb-10 Mar-10 Apr-10 May-10 Jun-10
A PersonA PersonB PersonA PersonA
A PersonC
B PersonD PersonD PersonA PersonA PersonB PersonB
B PersonE PersonC PersonC
C PersonF
D PersonG PersonG PersonG PersonG PersonG PersonG
E PersonH PersonH PersonH PersonH PersonH PersonH
E Personi Personi Personi Personi Personi Personi
I tried Cross tab query - but this gives me the result for each name
as a separate row if I use Group by. Or even though there is a spot
available on the previous row for the same type for some reason it
puts the data into the 2nd line for which I later have to manually cut
paste the data into the upper row.
I cannot use pivot feature of Access 2003 because I cannot edit it.
Your suggestion would be highly appreciated.
Thank you
Making the assumption that your table is keyed such that you can never
have
more the 1 person of a type on a given day. Use the cross tab query a
source
to a 2nd query to group by type and append all the null strings with the
one
record with a person on each day. Note you will want to add a Where clause
to the cross tab query to limit the number of days to keep the result
under
255 columns.- Hide quoted text -
- Show quoted text - |
|
|
| Back to top |
|
|
|
|
