What's Causing This Query To Repeat Rows Please?

[seantnash]

Here's the query I have written:

SELECT

MemberContact.membershipno,

membercontact.firstname,

memberbasic.joindate,

WIRecords.WIID AS WInID,

wirecords.WIDate AS WInDate,

DSum("daysteps","stepcount","stepsdate between #" & Format([WinDate]-1,"mm/dd/yyyy") & "# And #" & Format([WinDate]-7, "mm/dd/yyyy") & "# and membershipno=" & stepcount.membershipno) AS WeekSteps

FROM stepcount

INNER JOIN (membercontact

INNER JOIN (memberbasic

INNER JOIN wirecords

ON memberbasic.membershipno = wirecords.membershipno)

ON membercontact.membershipno = wirecords.membershipno)

ON StepCount.membershipno = MemberBasic.membershipno;

The trouble is it's causing repeat rows for each MembershipNo's WInID based on the number of "daysteps" entries for that member.

Sample:

MembershipNo ... FirstName ... JoinDate...WInID ... WINDate ... WeekSteps

..........1......................Bob.............01/01/01......1...........06/01/01.........15000
..........1......................Bob.............01/01/01......1...........06/01/01.........15000
..........1......................Bob.............01/01/01......1...........06/01/01.........15000
..........1......................Bob.............01/01/01......1...........06/01/01.........15000
..........1......................Bob.............01/01/01......2...........01/02/01.........11000
..........1......................Bob.............01/01/01......2...........01/02/01.........11000
..........1......................Bob.............01/01/01......2...........01/02/01.........11000
..........1......................Bob.............01/01/01......2...........01/02/01.........11000

So, in the "StepCount" table there are 4 rows for Bob, representing 4 different days worth of steps:

StepsID ...... MembershipNo ..... StepsDate..... DaySteps
.....1............................1......................04/01/01..........5000
.....2............................1......................05/01/01.........10000
.....3............................1......................27/01/01..........2000
.....4............................1......................29/01/01..........9000

I've tried using the DISTINCT command and, whilst it does work, I know it's not really a good way to write a query.

I then tried the Max() function. This worked, right up until the DSum part where it started saying a subquery could not be used.

Soooo, how can I correct this query so that it works properly please?

 

[Ranman256]

When you join tables , the logic COULD create duplicates.
If you dont want this, set the query property = unique values.

(you souldnt use DSUM in a query. The QUERY is the Dsum. It slows down queries)

 

[seantnash]

I'm using Access 2016 and unique values doesn't appear in the properties list

I have noticed the query does run a tad slow but I'm new to Access so no idea how to rewrite it so that it's not a DSum unfortunately. I'm guessing it's a subquery that will be needed though?