Join fields query

[Pink_Panther]

Hi Guys,

I'm new to the forum and new to Access. I tried doing a search but couldnt find the i needed. If someone could help me out that would be great. I'm sure what im wanting to do is easy so heres hoping haha.

Okay pretty much my data looks like this

Category_name Sku
cars 1
Trucks 1
Bikes 1
Phones 2
Tablets 2
Hats 3

and what i need to is something that will link all sku 1 category names in a new field with a | divider and then all the sku 2 category names together with | as a divider and so on. the amount of categories is different for every sku. help with this would be great! and would save me 10's of hours.

so it looks like this

Joined_categories sku
Cars|Trucks|Bikes 1
Phones|Tablets 2
Hats 3


And if this is easy enough, its not that important though but to delete duplicate category names when it transfers them across to a new field joined together. this isnt important though would just be nice.

Thanks

 

[Mihail]

Welcome to the forum.
Upload your database because I understand almost... nothing from your explanations .

!!!!!!!!! ZIP before uploading.

 

[Brianwarnock]

I think that you need VBA for this as you would need to read the table sorted on SKU building the output until change of SKU then write that record and start the next. Probably easy for those upto speed in VBA but mine is terribly rusty.

Brian

 

[pr2-eugin]

Hello Pink Panther, Welcome to AWF..

I think what you want to do is "Concatenate values from related records", Allen Brown provides a very good explanation of what you need to do..

 

[Brianwarnock]

Ah that's where I had seen it, I thought that it was the Baldyweb and looked there, thanks Paul .

Brian

 

[Pink_Panther]

Thanks for teh info guys and the warm welcome. WHOAH that link looks complicated :S but that does sound like what i need. ive been doing some more research and it looks like i need to make a function and then somehow run it as a query. This is all just going right over my head to be honest haha. Its hard to understand something over the internet in a field other than your own. i managed to find this code

Select T.ColumnA
, GetList("Select ColumnB From Table1 As T1 Where T1.ColumnA = " & [T].[ColumnA],"",", ") AS ColumnBItems
From Table1 AS T
Group By T.ColumnA;

but i tried to put it in and i managed to make a module but couldnt work out how to get the module to run in a query.

if theres an easier way to do this that isnt access like a VBA script or something i am in. i just need to be able to pull the info out in 2 fields i can put into excel.

I've uploaded a DB with some of the data i need merged. if maybe someone could show me that would be amazing as this is going right over my head haha.

Thanks guys

 

[Mihail]

Run the JoinedData query !
I'll never understand how you intend to use this

 

[Brianwarnock]

I'll never understand how you intend to use this

You and me both.

personally I would have the sku as the first column in the Excel spreadsheet, but why would you want multiple categories in one cell.

I can't open accdb to look at the code but with the fields reversed I think that exporting the original data to Excel and doing the work there would be a simple VBA task.

Brian

 

[Mihail]

JoinedData JoinedData PC Cable & Adapters|Galaxy S2 i9100|Galaxy Note i9220|Electronics|Electronic Cigarette|Kindle Accessories|LED Desk Lamps|Computer Cables|Microscopes & Endoscope|Housekeeping & Organization|iPhone 5 |GAME Accessories For NDS/DS Lite|WebCams|Mini Projector|Health Care|Tablet PC & Netbook|USB Hub|Hot-Sellers|BT Function Headset|NDSi Accessories|1 Car Accessories|Alarm Devices|Funny Gadgets|Green Laser Pointer|Tools Gadgets|Stationeries|2 Galaxy S3 i9300|Tablet PC Accessaries|RC Toy|HDMI cables|Camera Tripod & Mount|3 BT Function Devices|Laptop Gadgets|Touch Pen|4 Bicycle Gadgets|Card Readers & Adapters|FM Transmitter|Screen Protectors|Samsung Accessories|RC Helicopters & Accessories|China Phone Accessories|5 Cell Phone|PSP Accessories|Mobile Phone Cases|6 Home Gadgets|Screwdrivers|7 iPhone Transmitter|iPhone 3G Case|8 Openers|9 iPad Screen Protector|Solar Charger & Gadgets|Media Player Accessories|10 HTC Accessories|Stand|11 Cell Phone Batteries|13 Galaxy Tab Series|14 iPhone Screen Protector|17 Cell Phone Cables & Keypads|19 Case and Keyboard|20 BT Function Keyboard|21 Car Charger|Headphone & Earpiece|22 Docks|Car Gadgets|26 Mobile Phone Chargers|29 Adapters|30 Armbands|31 iPod|32 iPhone Earphone|iPad 2 |33 iPad Charger|38 Cell Phone Stylus|44 Cell Phone Gadgets|45 iPad Mini Accessories|59 Power Bank|iPhone Spare Parts|61 Mounts & Holders|62 Macbook|63 Stylus|84 iPad Case|93 iPhone Battery|94 iPhone Charger|99 Cables|iPhone 5 Gadgets|123 Apple Gadgets|154 Today's Deal|174 Under $1.99|176 iPhone 5 Accessories|199 iPhone 4/4S Case|303 iPhone 5 Cases|304

 

[Mihail]

@Brian
The result is something like this (but in separate rows)
I have try to save in 2003 for you, but Access wont bcause some incompatible features.

 

[Brianwarnock]

Thanks for trying.

I know my software is out of date, a bit like me, but 7+ years into retirement and only using Access on the forum I'm not about to update and go through the learning curve.


Brian

 

[Pink_Panther]

Hi,

Thanks for the help. but i dont think i explained it right, sorry. the first one should have a category id of 0 in a seperate field and then in the field next to it called category name text should have Adapters. then on the next line down it should have a category id of 1 and in the category name field should be Cell Phone Gadgets|Adapters|Apple Gadgets|Mounts & Holders. then category id 2 should have a category name text of iPhone Battery|iPhone 5 Gadgets|Power Bank

the reason the data needs to be like this is for a program called CSVI which wont accept categories in multiple fields for products if you do it that way it only leaves the last one as the category, very annoying. and yeah the category id is equal to a sku number.

I have uploaded the DB with a new table called how it should look. so you can see what i mean

Thanks guys

 

[Pink_Panther]

You and me both.

personally I would have the sku as the first column in the Excel spreadsheet, but why would you want multiple categories in one cell.

I can't open accdb to look at the code but with the fields reversed I think that exporting the original data to Excel and doing the work there would be a simple VBA task.

Brian

Yeah the id matches up against my other sheet in excel that does have a sku and all the other info. this data was originally from an xml file which has been cut into about 10 different files and has linked them all together by this id number. the problem is teh program i use to import only accepts everything for one sku on one line. it wont accept multiple lines. so everything else i can pull into a finished document in about 5 mins. its just teh categories i cant get to work. What kind of VBA script would i be looking at using? they dont have the same amount of categories each which is what let me down in trying to use excel.

 

[Brianwarnock]

I have attached a Workbook which I believe demonstrates what you require.
The code for the example is attached to sheet original.

To use this approach you would write a simple query selecting sku and category and export to excel, then do the organisation there, which I understand is where it is going anyway.

Brian

 

[Pink_Panther]

I have attached a Workbook which I believe demonstrates what you require.
The code for the example is attached to sheet original.

To use this approach you would write a simple query selecting sku and category and export to excel, then do the organisation there, which I understand is where it is going anyway.

Brian

Hi,

Thats exactly what i want. how do i use that xls sheet? I added lines to it but it didnt update in the concat tab?

 

[Brianwarnock]

Did you run the code?
As I said in the example spreadsheet it is allied to the sheet so you can right click on the tab , view code and then run.

The code addresses the sheets explicitly so could be moved to "This workbook" or a module for the live application and run from a comman button.

Brian

 

[Pink_Panther]

Thanks heaps man! that is exactly what i needed! life saver! thanks guys

 

[Pink_Panther]

Hey,

So i tried the script with my data and it joined some fields together with the sku so it looksl ike this 400|401|402 where the should be on each line and it didnt join any category fields together it just put them on another line. I have attached the File so you can see. Sorry to be such a pain. It has removed all teh duplicates though! YUS! haha

 

[Mihail]

Run query JoinedByCategory

 

[Brianwarnock]

Hey,

So i tried the script with my data and it joined some fields together with the sku so it looksl ike this 400|401|402 where the should be on each line and it didnt join any category fields together it just put them on another line. I have attached the File so you can see. Sorry to be such a pain. It has removed all teh duplicates though! YUS! haha

Your data appears to be in the opposite columns to mine,though not the headings, so you either need to output the data to match the order SKU, Category or tweak the code which should be simple todo. I have stated in more than one post that I would have the fixed SKU first before the variable categories and that is how I wrote it, you merely need to swap the column indexes.

Brian