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]

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

Hi James

I'm not sure how to post a screenshot. I have the excel HTML add in but not one for Access. Any tips?

 

[james_lankford]

can you post it here
Imgur: The most awesome images on the Internet
and then give us the link

 

[xenou]

Duh, Micron is right.

. 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.

So just (though it seems crazy) using a query on the query:

SELECT B.* FROM 
(
SELECT 
	A.CH_CODE, 
	A.CH_USER6, 
	A.SALES_PERCENT, 
	A.Split
FROM 
	dbo_UDEF_SALES_SPLITS A
WHERE 
	A.CH_USER6 = "John Jones"
) B
WHERE
	B.CH_USER6 = "John Jones"

The contents of the table in Post 9 shows why the data would be so garbled (or not, depending on point of view). JOHN is a salesperson on the same record as MARK so the results are correct.

 

[james_lankford]

I'm still not sure it is a multivalue field

the name of the table is
dbo_UDEF_SALES_SPLITS
and Access usually puts
dbo_
in front of tables that are linked from SQL Server or Oracle or other server databases

and I may be mistaken, but I don't think any of them allow multivalue fields

so I'm a little confused

that's why I'd like to see the table design
I'd also be interested in hearing what kind of database this table is linked from

 

[Jubert]

I'm still not sure it is a multivalue field

the name of the table is
dbo_UDEF_SALES_SPLITS
and Access usually puts
dbo_
in front of tables that are linked from SQL Server or Oracle or other server databases

and I may be mistaken, but I don't think any of them allow multivalue fields

so I'm a little confused

that's why I'd like to see the table design
I'd also be interested in hearing what kind of database this table is linked from

You're right James it is a linked SQL table.

I don't have permissions for this database remotely so will post the shot in imgur tomorrow. It all looked fairly tame in design view but I have an untrained eye.

 

[Jubert]

Micron/Xenou I will try the query route and let you know where that takes me although I did attempt before when I first discovered the issue and didn't have much success.

Thanks for posting.

 

[Jubert]

https://imgur.com/a/t4uDH

 

[Jubert]

Just to set the scene further. Essentially what I am trying to do is return a table of Sales by Salesperson. If I can show the three names in cell in column B it would allow me to do a calculation on the amount in column C. The colour coding on the table is just to represent that the data is returned from 3 different tables.
Excel 2010

	A	B	C	D	E	F	G	H	I	J

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #D8E4BC, align: center"]CH_CODE[/TD]
[TD="bgcolor: #D8E4BC, align: center"]CH_USER6[/TD]
[TD="bgcolor: #B7DEE8, align: center"]CH_NETT[/TD]
[TD="bgcolor: #FCD5B4, align: center"]SALES_PERCENT[/TD]
[TD="bgcolor: #FCD5B4, align: center"]SALES_PERSON1[/TD]
[TD="bgcolor: #FCD5B4, align: center"]SALES_PERCENT1[/TD]
[TD="bgcolor: #FCD5B4, align: center"]SALES_PERSON2[/TD]
[TD="bgcolor: #FCD5B4, align: center"]SALES_PERCENT2[/TD]
[TD="bgcolor: #FCD5B4, align: center"]SALES_PERSON3[/TD]
[TD="bgcolor: #FCD5B4, align: center"]SALES_PERCENT3[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #D8E4BC"]MW24248[/TD]
[TD="bgcolor: #D8E4BC"]JOHN JONES[/TD]
[TD="bgcolor: #B7DEE8, align: right"]6000[/TD]
[TD="bgcolor: #FCD5B4, align: right"]50[/TD]
[TD="bgcolor: #FCD5B4"]JOHN JONES[/TD]
[TD="bgcolor: #FCD5B4, align: right"]50[/TD]
[TD="bgcolor: #FCD5B4"]GARY ROBINSON[/TD]
[TD="bgcolor: #FCD5B4, align: right"]10[/TD]
[TD="bgcolor: #FCD5B4"]MARK OSGERBY[/TD]
[TD="bgcolor: #FCD5B4, align: right"]40[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #D8E4BC"]MW24248[/TD]
[TD="bgcolor: #D8E4BC"]JOHN JONES[/TD]
[TD="bgcolor: #B7DEE8, align: right"]6000[/TD]
[TD="bgcolor: #FCD5B4, align: right"]50[/TD]
[TD="bgcolor: #FCD5B4"]JOHN JONES[/TD]
[TD="bgcolor: #FCD5B4, align: right"]50[/TD]
[TD="bgcolor: #FCD5B4"]GARY ROBINSON[/TD]
[TD="bgcolor: #FCD5B4, align: right"]10[/TD]
[TD="bgcolor: #FCD5B4"]MARK OSGERBY[/TD]
[TD="bgcolor: #FCD5B4, align: right"]40[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #D8E4BC"]MW24248[/TD]
[TD="bgcolor: #D8E4BC"]JOHN JONES[/TD]
[TD="bgcolor: #B7DEE8, align: right"]6000[/TD]
[TD="bgcolor: #FCD5B4, align: right"]50[/TD]
[TD="bgcolor: #FCD5B4"]JOHN JONES[/TD]
[TD="bgcolor: #FCD5B4, align: right"]50[/TD]
[TD="bgcolor: #FCD5B4"]GARY ROBINSON[/TD]
[TD="bgcolor: #FCD5B4, align: right"]10[/TD]
[TD="bgcolor: #FCD5B4"]MARK OSGERBY[/TD]
[TD="bgcolor: #FCD5B4, align: right"]40[/TD]

</tbody>

Sheet2​

Whilst entering criteria against CH_USER6 in design view I can put "JOHN JONES", "GARY ROBINSON" OR "MARK OSGERBY" and return the three records. However if I write a statement asking if CH_USER6=SALES_PERSON2 it returns FALSE. I was hoping to use the IIF with the orange table to return what I need.

I haven't had any success with the .value query yet

 

[xenou]

The colour coding on the table is just to represent that the data is returned from 3 different tables.

Maybe we could write a query using the three tables, then? That would be ideal. What do you mean by three tables?

Also, how are you getting this data into Excel? Is it from Access, from SQL, from some other source?

Also, btw, the sales percents add up to more than 100% in your last example for MW24248!

 

[Micron]

I don't see where we've established that this is indeed a multi-value field...