Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old October 31st, 2007, 06:50 AM
Registered User
 
Join Date: Oct 2007
Location: , , .
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default concatenate records

I've looked a lot of places and thought about a lot of different things but can't find a way of doing what i want to do. i'm now hoping one of you cleverer than me can help me out.

what i have is a two tables, one that has aircraft restraints and one that has aircraft seats. but generally many seats will use the same design of restraint so in my report i want to list the seats and then give the details of the restraint. so it needs to be "seat1, seat2, seat3, ... " then below that the restraint that those seats use.

i can't find out if there is a way to list the seats like that, i can get them vertical very easily, lol.

any ideas? all ideas welcome!

dave
  #2 (permalink)  
Old October 31st, 2007, 02:26 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

What is the current data structure? (Tables, columns, relationships?)


mmcdonal

Look it up at: http://wrox.books24x7.com
  #3 (permalink)  
Old November 1st, 2007, 04:13 AM
Registered User
 
Join Date: Oct 2007
Location: , , .
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

well, just looking at it has got my brain in a twist - but the important bit i think is two tables,
"tblDrawingNos" with the fields
"drawing no" (text)
"divan" (yes \ no)

and
"tblRestraints" with the fields
"divan" (yes \ no)
"part number" (text)

it's linked on the "divan" field, so all the records in "tblDrawingNos" that have a no for divan use one restraint, and all the yeses use another restraint.

does that make any sense? and in my report i want to list them in a line like so:

"number1, number2, number3
use such and such a restraint"

possible?

  #4 (permalink)  
Old November 1st, 2007, 07:44 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Can you do this: use the actual field name. For example, do you want:

"drawing no, drawing no, drawing no...

or

"part number, part number, part number...

when you say number 1 number 2 etc?

Also, what is the "restraint" field? I see "divan," "drawing no," and "part number," but no restraint field.

What I think you should do is to create a table with your two columns, like this:

tblReport
mConcatenate - memo
tRestraint - text

Then create a delete query to remove all items from the table before you run each report.

Then create the code to concatenate the fields in the memo column, and add the restraint column.

Then run the report.

You can take parameters and then pass them to the SQL string that opens the recordset to build the data.

Will that work?


mmcdonal

Look it up at: http://wrox.books24x7.com
  #5 (permalink)  
Old November 1st, 2007, 08:04 AM
Registered User
 
Join Date: Oct 2007
Location: , , .
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

sorry, that last bit wasn't very clear was it -

it is supposed to be "drawing no, drawing no, drawing no" use restraint "part number".

but, that aside - your method sounds like it will work and i will set about trying to make it happen!

thank you very much for your response, i'll write another reply when i've had a play around with it.

  #6 (permalink)  
Old November 1st, 2007, 10:13 AM
Registered User
 
Join Date: Oct 2007
Location: , , .
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

thank you very much mmcdonal!

got it working perfectly.

dave



Similar Threads
Thread Thread Starter Forum Replies Last Post
Concatenate multiple records into one Raman Pahwa SQL Server ASP 1 August 19th, 2008 02:57 PM
Argh! Why does this concatenate? interrupt Javascript How-To 1 August 13th, 2005 08:44 AM
URGENT : Concatenate multiple records from table cool_rk SQL Language 2 October 7th, 2004 11:04 PM
Concatenate multiple records from table to single Ron Howerton SQL Language 6 August 23rd, 2004 08:49 AM
Concatenate numbers fs22 Javascript 1 April 25th, 2004 06:51 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.