Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
earthling
Starting Member
3 Posts |
 Posted - 2007-11-10 : 11:38:19
|
| Hi everyone, I'm currently developing a Windows Forms application with Access as the database and I'm currently facing a problem.I have three tables:[CODE]=============================tblFiles-------FileID FilePathtblCategories-------------CategoryID CategoryNametblFileCategories----------------CategoryID FileID CategoryText=============================[/CODE]The idea of this application is like this: The user will be able to add the file paths of files into the database and assign multiple category values to each file (e.g. Name = <name>, Reference Number = <refno>, etc... )The names of each category will be stored in the [tblCategories] table while the value assigned to each category for the file will be stored in the [tblFileCategories] table, this creating a database as follows:[CODE]=============================tblFiles-------FileID FilePath1 C:\MedicalReport.pdftblCategories-------------CategoryID CategoryName1 Patient Name2 Patient NumbertblFileCategories----------------CategoryID FileID CategoryText1 1 John AppleSeed2 1 1234567890=============================[/CODE]But now, I want to do a select query of the records such that the data returned would be in the following format:[CODE]==================================FilePath | Patient Name | Patient Number |==================================[/CODE]Do any experts here have any advice as to how I would go about doing this? |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-10 : 12:18:38
|
| Would make much more sense to have the name/number in two columns of the same table, rather than multiple rows..But you can query like this using derived tablesSelect a.FilePath,cat1.CategoryText as PatientName ,cat2.CategoryText as PatientNumberFROM tblFiles a inner join (Select FileID,CategoryID,categorytext from tblFileCategories where categoryID = 1) cat1 on a.FileID = cat1.FileID inner join (Select FileID,CategoryID,categorytext from tblFileCategories where categoryID = 2) cat2 on a.FileID = cat2.FileIDTo get the results... |
 |
|
|
earthling
Starting Member
3 Posts |
Posted - 2007-11-10 : 12:28:02
|
| Thanks for your quick reply!One important thing I forgot to mention is that the Categories table has a interface for the user to add/remove/rename categories and thus, I had to move the value of the categories out of the [tblFiles] table and place it in the [tblFileCategories] table instead.Given that I cannot assume how many categories there are at any one time, how would I go about accomplishing my aim?Thanks! |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-10 : 13:26:03
|
| Not sure you can do that in a query, but you can specify that kind of result in a Report in Access. I set up a access db and tried a number of things, but there is no "easy" way to account for the potential increase in variables, or to simplify making the CategoryName the Column Heading. |
|
|
|
earthling
Starting Member
3 Posts |
Posted - 2007-11-10 : 14:52:51
|
| Oh dear... that could be a tad problematic.Well, I guess I'll just have to manually combine my datasets in VB.NET to form the end result that I want, but it would involve more For Next loops and calls to the database than I would like. |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-10 : 15:50:50
|
| Seems that is the only way. If your front end is vb.net it won't be as hard as structuring the query in Access.Even if you were to do it in Access, it would have to be a VB function to determine how many rows to spin into columns. It may not need to be a for next loop, you could just do a For x = 1 to Max(CategoryID) NEXT loop and iterate through as needed. May not be as bad as you think. |
|
|
|
|
|
|
|
|
Returning Rows as Columns