Multiple DISTINCT select/combine

[soljb]

Hi,

Here's something that i've been working on for a while, and that i might know how to do in ASP, but in Access it's stumping me.

I'm trying to link up a part number prefix and part code database. they are heirarchical, so there are many Part Num Prefixes for each code, etc

example:
Table Name: All_parts (1.6 million records)

Part_Code | Part_Prefix | Part_Base
1 | BUH | 133
1 | BUH | 155
1 | BX | 317
2 | QRT | 185
2 | PFT | 546
2 | PFT | 985
3 | FRH | 346
3 | ETG | 354


now, what im trying to do is for each DISTINCT part code, go through and grab all DISTINCT prefixes within that.

the problem is that it is a double distinct combine.
i need the distinct codes, and within that set, i need the distinct prefixes. I need to then concatenate each of the prefixes (separated by commas) into a memo field in a different table.

essentially how to get from the above example to:

Table Name: Code_Prefix_Join (~150 records)

Part_Code | Part_Prefix_Join
1 | BUH,BX
2 | QRT,PFT
3 | FRH,ETG


the reason this needs to be done dynamically and can't just be given to a data-entry guy to figure out once is that the part codes and prefix relationships change often, and the part_prefix_join field needs to be used in a later search.

i've thought about running a distinct sql query on just the part codes, dumping them into an array and running through them looking for distinct prefixes. there are hundreds of part codes though, and i think that would just be massively inefficient. besides, i dunno how to go about that in access.

thanks for the help guys, you're awesome. if you have any questions or need any clarification please let me know.

thanks!

 

[oharab]

Copy this code into a module.
Create a new query (a create table query if you have to) and turn group mode on, then select the field Part_Code and put PrefixList: GetPartBase([part_code]) as the second field.
Now when you run the query you should have a nice concatenated list.

Here's the code. You will need to change the SQL statement to match you particular database and if you are using A2k you will need to make sure that you have set a reference to DAO3.6.

HTH

Ben

Function GetPartPrefix(PartCode As Integer) As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim aList As Variant

GetPartPrefix = ""

Set db = CurrentDb

Set rst = db.OpenRecordset("SELECT Part_Code, Part_Prefix FROM tblTest GROUP BY Part_Code, Part_Prefix HAVING (((Part_Code)=" & PartCode & &quot);&quot

If rst.BOF And rst.EOF Then
Exit Function
Else
Do Until rst.EOF
GetPartPrefix = GetPartPrefix & rst![part_prefix] & ","
rst.MoveNext
Loop
GetPartPrefix = Left(GetPartPrefix, Len(GetPartPrefix) - 1)
End If
rst.Close
Set rst = Nothing
End Function
----------------------------------
Ben O'Hara
Home: bpo@RobotParade.co.uk
Work: bo104@westyorkshire.pnn.police.uk
Web:

http://www.RobotParade.co.uk

----------------------------------

 

[soljb]

Followups:

Im using Access2k, so how to i set a reference to DAO3.6 ?

how do i turn on group mode?


Unrelated:
is there an easy way to trim a column of data in access? i know it's pretty simple in excel, but im new to access so i cant figure it out.

 

[oharab]

Open a module and go to tools->References. Scroll down the list intil you find Microsoft DAO3.6 object library and put a tick next to it. If you can't find it, or can only find 3.5 then click the browse button and find the file Dao360.dll, usually in program files>common files>shared>dao and select that.

When building your query:
Add the table All_Parts to your view. Add the fields Part_Code and Part_Prefix. In the menu bar at the top there should be a button that looks like Sigma (a zig-zag E). Press that and a new option will appear called total and "Group By" will appear in it. Now go to where you added Part_Prefix and change it so that it is
PrefixList: GetPartBase([part_code])
and run your query. You should now get the results you are after.

To trim a column of data, have a look for the trim function in the help files.

HTH

B ----------------------------------
Ben O'Hara
Home: bpo@RobotParade.co.uk
Work: bo104@westyorkshire.pnn.police.uk
Web:

http://www.RobotParade.co.uk

----------------------------------