Combine rows in table via query? 4

[taterease]

I've been messing with this for awhile and need to call on ya'll for help.

1. We are getting donations of items for an auction and each item description (ItemDescription) and monetary value (ItemValue) is logged into a table (tbl_history) along with other pertinent information.

2. Each item has an item number (a manually assigned number, not an auto-num) and also a Package Number (a manually assigned number, not an auto-num) because the auction will be by Package, not by Individual Items. Some Packages will have only 1 item, but some Packages may have numerous items.

I've exported all of the auction items into a second table, tbl_Auction_Items. Each row of that table equals one record in tbl_History. (I did the second table because tbl_History has a LOT of other information in it and the export to another table made it less klutzy to work with).

There are two things that I need assistance with, and I think a query may be the best way to go but I'm open to any suggestions.

1. I need to (in tbl_Auction_Items) fill in a PackageDescription field that would be comprised of all of the ItemDescriptions for items in the same Package Number.

For example, if there were three rows in tbl_Auction_Items with Package Number 18 with item descriptions of "2007 Cadillac" in one row, "1 Year free fuel" in row 2, and "1 year free carwash" in row three, I would like the PackageDescription field to contain all three of those text data: "2007 Cadillac", "1 Year free fuel", and "1 year free carwash".

Also in the PackageValue field I need to have the sum of all ItemValue in the package.

Sorry for the convoluted description. I spend little time in Access these days, and have been beating myself over the head trying to figure out the easiest way of saying "This Package contains these items and is worth this much" - only in a table, not a report.

Thanks for any ideas on this one!

 

[PHV]

Have a look here:
faq701-4233

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Darrylles]

Hi tater,

List your tablenames with associated fieldnames.


ATB

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[taterease]

Hi Darrylle!

tbl_Auction_Items

Record_ID (autonum)
PACKAGE_NUMBER
ITEM_DESCRIPTION
DONOR_HISTORY_RECORD
VALUE_OF_ITEM
AUCTION_TYPE


(The above table is the one that shows all of the donated items in one place).

tbl_History

Package_number
Item_Desscription
History_ID
Value_of_Item
Category

(Tbl_History is the table that holds the donation information as it comes in from the donor. Donor info (names, addresses, etc.) is held in a third table, tbl_constituents. But nothing from Tbl_constituents needs to be moved to tbl_Auction_Items.

So when I move the data from tbl_history (which has a LOT of other information in it not concerning auction donations) I get one line for each auction item in tbl_auction_items.

What I need is a way to bundle all records in tbl_auction_items that have the same Package_Number into one record (so that the item_description fields all join into one overall description and the item_value fields all sum up into a value for the package).

Messy.

Last year they just opened tbl_Auction_Items and manually edited it, choosing one row in each package, editing it, and then deleting the other rows in the package. I'm hoping for a more elegant solution <g>.

I've not had time to check out PHV's FAQ code, but hope to today.

 

[dhookom]

There is a sample database using the generic concatenate function posted by PH at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lespaul]

tater,

Did you look at the link PHV provided? Copy the code in the FAQ, follow the instructions and you'll get a comma separated list....

it's pretty easy and self explanatory....

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[taterease]

I think I'm getting closer!

I'm getting an error message "Type Mismatch" (see below).

But if it ain't workin' in practice, I think I have a better understanding of how it's supposed to work!




Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb

'LINE BELOW GENERATES ERROR, "DATA TYPE MISMATCH IN CRITERIA EXPRESSION"

Set rs = db.OpenRecordset(pstrSQL)


Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function

 

[PHV]

The error is in pstrSQL, so post the SQL code of the query calling the Concatenate function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[taterease]

PHV -

Yeah, we've left my knowledge of Access far, far behind at this point <g>.

Query:

SELECT PACKAGE_NUMBER, Concatenate("Select item_description FROM tbl_Auction_Items where Package_Number =" & [PACKAGE_NUMBER]) AS PACKAGE_DESCRIPTION
FROM tbl_Auction_Items;

I dunno that makes a whole lotta sense to me, seems like it's running in circles...but in my case, that would be nothing new <g>.

 

[PHV]

I guess that Package_Number isn't defined as numeric in tbl_Auction_Items, so you may try this:

SELECT DISTINCT PACKAGE_NUMBER
, Concatenate("SELECT item_description FROM tbl_Auction_Items WHERE Package_Number='" & PACKAGE_NUMBER & "'") AS PACKAGE_DESCRIPTION
FROM tbl_Auction_Items

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

It may be time to update the FAQ to include information on the difference between numeric, text, and date delimiters ;-)

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

Take it easy Duane, it's your (great) stuff.

 

[dhookom]

I just updated the FAQ to identify the differences between text and numeric fields.

I almost always use numeric fields in my primary/foreign keys.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[taterease]

That's it!!!!

Thanks gang - I still have to look at the module and query and figure out how it all works (I know a little about DataEase, but Access modules are beyond me <g>).

I really appreciate the help on this one.

 

[taterease]

I feel like Det. Columbo...just one more thing <g>.

When I run the query (which runs gangbusters, thank ya'll very much!) it truncates the package_description to 255 characters, although in the table it's defined as a memo.

Some of these auction items will have descriptions longer than 255. Is there a way to overcome that limitation in the query?

Thanks much gang!

 

[dhookom]

The concatenate function shouldn't truncate. Where are you noticing the truncation, in the query or in a form or report? If it is in the query, please provide your entire sql statement. Truncation will occur in any query that uses "GROUP BY" or "DISTINCT" or "UNION" ...

In a report or form control this can happen if the control has some value in the Format property. You will also see truncation when the control is in a group header or footer section.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

Just a note:
Truncation will occur in any query that uses "GROUP BY" or "DISTINCT" or "UNION [!]without the ALL keyword[/!]" ...

 

[taterease]

Hi Guys.

Yeah, I did a search for "truncate" before I posted back with the question, I don't wanna bug you guys too much. Here's the query code:

SELECT DISTINCT PACKAGE_NUMBER, Concatenate("SELECT item_description FROM tbl_Auction_Items WHERE Package_Number='" & PACKAGE_NUMBER & "'") AS PACKAGE_DESCRIPTION
FROM tbl_Auction_Items;

I don't see any of the deadly <g> keywords that you've mentioned above.

Here's an example of the truncating field text, taken from the query when run to screen:

Gift certificate for a 2- hour Franklin Athletic Club Birthday Party for up to 10 children., GE Wine Vault, Datrek Golf Bag, Limited edition, signed screenprint of the General Motors Building, 3 Gift certificates for a total value of $200., Gift certifica

I did check the records in the table to make sure they weren't truncated there, but they are fine.

But as you can see above, this really works very well except for this minor glitch.

 

[PHV]

I don't see any of the deadly <g> keywords that you've mentioned above
Really ?
Remove the DISTINCT predicate as clearly stated by dhookom 20 Jul 07 12:32

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[taterease]

DISTINCT...as in "I'm distinctly an idiot?"...

Geez-o-pete, I gotta stop playin' with this database for a few hours. I can't even read anymore...

Thanks for pointing it out - and sorry to have bugged ya'll for something I should've caught the first time around!