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

 

[xenou]

Currently Access is just displaying one name even though the field must contain others.

Don't you want John Jones? If you don't have that criteria would you get more results?

Edit: sorry what I mean is that you are you getting John Jones or more than John Jones?
First you said:

when the query is run I get John Jones entries but also other Salespeople

then you said:

Currently Access is just displaying one name even though the field must contain others.

So either you are getting one name or more than one name but I'm not sure which!

 

[Jubert]

Hi Xenou
It's funny how much sense you think your post makes until someone asks a question.
Sorry I wasn't clear.
I wanted all records where the sales person was John Jones. However I got records returned which said e.g. Gary Robinson which was completely unexpected. The record for Gary doesn't have John's name in the salesperson field visible but it must be in there because of the filter criteria. So whilst it is fine for the records to be returned as they are what I really need to be able to do is see the entire field which must be Gary Robinson, John Jones.

Does that help?

 

[xenou]

I normally don't use Multi-valued fields but in general they should work. Any particular reason why you think this is a multi-valued field or is that just a guess? What is your actual query? What is the data in the tables (if you can be that specific?) What are the fields in this four-column table? What happens when you just select * from that table?

 

[Jubert]

It is a total guess. I've just been trying to work out why the results appear as they do and that is what I came up with .

This is the SQL for the MS Access Query
SELECT dbo_UDEF_SALES_SPLITS.CH_CODE, dbo_UDEF_SALES_SPLITS.CH_USER6, dbo_UDEF_SALES_SPLITS.SALES_PERCENT, dbo_UDEF_SALES_SPLITS.SplitFROM dbo_UDEF_SALES_SPLITS
WHERE (((dbo_UDEF_SALES_SPLITS.CH_USER6)="John Jones"));

I have exported the result to excel so I can display a selection of records for you below.

Excel 2010

	A	B	C	D
1	CH_CODE	CH_USER6	SALES_PERCENT	Split
2	MW21713A	JOHN JONES	100	1st
3	MW23034	JOHN JONES	50	1st
4	MW23034D	JOHN JONES	50	1st
5	MW23194	JOHN JONES	60	1st
6	MW23194D	JOHN JONES	60	1st
7	MW22683	JOHN JONES	100	1st
8	MW22683D	JOHN JONES	100	1st
9	MW23233	JOHN JONES	100	1st
10	MW23233D	JOHN JONES	100	1st
11	MW23424	JOHN JONES	100	1st
12	MW23424D	JOHN JONES	100	1st
13	MW23426	JOHN JONES	100	1st
14	MW25243	JOHN JONES	100	1st
15	MW25243D	JOHN JONES	100	1st
16	MW25325	JOHN JONES	100	1st
17	MW25325D	JOHN JONES	100	1st
18	MW25317D	JOHN JONES	100	1st
19	MW25317	JOHN JONES	100	1st
20	MW25478	JOHN JONES	100	1st
21	MW25478D	JOHN JONES	100	1st
22	MW25333	JOHN JONES	100	1st
23	MW25333D	JOHN JONES	100	1st
24	MW24957	JOHN JONES	100	1st
25	MW24957D	JOHN JONES	100	1st
26	MW24959	JOHN JONES	100	1st
27	MW27215	JOHN JONES	100	1st
28	MW27215D	JOHN JONES	100	1st
29	MW27217	JOHN JONES	100	1st
30	MW27217D	JOHN JONES	100	1st
31	MW27219	JOHN JONES	100	1st
32	MW27219D	JOHN JONES	100	1st
33	MW24897A	JOHN JONES	100	1st
34	MW24957A	JOHN JONES	50	1st
35	MW24959A	JOHN JONES	50	1st
36	MW27217A	JOHN JONES	100	1st
37	MW27215A	JOHN JONES	100	1st
38	MW27219A	JOHN JONES	100	1st
39	MW26958A	JOHN JONES	100	1st
40	MW26205A	JOHN JONES	100	1st
41	MW28164	JOHN JONES	100	1st
42	MW28164D	JOHN JONES	100	1st
43	MW28166	JOHN JONES	100	1st
44	MW28166D	JOHN JONES	100	1st
45	MW28045	JOHN JONES	100	1st
46	MW28045D	JOHN JONES	100	1st
47	MW27828	JOHN JONES	100	1st
48	MW27828D	JOHN JONES	100	1st
49	MW28244	JOHN JONES	100	1st
50	MW28244D	JOHN JONES	100	1st
51	MW27034A	JOHN JONES	100	1st
52	MW12759A	GARY ROBINSON	50	1st
53	MW12773A	GARY ROBINSON	50	1st
54	MW12948	MARK OSGERBY	50	1st
55	MW12994A	GARY ROBINSON	50	1st
56	MW13341A	GARY ROBINSON	50	1st
57	MW13183A	GARY ROBINSON	50	1st
58	MW13511A	GARY ROBINSON	50	1st
59	MW13302A	GARY ROBINSON	50	1st
60	MW13747A	GARY ROBINSON	50	1st
61	MW13694	ZOE COX	50	1st
62	MW13694D	ZOE COX	50	1st
63	MW13750	ZOE COX	50	1st
64	MW13750D	ZOE COX	50	1st
65	MW13934A	ZOE COX	50	1st
66	MW14457	ZOE COX	50	1st
67	MW14457D	ZOE COX	50	1st
68	MW14457A	ZOE COX	37.5	1st
69	MW18733	SIMON BEST	60	1st
70	MW18733D	SIMON BEST	60	1st
71	MW18872	SIMON BEST	50	1st
72	MW18872D	SIMON BEST	50	1st
73	MW21320D	SIMON BEST	50	1st
74	MW21507	SIMON BEST	50	1st
75	MW21507D	SIMON BEST	50	1st
76	MW22364	SIMON BEST	50	1st
77	MW22364D	SIMON BEST	50	1st
78	MW20954A	SIMON BEST	25	1st
79	MW23143	SIMON BEST	50	1st
80	MW23143D	SIMON BEST	50	1st
81	MW24928	MIKE DAVIES	50	1st
82	MW24928D	MIKE DAVIES	50	1st
83	MW23720	MIKE DAVIES	50	1st
84	MW23720D	MIKE DAVIES	50	1st
85	MW25774	SIMON BEST	80	1st
86	MW25774D	SIMON BEST	80	1st
87	MW25917	SIMON BEST	50	1st
88	MW25917D	SIMON BEST	50	1st
89	MW24928A	MIKE DAVIES	50	1st
90	MW27227	SIMON BEST	80	1st
91	MW27223	SIMON BEST	80	1st
92	MW27223D	SIMON BEST	80	1st
93	MW27227A	SIMON BEST	80	1st
94	MW27221A	SIMON BEST	80	1st
95	MW27223A	SIMON BEST	80	1st
96	MW27407	IVAN THOMAS	80	1st
97	MW27407D	IVAN THOMAS	80	1st
98	MW27300A	SIMON BEST	80	1st
99	MW16106	MARK OSGERBY	80	1st
100	MW16106D	MARK OSGERBY	80	1st

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

 

[xenou]

what happens when you look at those codes for the other people. How are they related to John Jones? Do you have access to the table? What's in the table?

 

[james_lankford]

open dbo_UDEF_SALES_SPLITS in design mode --- maybe take a screenshot and post it

 

[Jubert]

open dbo_UDEF_SALES_SPLITS in design mode --- maybe take a screenshot and post it

So Row 54 has an entry for MARK OSGERBY. If I change the criteria from "JOHN JONES" to "MARK OSBERBY" (and add in a criteria for the CH_CODE MW12948) then the row is returned as per the original export i.e. with "MARK OSGERBY" in the CH_USER6 field (identical to row 54 previously posted)

SELECT dbo_UDEF_SALES_SPLITS.CH_CODE, dbo_UDEF_SALES_SPLITS.CH_USER6, dbo_UDEF_SALES_SPLITS.SALES_PERCENT, dbo_UDEF_SALES_SPLITS.Split
FROM dbo_UDEF_SALES_SPLITS
WHERE (((dbo_UDEF_SALES_SPLITS.CH_CODE)="GS12948") AND ((dbo_UDEF_SALES_SPLITS.CH_USER6)="MARK OSGERBY"));

 

[Jubert]

what happens when you look at those codes for the other people. How are they related to John Jones? Do you have access to the table? What's in the table?

There is separate table which shows how sales are split for a job (below filtered for same CH_CODE) but the two tables are not linked (though must be in the accounting package I guess).

I just want to 'See' both names in the original table.

Excel 2010
	A	B	C	D	E	F	G
1	CH_CODE	SALES_PERSON1	SALES_PERCENT1	SALES_PERSON2	SALES_PERCENT2	SALES_PERSON3	SALES_PERCENT3
2	MW12948	MARK OSGERBY	50	JOHN JONES	50		0
Sheet1

 

[Micron]

It looks like you're saying in post #8 that will give you all the single names for records that match both criteria fields, and may give you what you want - for now. Not sure I see how.
But if you need a list of Mark, or John (or whoever) without that restriction, you're back to square one. AFAIK, there is no way to 'filter out' a single name from a multi value field in one step. I think you can do this with a query that returns the .Value from the multi field, then query that query by name. The first query will produce the names in rows; the second would filter by name.