Missing Records

[brodaman]

My Query is not returning all my records am expecting 1079 records shown but am only seeing 656 records; I have 3 left table joins but wen I use date to filter I have some of my records missing. Please what may be the problem.

 

[arnelgp]

show us your SQL statement.

 

[brodaman]

select m.id,m.surname, m.firstname,m.middlename,Nz([c.contributions],0) as Contributions,Nz([l.loanrepayment],0) as loanrepayment ,Nz([I.item],0) as items,m.banks,accountno,c.Date,i.Date,l.Date,c.Detail,I.Detail,l.[Detail Transactions] from ((membership as m left join contributions as c on m.id=c.contributionid) left join items as i on m.id=i..memberid)left join loanrepayment as l on m.id =l.loanid where (((c.date)=[enter date] or(c.Date) is null) and ((i.date)=[enter date] or ((i.Date) is null) and((l.Date)=[enter date] or(l.Date) is null) and ((c.detail)=3 or(c.Detail) is null) and((i.detail=1 or (i.Detail) is null) and ((I.[Detail Transactions])="6" or (l.[Detail Transactions]) is null)); Note:I do d typing using my Android phone please. Thanks

 

[brodaman]

Good day,Please am waiting for your kind response to my SQL statement.

 

[arnelgp]

do you have a time stamp on your date fields, if so you can change the query to:

elect m.id,m.surname, m.firstname,m.middlename,Nz([c.contributions],0) as Contributions,Nz([l.loanrepayment],0) as loanrepayment ,Nz([I.item],0) as items,m.banks,accountno,c.Date,i.Date,l.Date,c.Det ail,I.Detail,l.[Detail Transactions] from ((membership as m left join contributions as c on m.id=c.contributionid) left join items as i on m.id=i.memberid)left join loanrepayment as l on m.id =l.loanid where ((Format(c.date,"ddmmyyyy")=Format([enter date],"ddmmyyyy") or IsNull(c.Date)) and (Format(i.date,"ddmmyyyy")=Format([enter date],"ddmmyyyy")) or Isnull(i.Date)) and (Format(l.Date,"ddmmyyyy")=Format([enter date],"ddmmyyyy") or IsNull(l.Date)) and ((c.detail)=3 or IsNull(c.Detail)) and ((i.detail=1 or IsNull(i.Detail)) and ((I.[Detail Transactions])="6" or IsNull(l.[Detail Transactions])));

 

[brodaman]

Thank you,but I don't have a time stamp,And one thing I noticed is each month query gives different number of records,like the month of February 2015 return 713,month of June return 652 while month of may return 681 records instead of them returning 1079 records.

 

[arnelgp]

you have set a criteria in your query that should return:

date in your field should be equal to your parameter date or
date in your field is blank.

 

[brodaman]

Base on your observation, I have now set my date to start and end date parameter so that it can capture all d event of that month. e.g Start date 6/1/2015 and end date 6/30/2015.And I still get same record return. I still look forward to your reply. Thanks.

 

[Minty]

You have your dates formatted to UK ddmmyyyy, but you are writing them as mm/dd/yyyy - this may be your problem?

 

[brodaman]

I am using d default format,I did not make any format of my own

 

[gemma-the-husky]

while testing, I would add (some of) the date values you are testing as additional columns in the query - so you can double check they are indeed being interpreted correctly.

In the UK, I tend to use format(adate,"long date") just in case

 

[brodaman]

Have used all d possible suggestions and am still hooked.Please I look forward to possible solution from the community.

 

[arnelgp]

Thank you,but I don't have a time stamp,And one thing I noticed is each month query gives different number of records,like the month of February 2015 return 713,month of June return 652 while month of may return 681 records instead of them returning 1079 records.

are you querying by monthly basis? your parameter should be changed to [enter date (m/yyyy)].

then we can just get all record by month and year:

elect m.id,m.surname, m.firstname,m.middlename,Nz([c.contributions],0) as Contributions,Nz([l.loanrepayment],0) as loanrepayment ,Nz([I.item],0) as items,m.banks,accountno,c.Date,i.Date,l.Date,c.Det ail,I.Detail,l.[Detail Transactions] from ((membership as m left join contributions as c on m.id=c.contributionid) left join items as i on m.id=i.memberid)left join loanrepayment as l on m.id =l.loanid where ((Format(c.date,"mmyyyy")=Format([enter date (m/yyyy)],"mmyyyy") or IsNull(c.Date)) and (Format(i.date,"mmyyyy")=Format([enter date (m/yyyy)],"mmyyyy")) or Isnull(i.Date)) and (Format(l.Date,"mmyyyy")=Format([enter date (m/yyyy)],"mmyyyy") or IsNull(l.Date)) and ((c.detail)=3 or IsNull(c.Detail)) and ((i.detail=1 or IsNull(i.Detail)) and ((I.[Detail Transactions])="6" or IsNull(l.[Detail Transactions])));

 

[brodaman]

Have effect d change but same result.

 

[brodaman]

I separated d query; Membership table and Contributions table returned all d records. 2.Membership table and loan table return 1045 records 34 items missing.
3.Membership table and Item table returns 847 records 232 missing. May b these information can help please.

 

[Minty]

Okay - what is causing the 34 items to be missing from the first query - there must be a data explanation.?