How to make query from 3 tables that providing all data (including empty record)

[calaxan]

Hi All,
Please some help.
i have some difficultie to make a query to providing all data including zero record. I hope some of you can find the solution.


I have table like these:


<code>
tblData
Grade OB_Type Tot_Prod
GR-1 Full 100
GR-2 HALF 140
GR-1 HALF 50
GR-3 FULL 20




tblOB
OB_Type OB_Qty OB_Supply
FULL 1000 Comp-1
HALF 500 Comp-1




tblGrade
Grade Code
GR-1 X001
GR-2 X002
GR-3 X003
GR-4 X004


</code>


How to make a Query, so i will get data like this:


<code>
Grade OB_Type Weight
GR-1 Full 100
GR-1 HALF 50
GR-2 FULL 0
GR-2 HALF 140
GR-3 Full 20
GR-3 HALF 0
GR-4 FULL 0
GR-4 HALF 0
</code>


Thanks for the help.

[Bob Fitz]

To show Zeros use a calculated field and the Nz() function:

Wht:Nz(Weight,0)

[calaxan]

Hi...
i've try the all the way i know...but still not meet my expectation. See the pict attached.

[aytee111]

Begin by removing the Group By and make it a straight select query. Then check that each line in the query is (a) present and (b) showing the correct data (the OB Type is blank in the incorrect query, yet is present in the expected one?). Your pictures aren't showing what the value is in the Ranking column, is this causing a problem?

[calaxan]

Thanks for your reply.
Sorry if my question and data provided is difficult to understand.

Ranking column is just to short the Grade record. I want to short like this : LS42**..S / LS42**..H /LS42**..R / LS42**..L. I'm using ranking because my record is not alphabetic. And i dont want to show Ranking number.

I'm using Group By, because i want to group from many record of the grade of :
"LS42**..S" in "FULL OCTABIN" how many qty
"LS42**..S" in "HALF OCTABIN" how many qty
...
...
etc.

if my Table or Query is difficult or even impossible to make as per I expected, i'd like to ask among of you if any better solution to get as per expected.

Many Thanks,

[June7]

Post query SQL statement. If you want 2 records for every grade, then there must be 2 records for each grade in tblData2018.

How many OB types are there? Your expected results show only 2.

Should be linking on primary and foreign key fields. There are no keys defined in your tables.

What purpose is OBCode field if it is not designated as primary key?

Grade should be primary key in tblGradeRank (or use Ranking as key and save into tblData2018). And designate OBType as primary key in tblOB - unless OBCode should be key and save its value as foreign key in tblData2018.

Why does the table have year in name? I hope you do not have a table for each year.

Forum CODE tags use [ ] not < >.

[calaxan]

Thanks June7 for the input, i will fix the data structure, hope will get easier to solve the problem.