Multi Valued field - how can I display contents?

[Jubert]

I am pulling a sales split table into MS Access from an accounting package.

The table only has 4 columns.
In the design mode I have entered the criteria ="John Jones" against the salesperson field and when the query is run I get John Jones entries but also other Salespeople so I'm assuming the field must be mutli valued. This makes sense as a number of sales people may work on a given contract. But how do I see what is in the field? Currently Access is just displaying one name even though the field must contain others.

Many Thanks

 

[Jubert]

Xenou
I am hoping to return data from SQL to excel via ms query but I've been using access to help me do some of the queries. (In that I find it more user friendly and can copy the code back to ms query).

It's a weird sales split table (the orange one). The sales partner % is given twice but accounting for that the totals do come back to 100%.

I'll post more detail tmrw - sorry today was nuts - and hope that we can figure out a solution.

Micron - I'm not sure that I've established that it is. Just saying what I'm seeing which has managed to confuse me in the results I get. If I can work out the query I'm ok with that. Though appreciate the title of the post becomes a bit misleading!

 

[james_lankford]

please run this query in MS Access and see if the results make any sense

SELECT 
  tbl.CH_CODE, 
  tbl.CH_USER6, 
  tbl.SALES_PERCENT, 
  tbl.Split, 
  'Q 1' as [which query] 
FROM 
  dbo_UDEF_SALES_SPLITS as tbl
WHERE 
(
  (
    tbl.CH_USER6 = 'John Jones' 
  )
)


union all 


SELECT 
  tbl.CH_CODE, 
  tbl.CH_USER6, 
  tbl.SALES_PERCENT, 
  tbl.Split,
  'Q 2' as [which query]  
FROM 
  dbo_UDEF_SALES_SPLITS as tbl
WHERE 
(
  (
    tbl.CH_USER6 <> 'John Jones' 
  )
)


union all 


SELECT 
  tbl.CH_CODE, 
  tbl.CH_USER6, 
  tbl.SALES_PERCENT, 
  tbl.Split,
  'Q 3' as [which query]  
FROM 
  dbo_UDEF_SALES_SPLITS as tbl

just trying to get an idea of what's in the table

 

[Jubert]

please run this query in MS Access and see if the results make any sense

SELECT 
  tbl.CH_CODE, 
  tbl.CH_USER6, 
  tbl.SALES_PERCENT, 
  tbl.Split, 
  'Q 1' as [which query] 
FROM 
  dbo_UDEF_SALES_SPLITS as tbl
WHERE 
(
  (
    tbl.CH_USER6 = 'John Jones' 
  )
)


union all 


SELECT 
  tbl.CH_CODE, 
  tbl.CH_USER6, 
  tbl.SALES_PERCENT, 
  tbl.Split,
  'Q 2' as [which query]  
FROM 
  dbo_UDEF_SALES_SPLITS as tbl
WHERE 
(
  (
    tbl.CH_USER6 <> 'John Jones' 
  )
)


union all 


SELECT 
  tbl.CH_CODE, 
  tbl.CH_USER6, 
  tbl.SALES_PERCENT, 
  tbl.Split,
  'Q 3' as [which query]  
FROM 
  dbo_UDEF_SALES_SPLITS as tbl

just trying to get an idea of what's in the table

So I don't think I can post the result so I will describe what had been returned.
Q1 has 498 records relating to John Jones
Q2 has 12484 records - none of which are JJ
Q3 had 12982 records and these include JJ

 

[Jubert]

Ok so interesting thing just happened.
I started to try to write the query as per posts #18 and #19.
If I group totals by the CH_USER6 then the names appear separately which I believe lets me get to what I need to - though I'm not really sure what's happening behind the scenes

 SELECT dbo_UDEF_SALES_SPLITS.CH_CODE, dbo_UDEF_SALES_SPLITS.CH_USER6, Sum(dbo_UDEF_SALES_SPLITS.SALES_PERCENT) AS SumOfSALES_PERCENT, dbo_UDEF_SALES_SPLITS.Split
FROM dbo_UDEF_SALES_SPLITS
GROUP BY dbo_UDEF_SALES_SPLITS.CH_CODE, dbo_UDEF_SALES_SPLITS.CH_USER6, dbo_UDEF_SALES_SPLITS.Split
HAVING (((dbo_UDEF_SALES_SPLITS.CH_CODE)="MW24248"));

Excel 2010
	A	B	C	D
1	CH_CODE	CH_USER6	SumOfSALES_PERCENT	Split
2	MW24248	JOHN JONES	50	1st
3	MW24248	GARY ROBINSON	10	2nd
4	MW24248	MARK OSGERBY	40	3rd
Sheet2

 

[xenou]

Progress is good. It would be interesting to see the same query without the group by - to see what it is summing up. Not sure why it would be working now all of a sudden.

 

[Micron]

This is a known behavior of multi valued fields. Grouping By will break out multi value fields into their individual elements since each one will constitute a group. At the risk of being repetitive - do we have a multi value field or not? I'm beginning to think providing a copy of the table, or any tables involved in a query that produces the posted results, will be the only way to figure it out.

 

[james_lankford]

I don't think its a multivalued field

when I asked him to run the query for
= 'John Jones'
he only got John Jones

and the the results for the other queries I asked him to run also sound right, especially since the numbers add up

I haven't looked at his "group by" results yet