Query a list, then show only unique values

[bigalpha]

One Manifest can have many line items. One line item can only have 1 designation. Each manifest may have many line item with same designations.

For example:
Manifest #0001
Line Item 1: N
Line Item 2: H
Line Item 3: U
Line Item 4: N
Line Item 5: P
Line Item 6: H

Table set-up
CurrentCY
CurrentCYIDPK
WasteCategoryIDFK
LineItemInformation [Line Item 1, Line Item 2, etc]
ManifestDataIDFK

ManifestData
ManifestDataIDPK
ManifestNumber [0001]

WasteCategory
WasteCategoryIDPK
WasteCategory [H, N, U, etc]

I'd like to query the line items for each manifest so the end result has the manifest number (Manifest #0001) in a field and the designations (N, H, P, U) in fields on a report.

Not sure where to even start on this! Thanks for the help!

 

[Cronk]

If you want a query that just shows the different WasteCategories for each manifest, then create a query with tables ManifestData and CurrencyCY linked on ManifestDataIDFK fields, select the Totals button and select and group on ManifestNumber and WasteCategory.

If you want the count of each Category, include the field CurrentCYIDPK and select the grouping as a Count.

 

[bigalpha]

If you want a query that just shows the different WasteCategories for each manifest, then create a query with tables ManifestData and CurrencyCY linked on ManifestDataIDFK fields, select the Totals button and select and group on ManifestNumber and WasteCategory.

If you want the count of each Category, include the field CurrentCYIDPK and select the grouping as a Count.

The problem is that now I have many lines in the query for one manifest; I don't know how to combine all of the WasteCategory designations (H, N, U, etc) into one field based on the Manifest Number.

So I get this:
0001 H
0001 N
0001 P

But I don't know how to concatenate the H, N and P.

 

[Cronk]

Write a query that contains a VBA function to generate the concatenated string.

eg Select ManifestNumber, fnConCatDesignations(ManifestDataIDPK) from ManifestData;

The function would open a recordset of all records for that Manifest and loop through to generate the string of designations.

If you don't know how to write the VBA, do some research (there will be plenty of examples floating around) and post a question on the Modules section.

 

[bigalpha]

So the only real way to do this is with VBA in a module?

 

[spikepl]

That is not certain.

For example:
[I]Manifest #0001[/I]
Line Item 1: N
Line Item 2: H
Line Item 3: U
Line Item 4: N
Line Item 5: P
Line Item 6: H

[B]Table set-up[/B]
[U]CurrentCY[/U]
CurrentCYIDPK
WasteCategoryIDFK
LineItemInformation [Line Item 1, Line Item 2, etc]
ManifestDataIDFK

[U]ManifestData[/U]
ManifestDataIDPK
ManifestNumber [0001]

[U]WasteCategory[/U]
WasteCategoryIDPK
WasteCategory [H, N, U, etc]

Based on the above data, show exactly what the desired output should look like.

You mentioned that the data is for a report - reports can do some things that anywhere else would require VBA.

 

[bigalpha]

I'm using the report to make labels for folders. There are a number of fields on the report, but the two I'm working on are the Manifest Number and WasteCategory fields, as below.

Manifest Number Waste Category Removal Date
0001 H, N 1/1/2013
0002 H, N, U 2/1/2013
0003 U, P 3/1/2013

edit:
I implemented this code from Allen Browne but only could get it to show the WasteCategoryIDFK:

Manifest 001 1, 1, 1, 4, 4, 6, 6
instead of
Manifest 001 H, N, U

I hope that makes sense.

 

[spikepl]

So that corrresponds to the data shown in #6? How?

 

[bigalpha]

Yes.

Because each manifest may have multiple line items, I would get multiple Waste Categories (one for each line item) when I only need it to show the unique Waste Categories for each manifest.

Manifest #0001 may have 5 line items, and each line item has a Waste Category, like this:

Manifest #0001
Line Item 1: N [The "N" is the waste category]
Line Item 2: H [The "H" is the waste category]
Line Item 3: U [The "U" is the waste category]
Line Item 4: N [The "N" is the waste category]
Line Item 5: P [The "P" is the waste category]

I have a field on my report that should show only the unique Waste Categories for each Manifest like this:

Manifest #0001 - H, N, P, U

Instead of

Manifest #0001 - H
Manifest #0001 - N
Manifest #0001 - P
Manifest #0001 - U

 

[bigalpha]

So I found THIS post that allows me to select only the distinct values using the Allen Browne concat module.

However, I'm still limited to the ID of the Waste Category (e.g. 1) instead of the category (e.g. H).

 

[spikepl]

OK - just for consistency, what you show in #8 does not correspond to the data in #6, judging by what you show of data in #9, despite your claim to the contrary. We have no ESP powers so do not be sloppy: we cannot guess what you mean.

The way I see it you have two option: continue with Da Master's (a.ka. Allenm Browne) code and make that work, or use a multicolumn report - check this link http://office.microsoft.com/en-us/access-help/multiple-column-reports-HA001156534.aspx .

I suspect a multi-column report will require much more fiddling, and since you already managed to to do the concatenation, thatn I'd suggest you just continue that way. When you have the ID's then the primitive/slow way is to use Dlookup, the proper way is to create a query with joins such that it yields the designators for the waste category. Play with it in the query designer.

 

[bigalpha]

I'm not sure what you mean about the data consistency. I copied and pasted the data in post 9 from post 6.

I tried to use the code from Allen Browne to see if I could make it output the WasteCategory (H, N, U) instead of the Waste Category PK (1, 3, 4). I could not. Can I use a query to replace the WasteCategoryPK with the Waste Category? I'd go from 1, 3, 4 to H, N, U?

I'm not sure the multi-column report is what I'm after either, unfortunately.

 

[spikepl]

When you have the ID's then the primitive/slow way is to use Dlookup, the proper way is to create a query with joins such that it yields the designators for the waste category. Play with it in the query designer.

What is not clear in the text above?

 

[bigalpha]

Okay, I figured it out. I created Query1 that gave me this:

Manifest 0001 - H
Manifest 0001 - N

I created Query 2 that uses Query 1 as a source. I then concatenated the WasteCategories from Query1 in Query2 based on ManifestDataIDPK between Query 1 and 2.

This gave me exactly what I needed. Sorry for being dense, but you've been an immense help with this problem!

Query SQL

SELECT DISTINCT del.Manifestnum, ConcatRelated("WasteCategory","del","del.manifestdataidpk = " & [tblmanifestdata].[manifestdataidpk],"wastecategory") AS Concate
FROM tblManifestData INNER JOIN del ON tblManifestData.ManifestDataIDPK = del.ManifestDataIDPK;

 

[spikepl]

 

[bigalpha]

lol. That makes me feel better about myself more than it probably should have!