where is the other records ?

[hvsummer]

Hi, I'm back here with new simple question, but since I'm noob at SQL, so I can't answer this myself.
assumption I have 2 table, Table 1 (10000 rows) is data raw having lot of infomation of bill, and table 2 (10 rows) content client-to-tracking infomation. Those 2 table have connect by ID.

when I use query, set relationship to left join
(all from table 2 and match from table 1),
the result show less record than number of table 2's record
(4 rows for example)

where is the rest record ? i don't expect left join having this result, I try to change relationship to innerjoin or right join have no different.

need to show up 10 rows in query to tracking... I can't miss even 1 row
I use some total (group by, expression, where...) on design.

code like this:

SELECT [Table 2].Ter, [Table 2].ID, [Table 2].name, [Table 2].voltarget,  Sum([Table 1].[volume]) AS [Total-MTD], IIf([Total-MTD]>=[voltarget],"yes”,"No") AS [Success (y/n)]
FROM [Table 2] LEFT JOIN [Table 1] ON [Table 2].ID = [Table 1].ID
WHERE ((([Table 1].[Item code]) Like "116*"))
GROUP BY [Table 2].Ter, [Table 2].ID, [Table 2].name, [Table 2].ID;
 

[jimatqsi]

You are testing a column from table 1 in your where clause. That criteria can never pass when the table one record is missing. Change

((([Table 1].[Item code]) Like "116*"))

to

(((NZ([Table 1].[Item code],"what you want when it is missing")) Like "116*"))

The what you want when it is missing must be like 116 if you want to include those records that don't have a matching ID.

Alternatively, you could try

((([Table 1].[Item code]) Like "116*") or ([Table 1].[Item code] Is Null))
 

Jim

[hvsummer]

it have better result, increase from 4 to 6 rows, but I need 10 rows result with left join :( still thank you Jim.

I draw it like this, TABLE1 down to 4 raw, table2 down to 6 raw to make it more simple, with code from Jim, it'll show from 2 to 3 raw, still missing something

in table 2 it have lot of item and Client, but I only need to track item 1 and those client from table1.

any idea ? help me plz, thank you.

[jimatqsi]

hvsummer, please help us help you. Show your code and do the investigation you can and tell us what your investigation reveals. You want 10 rows and got 6; so which 4 did you not get? What do those 4 have in common; what do the 6 have in common? The work is yours to do and folks here will help when you give them enough information to help with.

Again, show the code after you modified it.

Jim

[hvsummer]

hi Jim
I simplify code to post here, but I test with real big data, so I can't post what is the diffrent for you since my data is too big
but after I change code to your suggestion, like this

SELECT [Table 2].Ter, [Table 2].ID, [Table 2].name, [Table 2].voltarget,  Sum([Table 1].[volume]) AS [Total-MTD], IIf([Total-MTD]>=[voltarget],"yes”,"No") AS [Success (y/n)]
FROM [Table 2] LEFT JOIN [Table 1] ON [Table 2].ID = [Table 1].ID
WHERE (((NZ([Table 1].[Item code],"what you want when it is missing")) Like "116*"))
GROUP BY [Table 2].Ter, [Table 2].ID, [Table 2].name, [Table 2].ID;
 

result 600 raws (missing 400 raw). I said 10 (6 miss 4), but that I make it simple to understand..
with code that you want me to change "altenative"

SELECT [Table 2].Ter, [Table 2].ID, [Table 2].name, [Table 2].voltarget,  Sum([Table 1].[volume]) AS [Total-MTD], IIf([Total-MTD]>=[voltarget],"yes”,"No") AS [Success (y/n)]
FROM [Table 2] LEFT JOIN [Table 1] ON [Table 2].ID = [Table 1].ID
WHERE ((([Table 1].[Item code]) Like "116*") or ([Table 1].[Item code] Is Null))
GROUP BY [Table 2].Ter, [Table 2].ID, [Table 2].name, [Table 2].ID;
 

it showed up 500 rows (mean 5/10).
I have to track client from the list on table 1 (table 1 don't have item code) which buying item code start with 116 in table 2(only table 2 have item code)

I findout in the simple model that the row mising is the client which don't have vol in tracking-item in table 2 (mean that client did not buy tracking-item). but I still need to show him up (client which didn't have vol (total-MTD), or you can say "did not buy tracking-item"). any idea ?

[hvsummer]

ok, Resolve by myself...
I findout that to show all the client to track I don't use condition in the criterial anymore
code should be like this to work as I want:

SELECT Table1.NameClient, Table1.ID, Table1.Item, Table1.Vol, Sum(IIf([Table2].[item]=1,[table2].[vol],0)) AS MTD, IIf([MTD]>=[Table1].[Vol],"ok","not ok") AS ok
FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID
GROUP BY Table1.NameClient, Table1.ID, Table1.Item, Table1.Vol;
 

[jimatqsi]

Congratulations. The best solutions are the ones we can arrive at ourselves.

Jim