Changing the columns in a Query

  • Thread starter Thread starter hansjhamm
  • Start date Start date
H

hansjhamm

I have a table that has the following columns that I run in my query
ID-PK
StoreNumber
Date
ResumesSent
Currently, when I run the query and if a store number is listed 3 times
it will return the store number 3 times...easy enough. BUT, HR Dept. is
needing it a different way. They want the date column to reflect each
date in the fields, i.e. if a resume was sent on 9-5-06...that would be
a column. If one (or more than one) resume was sent on 9-6-06...that
would be a new column.
So, effectively the query would return something like this
ID StoreNumber (ResumesSent on)9-5-06 (ResumesSent on)9-6-06
1 111 3
4
2 222 7
0


I have never done this before and don't really know how to explain
it...


Thanks in Advance!

Hans
 
Try this --
TRANSFORM Count(hansjhamm.[ID-PK]) AS [CountOfID-PK]
SELECT hansjhamm.[ID-PK], hansjhamm.StoreNumber
FROM hansjhamm
GROUP BY hansjhamm.[ID-PK], hansjhamm.StoreNumber
PIVOT "Resume Sent on " & Format([DateResumesSent],"Short Date");
 
Karl,

Thanks for the "FAST" response. It worked very close, but not quite was
I am needing


Here is the way the table is laid out:


ID StoreNumber Date ResumesSent
1 8926 9-5-2006 4
2 8924 9-4-2006 2
3 8924 9-5-2006 9


How I need it returned is like this:
ID StoreNumber 9-4-2006
9-5-2006
Do I need this? 8924 2
9
"" 8926
4


Is this possible in SQL?

Thanks!


Hans

KARL said:
Try this --
TRANSFORM Count(hansjhamm.[ID-PK]) AS [CountOfID-PK]
SELECT hansjhamm.[ID-PK], hansjhamm.StoreNumber
FROM hansjhamm
GROUP BY hansjhamm.[ID-PK], hansjhamm.StoreNumber
PIVOT "Resume Sent on " & Format([DateResumesSent],"Short Date");


[email protected] said:
I have a table that has the following columns that I run in my query
ID-PK
StoreNumber
Date
ResumesSent
Currently, when I run the query and if a store number is listed 3 times
it will return the store number 3 times...easy enough. BUT, HR Dept. is
needing it a different way. They want the date column to reflect each
date in the fields, i.e. if a resume was sent on 9-5-06...that would be
a column. If one (or more than one) resume was sent on 9-6-06...that
would be a new column.
So, effectively the query would return something like this
ID StoreNumber (ResumesSent on)9-5-06 (ResumesSent on)9-6-06
1 111 3
4
2 222 7
0


I have never done this before and don't really know how to explain
it...


Thanks in Advance!

Hans
Click to expand...
Click to expand...
 
TRANSFORM Sum(hansjhamm.ResumesSent) AS SumOfResumesSent
SELECT hansjhamm.[ID-PK], hansjhamm.StoreNumber
FROM hansjhamm
GROUP BY hansjhamm.[ID-PK], hansjhamm.StoreNumber
PIVOT Format([Date],"Short Date");


[email protected] said:
Karl,

Thanks for the "FAST" response. It worked very close, but not quite was
I am needing


Here is the way the table is laid out:


ID StoreNumber Date ResumesSent
1 8926 9-5-2006 4
2 8924 9-4-2006 2
3 8924 9-5-2006 9


How I need it returned is like this:
ID StoreNumber 9-4-2006
9-5-2006
Do I need this? 8924 2
9
"" 8926
4


Is this possible in SQL?

Thanks!


Hans

KARL said:
Try this --
TRANSFORM Count(hansjhamm.[ID-PK]) AS [CountOfID-PK]
SELECT hansjhamm.[ID-PK], hansjhamm.StoreNumber
FROM hansjhamm
GROUP BY hansjhamm.[ID-PK], hansjhamm.StoreNumber
PIVOT "Resume Sent on " & Format([DateResumesSent],"Short Date");


[email protected] said:
I have a table that has the following columns that I run in my query
ID-PK
StoreNumber
Date
ResumesSent
Currently, when I run the query and if a store number is listed 3 times
it will return the store number 3 times...easy enough. BUT, HR Dept. is
needing it a different way. They want the date column to reflect each
date in the fields, i.e. if a resume was sent on 9-5-06...that would be
a column. If one (or more than one) resume was sent on 9-6-06...that
would be a new column.
So, effectively the query would return something like this
ID StoreNumber (ResumesSent on)9-5-06 (ResumesSent on)9-6-06
1 111 3
4
2 222 7
0


I have never done this before and don't really know how to explain
it...


Thanks in Advance!

Hans
Click to expand...
Click to expand...
Click to expand...
 
Karl,
I think you are just about there...If you can help me onthis last
point, I will be GOOD!
The last one woked really good, but what I also need is for each store
to be listed just once...Give you an idea of what it returns now, I
have the following:
ID StoreNumber 9-4-2006 9-5-2006
1 8926
1
2 8924 3
3 8924
4

If the formatting gets screwed up, it is listing, in this example, 8924
twice. Once on the 4th with 3 resumes sent and once on the 5th with 4
resumes sent.


Thanks again!

KARL said:
TRANSFORM Sum(hansjhamm.ResumesSent) AS SumOfResumesSent
SELECT hansjhamm.[ID-PK], hansjhamm.StoreNumber
FROM hansjhamm
GROUP BY hansjhamm.[ID-PK], hansjhamm.StoreNumber
PIVOT Format([Date],"Short Date");


[email protected] said:
Karl,

Thanks for the "FAST" response. It worked very close, but not quite was
I am needing


Here is the way the table is laid out:


ID StoreNumber Date ResumesSent
1 8926 9-5-2006 4
2 8924 9-4-2006 2
3 8924 9-5-2006 9


How I need it returned is like this:
ID StoreNumber 9-4-2006
9-5-2006
Do I need this? 8924 2
9
"" 8926
4


Is this possible in SQL?

Thanks!


Hans

KARL said:
Try this --
TRANSFORM Count(hansjhamm.[ID-PK]) AS [CountOfID-PK]
SELECT hansjhamm.[ID-PK], hansjhamm.StoreNumber
FROM hansjhamm
GROUP BY hansjhamm.[ID-PK], hansjhamm.StoreNumber
PIVOT "Resume Sent on " & Format([DateResumesSent],"Short Date");


:

I have a table that has the following columns that I run in my query
ID-PK
StoreNumber
Date
ResumesSent
Currently, when I run the query and if a store number is listed 3 times
it will return the store number 3 times...easy enough. BUT, HR Dept. is
needing it a different way. They want the date column to reflect each
date in the fields, i.e. if a resume was sent on 9-5-06...that would be
a column. If one (or more than one) resume was sent on 9-6-06...that
would be a new column.
So, effectively the query would return something like this
ID StoreNumber (ResumesSent on)9-5-06 (ResumesSent on)9-6-06
1 111 3
4
2 222 7
0


I have never done this before and don't really know how to explain
it...


Thanks in Advance!

Hans
Click to expand...
Click to expand...
Click to expand...
 
Drop the column for ID. Your stores have multiple ID entries.

[email protected] said:
Karl,
I think you are just about there...If you can help me onthis last
point, I will be GOOD!
The last one woked really good, but what I also need is for each store
to be listed just once...Give you an idea of what it returns now, I
have the following:
ID StoreNumber 9-4-2006 9-5-2006
1 8926
1
2 8924 3
3 8924
4

If the formatting gets screwed up, it is listing, in this example, 8924
twice. Once on the 4th with 3 resumes sent and once on the 5th with 4
resumes sent.


Thanks again!

KARL said:
TRANSFORM Sum(hansjhamm.ResumesSent) AS SumOfResumesSent
SELECT hansjhamm.[ID-PK], hansjhamm.StoreNumber
FROM hansjhamm
GROUP BY hansjhamm.[ID-PK], hansjhamm.StoreNumber
PIVOT Format([Date],"Short Date");


[email protected] said:
Karl,

Thanks for the "FAST" response. It worked very close, but not quite was
I am needing


Here is the way the table is laid out:


ID StoreNumber Date ResumesSent
1 8926 9-5-2006 4
2 8924 9-4-2006 2
3 8924 9-5-2006 9


How I need it returned is like this:
ID StoreNumber 9-4-2006
9-5-2006
Do I need this? 8924 2
9
"" 8926
4


Is this possible in SQL?

Thanks!


Hans

KARL DEWEY wrote:
Try this --
TRANSFORM Count(hansjhamm.[ID-PK]) AS [CountOfID-PK]
SELECT hansjhamm.[ID-PK], hansjhamm.StoreNumber
FROM hansjhamm
GROUP BY hansjhamm.[ID-PK], hansjhamm.StoreNumber
PIVOT "Resume Sent on " & Format([DateResumesSent],"Short Date");


:

I have a table that has the following columns that I run in my query
ID-PK
StoreNumber
Date
ResumesSent
Currently, when I run the query and if a store number is listed 3 times
it will return the store number 3 times...easy enough. BUT, HR Dept. is
needing it a different way. They want the date column to reflect each
date in the fields, i.e. if a resume was sent on 9-5-06...that would be
a column. If one (or more than one) resume was sent on 9-6-06...that
would be a new column.
So, effectively the query would return something like this
ID StoreNumber (ResumesSent on)9-5-06 (ResumesSent on)9-6-06
1 111 3
4
2 222 7
0


I have never done this before and don't really know how to explain
it...


Thanks in Advance!

Hans
Click to expand...
Click to expand...
Click to expand...
 
Karl,


PERFECT!! Thanks for your help!

Hans

KARL said:
Drop the column for ID. Your stores have multiple ID entries.

[email protected] said:
Karl,
I think you are just about there...If you can help me onthis last
point, I will be GOOD!
The last one woked really good, but what I also need is for each store
to be listed just once...Give you an idea of what it returns now, I
have the following:
ID StoreNumber 9-4-2006 9-5-2006
1 8926
1
2 8924 3
3 8924
4

If the formatting gets screwed up, it is listing, in this example, 8924
twice. Once on the 4th with 3 resumes sent and once on the 5th with 4
resumes sent.


Thanks again!

KARL said:
TRANSFORM Sum(hansjhamm.ResumesSent) AS SumOfResumesSent
SELECT hansjhamm.[ID-PK], hansjhamm.StoreNumber
FROM hansjhamm
GROUP BY hansjhamm.[ID-PK], hansjhamm.StoreNumber
PIVOT Format([Date],"Short Date");


:

Karl,

Thanks for the "FAST" response. It worked very close, but not quite was
I am needing


Here is the way the table is laid out:


ID StoreNumber Date ResumesSent
1 8926 9-5-2006 4
2 8924 9-4-2006 2
3 8924 9-5-2006 9


How I need it returned is like this:
ID StoreNumber 9-4-2006
9-5-2006
Do I need this? 8924 2
9
"" 8926
4


Is this possible in SQL?

Thanks!


Hans

KARL DEWEY wrote:
Try this --
TRANSFORM Count(hansjhamm.[ID-PK]) AS [CountOfID-PK]
SELECT hansjhamm.[ID-PK], hansjhamm.StoreNumber
FROM hansjhamm
GROUP BY hansjhamm.[ID-PK], hansjhamm.StoreNumber
PIVOT "Resume Sent on " & Format([DateResumesSent],"Short Date");


:

I have a table that has the following columns that I run in my query
ID-PK
StoreNumber
Date
ResumesSent
Currently, when I run the query and if a store number is listed 3 times
it will return the store number 3 times...easy enough. BUT, HR Dept. is
needing it a different way. They want the date column to reflect each
date in the fields, i.e. if a resume was sent on 9-5-06...that would be
a column. If one (or more than one) resume was sent on 9-6-06...that
would be a new column.
So, effectively the query would return something like this
ID StoreNumber (ResumesSent on)9-5-06 (ResumesSent on)9-6-06
1 111 3
4
2 222 7
0


I have never done this before and don't really know how to explain
it...


Thanks in Advance!

Hans
Click to expand...
Click to expand...
Click to expand...
 
Back
Top