Strange query?

[Guest]

Say I have a list of employee ID numbers and the employees work different
days. I have two tables, two columns each. One lists empID, dayNum; the
other lists dayNum, dayName.

1stTable
empID, dayNum
24, 1
24, 2
25, 3
25, 5
25, 7
29, 2
29, 3
29, 6

2ndTable
dayNum, dayName
1, Sunday
2, Monday
3, Tuesday
4, Wednesday
5, Thursday
6, Friday
7, Saturday

I need to create a query that will give me _two_ fields containing empID,
empDays to export to a spreadsheet:
24, "Sunday, Monday"
25, "Tuesday, Thursday, Sunday"
29, "Monday, Tuesday, Friday"

This is a simplified explanation of what I'm doing. These are two tables at
the end of a many-to-one/one-to-many link. I have an imperferct database
structure that I am stuck with. It is what it is, and this is what I have to
get out of it. I think it would be easier to make a report, but this has to
be provided as a spreadsheet.

If anyone can help pronto, it would be very greatly appreciated!!!

TIA

 

[Duane Hookom]

Import the generic Concatenate function/module from
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

Create a query like:

SELECT EmpID, Concatenate("SELECT Format(DateSerial(2005,5,dayNum),'dddd')
FROM [1stTable] WHERE empID =" & [empID]) AS EmpDays
FROM 1stTable
GROUP BY EmpID, Concatenate("SELECT Format(DateSerial(2005,5,dayNum),'dddd')
FROM [1stTable] WHERE empID =" & [empID]);

May of 2005 begins on a Sunday.

 

[Guest]

Thanks a lot for the quick response!

Because this is a web app using Coldfusion and an MS Access db, I ended up
just looping a second query in my result set, setting the MIME to Excel, and
dowloading the page. Worked for me.

I do appreciate the help!

Import the generic Concatenate function/module from
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

Create a query like:

SELECT EmpID, Concatenate("SELECT Format(DateSerial(2005,5,dayNum),'dddd')
FROM [1stTable] WHERE empID =" & [empID]) AS EmpDays
FROM 1stTable
GROUP BY EmpID, Concatenate("SELECT
Format(DateSerial(2005,5,dayNum),'dddd') FROM [1stTable] WHERE empID =" &
[empID]);

May of 2005 begins on a Sunday.
--
Duane Hookom
MS Access MVP

Say I have a list of employee ID numbers and the employees work different
days. I have two tables, two columns each. One lists empID, dayNum; the
other lists dayNum, dayName.

1stTable
empID, dayNum
24, 1
24, 2
25, 3
25, 5
25, 7
29, 2
29, 3
29, 6

2ndTable
dayNum, dayName
1, Sunday
2, Monday
3, Tuesday
4, Wednesday
5, Thursday
6, Friday
7, Saturday

I need to create a query that will give me _two_ fields containing empID,
empDays to export to a spreadsheet:
24, "Sunday, Monday"
25, "Tuesday, Thursday, Sunday"
29, "Monday, Tuesday, Friday"

This is a simplified explanation of what I'm doing. These are two tables
at the end of a many-to-one/one-to-many link. I have an imperferct
database structure that I am stuck with. It is what it is, and this is
what I have to get out of it. I think it would be easier to make a
report, but this has to be provided as a spreadsheet.

If anyone can help pronto, it would be very greatly appreciated!!!

TIA

 

[Duane Hookom]

I seemed to have missed the part in your first post where you mention
ColdFusion ;-). I have re-written the Concatenate() function for use with
classic ASP but not ColdFusion.

--
Duane Hookom
MS Access MVP

Thanks a lot for the quick response!

Because this is a web app using Coldfusion and an MS Access db, I ended up
just looping a second query in my result set, setting the MIME to Excel,
and dowloading the page. Worked for me.

I do appreciate the help!

Import the generic Concatenate function/module from
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

Create a query like:

SELECT EmpID, Concatenate("SELECT
Format(DateSerial(2005,5,dayNum),'dddd') FROM [1stTable] WHERE empID =" &
[empID]) AS EmpDays
FROM 1stTable
GROUP BY EmpID, Concatenate("SELECT
Format(DateSerial(2005,5,dayNum),'dddd') FROM [1stTable] WHERE empID =" &
[empID]);

May of 2005 begins on a Sunday.
--
Duane Hookom
MS Access MVP

Say I have a list of employee ID numbers and the employees work
different days. I have two tables, two columns each. One lists empID,
dayNum; the other lists dayNum, dayName.

1stTable
empID, dayNum
24, 1
24, 2
25, 3
25, 5
25, 7
29, 2
29, 3
29, 6

2ndTable
dayNum, dayName
1, Sunday
2, Monday
3, Tuesday
4, Wednesday
5, Thursday
6, Friday
7, Saturday

I need to create a query that will give me _two_ fields containing
empID, empDays to export to a spreadsheet:
24, "Sunday, Monday"
25, "Tuesday, Thursday, Sunday"
29, "Monday, Tuesday, Friday"

This is a simplified explanation of what I'm doing. These are two tables
at the end of a many-to-one/one-to-many link. I have an imperferct
database structure that I am stuck with. It is what it is, and this is
what I have to get out of it. I think it would be easier to make a
report, but this has to be provided as a spreadsheet.

If anyone can help pronto, it would be very greatly appreciated!!!

TIA