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.