First-Last(or something like that) With a twist... (1 Viewer)

A

AggieLJ

Registered User.

Local time

Today, 15:29

Joined

Jan 9, 2009

Messages

30

• Apr 1, 2009

• #1

SURELY the old saying "If there is a will, there is a way" will still hold true for this one!

I have a database that keeps track of lab records.
If I have a set of ten labs on a particular invoice, such as these:

.Lab Number.|.TestWanted.|.Description..|.Price.
.....1......|.....S1.....|.Routine......|.10.00
.....2......|.....S1.....|.Routine......|.10.00
.....3......|.....S1.....|.Routine......|.10.00
.....3......|.....S1.....|.Routine......|.10.00
.....5......|.....S1.....|.Routine......|.10.00
.....6......|.....S2.....|.Routine.+.A..|.15.00
.....7......|.....S2.....|.Routine.+.A..|.15.00
.....8......|.....S2.....|.Routine.+.A..|.15.00
.....9......|.....S2.....|.Routine.+.A..|.15.00
.....10.....|.....S1.....|.Routine......|.10.00

How would I be able to format the output of a query to look like:

__Quantity__|_TestWanted_|_Description__|_Price_|_LabNumbers
_____6______|_____S1_____|_Routine______|_10.00_|_1-5,10____
_____4______|_____S2_____|_Routine_+_A__|_15.00.|_6-9_______

(sorry from the change in [space] designation, I didn't want the output to look like POINT 1 or POINT 6...)

Right now I can easily get everything but the LabNumbers part of the output...

Thanks in advance!

 

Uncle Gizmo

Nifty Access Guy

Staff member

Local time

Today, 21:29

Joined

Jul 9, 2003

Messages

16,271

• Apr 1, 2009

• #2

You may be able to adapt the code in this example here....

And another possibility look at crosstab queries, you may be able to employ one of those.

 

A

AggieLJ

Registered User.

Local time

Today, 15:29

Joined

Jan 9, 2009

Messages

30

• Apr 1, 2009

• #3

Thank you! I will give it a try and let you know how it goes!