DCount multi value fields

[Xterra14s]

Hello everyone,


In my database I have a field for supplies with multivalue field. It has to be done this was since it's one order with multiple supplies that can be selected. I ran a query to pull all the orders between two dates. I need it to count all the orders as one section, all machines as another, and then all supplies as the last. The order part works since i'm counting the actual order number. Since machines and supplies are multivalue fields it's counting each supply and each machine instead of just the order that has machines.

Example: There are 4 orders. 1 order is for machines and 3 are for supplies. with dcount its counting 4 orders, 1 machine, and 22 supplies (more than one supply per orer). Any idea how I can get it to count the field and not each supply or machine separately in the order?



This is what I have so far:
=DCount("[Machines]","Current Query",Not Null)
=DCount("[Supplies]","Current Query",Not Null)

=DCount("[Order ID]","Current Query")

[Bulzie]

Not sure what your query is returning but that is what it is counting. Can you give better example of the data from your query. Also maybe try to use Totals/GrroupBy option in your query to get distinct records.

[Xterra14s]

This is what its pulling. It's counting each one of those supplies as 1 instead of counting the whole field as one.

[CJ_London]

multivalue fields are not intended to be used in this way - they should be used for a limited range of relatively fixed values like days of the week, months of the year, type of vehicle where a record could be 'more than one choice', e.g. 'deliveries only on Mondays and Wednesdays' or 'send report in Jan, July and August' op 'vehicle available as 'sedan, convertible'

to access the individual component in a multivalue you use multivaluefieldname.value

however I have no idea if this will work in a domain function like dcount. The alternative is to build a group by query for your form recordsource which can do the equivalent to your dcount calculation.

It does sound like your table design and relationships are wrong but without knowing the full story can't really comment further

[Bulzie]

"This is what its pulling. It's counting each one of those supplies as 1 instead of counting the whole field as one." What do you mean? For 2481, it should be 1 supply or 7? Are each of those orders tied to a customer? Can you give examples of what your counts should look like for that example you have.

[Xterra14s]

This report is more of an audit that will be done monthly, quarterly, etc. This query for that date should say there are 4 total orders of which 1 consisted of a machine and 4 consisted of supplies. Each order is tied to a customer by the Order ID number

[Bulzie]

nm, keep thinking you need the supplies split out. So do all orders have at least 1 supply or no? Does each Order record have a CustomerID that links back to the Customer table?