If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Strange query?
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 |
#2
|
|||
|
|||
Strange query?
Import the generic Concatenate function/module from
http://www.rogersaccesslibrary.com/O...p#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 wrote in message ... 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 |
#3
|
|||
|
|||
Strange query?
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! "Duane Hookom" DuaneAtNoSpanHookomDotNet wrote in message ... Import the generic Concatenate function/module from http://www.rogersaccesslibrary.com/O...p#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 wrote in message ... 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 |
#4
|
|||
|
|||
Strange query?
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 wrote in message news 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! "Duane Hookom" DuaneAtNoSpanHookomDotNet wrote in message ... Import the generic Concatenate function/module from http://www.rogersaccesslibrary.com/O...p#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 wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Syntax for nulls in query | MeredithS | Setting Up & Running Reports | 10 | April 2nd, 2006 02:42 AM |
creteria macros | sierralightfoot | Running & Setting Up Queries | 13 | March 8th, 2006 06:13 AM |
Crosstab Query Help | Becks | New Users | 17 | October 11th, 2005 08:31 PM |
Nested in-line Query | laura | Running & Setting Up Queries | 0 | February 11th, 2005 12:17 AM |
Union Query Not Returning A Value | Jeff G | Running & Setting Up Queries | 2 | October 19th, 2004 05:47 PM |