Error in multiple value stored field

[Claire]

I am working on unioning three different queries, and have successfully moved
around and added dummy fields so that they have the same fields in the same
order. The union pulls in everything correctly, with the correct number of
records, except that the Department field has errors in every record.

This field is in each query in the same way, with the same relationship
order, and no errors in the original queries. Department is a lookup field
that can store multiple values. Can union queries not deal with this?

This field allows multiple values because some employees work in more than
one department, but if this is truly the problem I'm considering creating a
department name that contains both, or just assigning those people to only
one department (there aren't many people that work for more than one
department, so it wouldn't be awful).

Here's the SQL:
select * from [hrs per job qry]
union all
select * from [hrs per lead qry]
UNION ALL
select * from [hrs unapplied qry];

And yes, I already know that I shouldn't have spaces in my query names, but
I've decided to live on the wild side, at least for now.


Thanks for any suggestions,
Claire

 

[Allen Browne]

Clare, what happens if you add:
.Value
to the name of the multi-valued field in each of the lower level queries?

What I would expect is that it should generate a row for each of the
multiple values, so most people will have 1 row (since they have only one
value) but some will have more. Without testing, I'm guessing that It might
work better with the UNION.

 

[John Spencer]

This sounds like the Access 2007 Multi-value field. Perhaps that doesn't play
nicely with Union queries.

I don't know. I am speculating.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Claire]

It is indeed the Access 2007 multi-value field. And currently it looks like
it doesn't play well with union queries. I tried adding .value to the first
level queries, and the departments pulled through on those queries. The
union query does not have errors anymore, but each record is null. So, not
any more helpful than the errors. Any other ideas out there?

 

[Claire]

I changed the field to only accept one value (ie made a new one), and not
surprisingly everything works. I currently hypothesize that union queries
can not handle multi-value fields. In this case it limits me a bit, but not
entirely. However I can think of some situations where it would be more
limiting, or at least require creative ways around using the multi-value
field. Let me know if anything else comes to mind.