NEWBIE: How do I convert a query to text?

[DeltaWolf7]

Hi,

I have a database with a query that returns a list of printers. This is created by finding all the printers in the database that take a perticular cartridge by code. Sample below.

PrinterName
-----------------
Some Printer 1
Some Printer 2
Some Printer 3
ETC

What I want to do is take the results from the query and convert it into a single line of text and place it into a column of a table related to that cartridge code.

So if say the cartridge code '12345' returns a list of printers such as;

printer 1
printer 2
printer 3

I want to take this data and turn it into a line like this;

printer 1, printer 2, printer 3

I then want to take this new line and place it in a column called Desc in another table relative to the original cartridge code.

table should look like this after query.
BEFORE:
CartCode | CartDesc | Price | Desc
12345 | Something here | 10.00 |


AFTER:
CartCode | CartDesc | Price | Desc
12345 | Something here | 10.00 | printer 1, printer 2, printer 3

I hope what I have said makes sence. I am pretty new to access and feel I have done quite well so far. Please can someone help me solve this problem?

Thank you

 

[Fear Naught]

I don't understand why you would want to do this. In my view it goes against the principles of good database design.

You need two tables that are linked. Something like:

tblPrinters
PrinterId Autonumber PrimaryKey
PrinterName Text
CartridgeID Number (Long Integer) ForeignKey to tblCartridges

tblCartridges
CartridgeID Autonumber PrimaryKey
CartridgeDesc Text
CartridgePrice Currency

You can then easily report on what printers use what cartridges.

HTH

 

[DeltaWolf7]

I know it breaks every rule in the book but this database is only used for managing our printer/cartridges.

We actually use it to mass edit items and then export in to a tab deliminated file which then get import by our oscommerce website.

Please, I really need to do this to save myself hours of time.

 

[The_Doc_Man]

DeltaWolf7, you are thinking incorrectly. If you have a proper join, you can build reports that do what you want without violating two of the first three normalization forms at the same time. While I am a firm believer in doing the quick and dirty, I disagree with your time assessment. Having the right kind of queries SAVES time. Designing an app that builds denormalized strings TAKES EXTRA time.

 

[Fear Naught]

I know it breaks every rule in the book but this database is only used for managing our printer/cartridges.

We actually use it to mass edit items and then export in to a tab deliminated file which then get import by our oscommerce website.

Please, I really need to do this to save myself hours of time.

DeltaWolf7 If you follow the guidelines that I gave earlier in the thread and re-iterated by The_Doc_Man below you can manipulate your data any way you want. "You know it makes Sense "

To get the sort of output that you describe you can play around with Crosstab queries which will output in the way you are describing.