MVFs create duplicate value in Query, how to avoid ?

[hvsummer]

ok, After I used MVFs (Multivalue Fields) to query thing, I got alittle problem with it, It created duplicate value in query, can anyone spot out the problem and give some solution idea ?

the result should be 20 rows since I have only 20 bills in table2.
here my query SQL (this code generate 28 rows, duplicate 8 rows)

SELECT Table2.NameClient, Table2.ID
   , Table2.Item, Table2.Vol
   , Table2.[Bill-date], Table2.Price
   , IIf([Table2].[Bill-date] 
         Between [table3].[from] 
            And [table3].[to]
         ,[table3].[km]
         ,0) 
   AS KM
FROM Table2 
   LEFT JOIN Table3 
      ON Table2.Item 
      = Table3.IDbrand.Value;
 

I try left, right, inner join, and even "select distinct" (decrease from 28 to 24 rows, duplicate 4 rows)

SELECT DISTINCT Table2.NameClient, Table2.ID
   , Table2.Item, Table2.Vol
   , Table2.[Bill-date], Table2.Price
   , IIf([Table2].[Bill-date] 
         Between [table3].[from] 
            And [table3].[to]
         ,[table3].[km]
         ,0)
      AS KM
FROM Table2 
   INNER JOIN Table3 
      ON Table2.Item 
      = Table3.IDbrand.Value;

it still have lot of duplicate rows.

Did anyone deal with this problem ? how to avoid duplicate rows created by MultiValue Field ?

[hvsummer]

oh god, I find out, not MVFs, but the "between .. and .." create these duplicate field.

ok, I have to compare bill-date which is between start day (from) and end day (to) in table to determine whether it have table3.KM or not.

first Access compare ID, if ID match in relationship, then I'll calculate iif condition, but why it give back duplicate result ?
this is not MFVs problem anymore, it's SQL's problem

help me plz, I still suck at SQL ==

[zmbd]

this is not MFVs problem anymore, it's SQL's problem

Are you sure?
:)
(I'm teasing here :) )

Let's take a careful look at things...
---
Looking at the results in the third image, review the values returned in field [KM] for the indicated "duplicate" records. [KM]=0 for one record and [KM]=1000 for the second record.
> To me, this indicates that there is no duplication of the records.

If you do not already do so, try adding a primary key to all of your tables.

Table3:
[PK][From_date][To_Date][IDBrand][KM]

Showing this Primary key in your results would help you (and us) to determine if the record is an actual duplicate or a related record to the search.
--
I've tried to re-create you database in my test environment.
Once again, I am not seeing duplicated records in that the value of [KM] is different. What I am seeing (with the help if the primary key) is that the same record from table2 is being returned for each matching value of the calculated field [KM] between the joins.

I am also getting slightly different values in the recordset for [KM].. most likely because I am working only with the sample data provided :)
For example the first duplicated set of records (note I have a primary key here. I initially designed the tables and queries without the primary key (breaking my own rules) and the results were the same for me either way...

From the first query:
Table2.PK    Table2.NameClient    Table2.ID    Table2.Item    Table2.Vol    Table2.Bill_date    Table2.Price    KM
2    a    1    2    2    2015-01-01    5    1000
2    a    1    2    2    2015-01-01    5    2000

and the second query:
Table2.PK    Table2.NameClient    Table2.ID    Table2.Item    Table2.Vol    Table2.Bill_date    Table2.Price    KM
2    a    1    2    2    2015-01-01    5    1000
2    a    1    2    2    2015-01-01    5    2000

((NOTE) I've not spaced the result out to match the column names here so that it is easier to see the returned values without scrolling the table :) )
--
Without your table relationships, there may be other things going on here that I don't readily see. I'll have to take a better look in little bit.

--
It would be helpful to have a few rows of the records you are expecting to have returned. If you use the [CODE/] button in the formatting toolbar, you can hand type these (as I have done above) as a formatted table.
One thing of note... do not use the [tab] key... use [spaces] to create your columns :)

--
It's my day off, and of course, the kids have no school today for some teacher training session... and the kids are trying to destroy the house ( :sigh: ) so I have to take a break for the moment.

IN the meantime, I've attached a zip-file of the test database I've created to work thru this with you so that our other experts will have something to play with. WE have some real SQL-Wizards!

--

Also:
You should avoid using reserved words and tokens as field names, file names, etc....

SQL Reserved Words - Office 2013 or later (read more)

This is from Allen Browne... his list has a little more detail
Problem names and reserved words in Access this list hasn't been updated since 2010; however, I've not found a word on it yet that wasn't a potential problem :)

[id] [Key_Tokem] [Reserved by]
1506 FROM JET reserved (kb248738);SQL Server reserved;ODBC (kb125948)
2253 TO JET reserved (kb248738);SQL Server reserved;ODBC (kb125948) 
 
 

>>> I you do not already do so, I highly, highly, advise having a primary key in all tables. I tend to you the autonumber field and I do not normally use the PK for anything other than a record id value. Normally, I do not use the PK as anything meaningful to the human user. I learned this the hard way when we changed how samples were identified. Fortunately, I had a fair understanding of SQL and VBA by then and the transition wasn't too horrible... but took a long time to code and implement on the legacy records.

Finally, Please note, the DB-Attachment is in Acc2013 format. In general, I have not had issues with ACC2010 opening these files; however, ACC2007 has occasionally choked and ACC2003 will not open this file.

[hvsummer]

oh my, old question that I have resovled and forgot to mention ==

Still thank you, zmbd, I have solution for this in my db already lol
since they're Many-to-Many relationship, I use group query to clear the duplicate.

not really a duplicate rows, I mean I want only 1 KM return from query, not 2 value like example due to compare between value. So I use sum() to calculate the total KM (since I set value return between range, and return 0 if not valid) so KM value will return right value.

I like this part: "and the kids are trying to destroy the house "

all database I ask you guy here is the simple model, so I just choose random shortest word to explain my idea, don't worry about reserve work, I know bout it and believe me, I will not suffer that problem since I use my language to name my field haha

[zmbd]

Would you mind posting your SQL solution so that others that find your thread can benefit?

Thnx
Z


(house is still standing... I am however, soaked. )

[hvsummer]

I test it in "test.accdb" but actually after resolve, I've change code to solve next problem, so I'll try to recall it ==

SELECT Table2.NameClient, Table2.ID, Table2.Item, Table2.Vol, Table2.[Bill-date], Table2.Price,
sum(IIf([Table2].[Bill-date] Between [table3].[from] And [table3].[to], [table3].[km],0)) AS KM
FROM Table2 
   LEFT JOIN Table3 
      ON Table2.Item 
      = Table3.IDbrand.Value
Group by Table2.NameClient, Table2.ID, Table2.Item, Table2.Vol, Table2.[Bill-date], Table2.Price
Order by Table2.NameClient, Table2.ID, Table2.Item;
 

seem like that, since my problem is far more harder than this, I split my model into many pieces to solve.
this is only 1 pieces that I solved ==