Another parsing question 1

[vbahelp07]

Hello everyone!

I have the following question / issue.

i need to parse out the 3 letter ArtistCodes as some items have more then one Artist that designed the item.

I am using the following at the moment:
1stArtist: Left([DB_UDF_IMH_ARTIST],InStr([DB_UDF_IMH_ARTIST],".")-1)

and getting this as the result:
1stArtist DB_UDF_IMH_ARTIST
#Error MMF
MMF MMF.DRG
MMF MMF.DRG
MMF MMF.DRG
MMF MMF.EAC
MMF MMF.SNS
MMF MMF.SNS

ok, the question / issue on what I am trying to do.
I need to show the ArtistCodes as they were inputted BUT also separate the codes out as their own record.
this is so that the total per Artist can be calculated.

MMR.DRG means both MMR and DRG Artists designed an item.

MMF.DRG	VHW003 $657.96

MMF VHW003 $328.98
DRG VHW003 $328.98

i hope I made sense here ...

 

[dhookom]

Do you have a table of each unique ARTIST value?
Can you provide some actual table and field names?

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[vbahelp07]

Hi dhookom!

sort of, the actual full name has not been provided.

ArtistCode    ArtistFullName
DRG
EAC
EAC.DRG
GGN
GNN
KMG
MMF
MMF.DRG
MMF.EAC
MMF.SNS
SNS
SNS.DRG
TJF
TJF.DRG
TJF.EAC
TKB.SNS

please let me know if you need add'l / other information.

They are looking to see how much of these Artist's designs did last year. The ones with the combined codes separated value "." would split the InvoiceTotal per item sold.

 

[lespaul]

MMR.DRG means both MMR and DRG Artists designed an item.

you have a normalization issue....you are storing two pieces of information in a single record...

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[dhookom]

Can you provide some actual table and field names?

If you create a query like

SELECT ArtistCode
FROM tblWithNoName
WHERE ArtistCode Not like "*.*"
GROUP BY ArtistCode;

Do you get a list of every unique artist code?

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[vbahelp07]

lespaul,
yes, i know :-(

unfortunately there wasn't any other choice to enter the codes into their back office database to make this work.
meaning, storing two or more ArtistCodes per item in their Inventory Maintenance.

we are unable to modify the back office table / database structure. so having to come up with a solution that could possible work.

so the option to combine with some sort of separator, the "." between the ArtistCodes was the best bet.

 

[vbahelp07]

dhookom,
the table name and data in it that I've provided is the current unique codes list.

 

[vbahelp07]

dhookom,
sorry, here's the list of unique ArtistsCodes without the "."

Expr1
DRG
EAC
GGN
GNN
KMG
MMF
SNS
TJF

 

[dhookom]

Ok, I still don't see a table name anywhere (I might just be missing it) and I still don't know if a simple query can be written from any table that generates a list of single, unique ArtistsCodes.

Assuming you create a query [qgrpArtistCodes] that results in a column named [ACode] with each different artist in it. You could create a query based on the table name that I can't find and qgrpArtistCodes like:

SELECT DB_UDF_IMH_ARTIST, FieldWithVHW003 , FieldWithCurrency, [ACode]
FROM tblIDontKnowTheName, qgrpArtistCodes
WHERE DB_UDF_IMH_ARTIST Like "*" & [ACode] & "*";

From this last query you should be able to write a query that divides the FieldWithCurrency by the number of records from the query with the same FieldWithVHW003.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[vbahelp07]

dhookhom,

i should have been more specific ... not very used to doing this. here are all the tables involved.

there are a total of 5 tables

Artist info table:
tablename: ArtistsCodes
fieldname1: ArtistCodes
fieldname2: ArtistFullName

the invoice info table:
tablename: ARO
fieldnames used: InvoiceNumber, SOItemNumber and ItemTotal

tablenmae: ARN
fieldnames used: InvoiceDate, InvoiceNumber, InvoiceDate

item info table:
tablename: IM1
fieldnames used: ItemNumber

tablename: IM90_UDF
fieldnames used: ItemNumber and ArtistCode


table links:
ArtistsCodes equal join to IM90_UDF on ArtistCodes to ArtistCode.
IM90_UDF equal join to IM1 on ItemNumber to ItemNumber
IM1 equal join to ARO on ItemNumber to SOItemNumber
ARO to ARN on InvoiceNumber to InvoiceNumber

actual query: i shortened the tablenames above but you can see the table names within the actual ones here:

SELECT IM_90_UDF_IM_Masterfile.DB_UDF_IMH_ARTIST AS Artist, IM1_InventoryMasterfile.ItemNumber, Sum(ARO_InvHistoryDetail.SOExtChargeAmount) AS [NetSales$]
FROM ArtistsCodes INNER JOIN (((IM1_InventoryMasterfile INNER JOIN IM_90_UDF_IM_Masterfile ON IM1_InventoryMasterfile.ItemNumber = IM_90_UDF_IM_Masterfile.ItemNumber) INNER JOIN ARO_InvHistoryDetail ON IM1_InventoryMasterfile.ItemNumber = ARO_InvHistoryDetail.SOItemNumber) INNER JOIN ARN_InvHistoryHeader ON ARO_InvHistoryDetail.InvoiceNumber = ARN_InvHistoryHeader.InvoiceNumber) ON ArtistsCodes.ArtistCode = IM_90_UDF_IM_Masterfile.DB_UDF_IMH_ARTIST
WHERE (((IM_90_UDF_IM_Masterfile.ML_UDF_IMH_RELEASEDATE) Between #1/1/2007# And #12/31/2007#) AND ((ARN_InvHistoryHeader.SOTransDate) Between #1/1/2007# And #12/31/2007#))
GROUP BY IM_90_UDF_IM_Masterfile.DB_UDF_IMH_ARTIST, IM1_InventoryMasterfile.ItemNumber
ORDER BY IM_90_UDF_IM_Masterfile.DB_UDF_IMH_ARTIST, IM1_InventoryMasterfile.ItemNumber;

i need to somehow keep the grouped ArtistCodes together and in another column show the separated values for individual Artists and see the totals for each as well as a grouped.

 

[vbahelp07]

one example of result:

Artist	ItemNumber	NetSales$
DRG	VHW018         	$929.23
EAC	AP223          	$4,002.80
EAC.DRG	APM805         	$7,579.70

Each Artist has several itemnumbers they designed.
There is at least one or more itemnumbers where two Artists designed.

Here EAC and DRG are the Artists for Itemnumber APM805 with a 2007 net sales of $7,579.70.

the actual result we're looking for is:

Artist	ItemNumber	NetSales$
DRG	VHW018         	$929.23
DRG     APM805          $3789.85 (half of $7,579.70)
EAC	AP223          	$4,002.80
EAC     APM805          $3789.85 (half of $7,579.70)
EAC.DRG	APM805         	$7,579.70

HTH what I am trying to ask?

 

[dhookom]

Do you understand the solution that I have suggested so far?

I still don't know if a simple query can be written from any table that generates a list of single, unique ArtistsCodes

Can you get this from your ArtistsCodes table?

What is the name of your query? Did you try create a new query based on your query and a unique query of artist codes (if you can create one)? If you can't create a query of unique artist codes, then create a table. Then try my solution to see if you get close to what you want.


Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[vbahelp07]

dhookhom,
sorry, yes, no i didn't really understand it.

SELECT DB_UDF_IMH_ARTIST, FieldWithVHW003 , FieldWithCurrency, [ACode]
FROM tblIDontKnowTheName, qgrpArtistCodes
WHERE DB_UDF_IMH_ARTIST Like "*" & [ACode] & "*";

which would specifically for me would be:

SELECT DB_UDF_IMH_ARTIST, ItemNubmer, SOExtAmount, [ACode]
FROM tblArtistsCodes, qgrpArtistCodes
WHERE DB_UDF_IMH_ARTIST Like "*" & [ACode] & "*";

I currently have a query where I have linked all the tables to get the data needed.

It shows each Artist codes for each item and the total sold for the item.

i need to show the Artist codes separated out into their own 3 letter code. this is where I think i need the parsing help to parse out the other code in the "." separating value in the ArtistCodes field.

On that, i need to have the SOExtAmount divided by 2 or the number of combined codes per the "."

I need the original ArtistCodes to show as it is been entered into the system for the item in tact. Need the parsed information as another column.

 

[dhookom]

What is the name of the query/table that returns this result
[tt][blue] Expr1
DRG
EAC
GGN
GNN
KMG
MMF
SNS
TJF[/blue][/tt]

Again, what is the name of the query that begins

SELECT IM_90_UDF_IM_Masterfile.DB_UDF_IMH_ARTIST AS Artist,

These are the two tables/queries that can be combined in the query I suggested with

SELECT DB_UDF_IMH_ARTIST, FieldWithVHW003 , FieldWithCurrency, [ACode]
FROM tblIDontKnowTheName, qgrpArtistCodes
WHERE DB_UDF_IMH_ARTIST Like "*" & [ACode] & "*";

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[vbahelp07]

I didn't save the query to get the "every unique artist code"

If you create a query like
CODE
SELECT ArtistCode
FROM tblWithNoName
WHERE ArtistCode Not like "*.*"
GROUP BY ArtistCode;
Do you get a list of every unique artist code?

the name of the query that begins the SELECT IM_90 is ArtistsSales

i will try that

 

[vbahelp07]

ok,
i think i applied the suggestion

SELECT ArtistsSales.Artist, ArtistsSales.ItemNumber, ArtistsSales.[NetSales$], UniqueArtistCodes.ArtistCode
FROM ArtistsSales LEFT JOIN UniqueArtistCodes ON ArtistsSales.Artist = UniqueArtistCodes.ArtistCode
WHERE (((ArtistsSales.Artist) Like "*" & [ArtistCode] & "*"));

the result is not showing any of the combined Artist
example

Artist	ItemNumber	NetSales$	ArtistCode
EAC	AP223          	$4,002.80	EAC
EAC.DRG	APM805         	$7,579.70

This what I need:
Artist ItemNumber NetSales$
DRG VHW018 $929.23
DRG APM805 $3789.85 (half of $7,579.70)
EAC AP223 $4,002.80
EAC APM805 $3789.85 (half of $7,579.70)
EAC.DRG APM805 $7,579.70

what do I change?

 

[dhookom]

As per my suggestions, there is no join in the query. Try

SELECT ArtistsSales.Artist, ArtistsSales.ItemNumber, ArtistsSales.[NetSales$], UniqueArtistCodes.ArtistCode
FROM ArtistsSales , UniqueArtistCodes 
WHERE (((ArtistsSales.Artist) Like "*" & [ArtistCode] & "*"));

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[vbahelp07]

lol!
i know and i was wondering what to do about that join thing so i just ended up doing the LEFT JOIN.

thanks for the clarification.

i just thought there always has to be some join.

yeah!

Artist	ItemNumber	NetSales$	ArtistCode
EAC.DRG	APM805         	$7,579.70	DRG
EAC.DRG	APM805         	$7,579.70	EAC

so i do i show the split NetSales$ next to the ArtistCode only when this happens? Meaning when it parses the Artist

You are awesome!

 

[vbahelp07]

thinking something like

SplitSales:iif([Artist] is len([Artist])>3,[NetSales]/2, [NetSales$])

but obviously not the correct syntax.

 

[vbahelp07]

i meant

SELECT ArtistsSales.Artist, ArtistsSales.ItemNumber, ArtistsSales.[NetSales$], UniqueArtistCodes.ArtistCode, IIf(Len([Artist])>3,[NetSales$]/2,[NetSales$]) AS SplitSales
FROM ArtistsSales, UniqueArtistCodes
WHERE (((ArtistsSales.Artist) Like "*" & [ArtistCode] & "*"));

and it works!

Artist	ItemNumber	NetSales$	ArtistCode	SplitSales
EAC.DRG	APM805         	$7,579.70	DRG	3789.85
EAC.DRG	APM805         	$7,579.70	EAC	3789.85

thank you!