showing all records

[tselie115]

i have 2 tables: stocktable and projecttable

"stocktable" contains records listing materials for projects
"projecttable" will be filled by choosing a material from a drop down menu listing the materials from table "stocktable" and each record will have a qtyissued in "proejcttable".

the tables are linked by materialID primary field.

therefore the material not used yet for any project will not show in the table "projecttable"

im tryin to make a report that shows all items in "stocktable" and list the qty used from table " projecttable" if used already.

the problem is that the query is only showing the materials that are common means that are in "stocktable" and have been used in "projecttable" without showing the material that have no use in "projecttable"

how can i show all the items in the first table in the report?

Thank you

 

[WIS]

how can i show all the items in the first table in the report?

Go to the join line between the 2 tbls in the qry and rt click. Select option 2 or 3 (whichever will show all the records in the reqd tbl).

 

[tselie115]

i have already done dat in the query, edited the relationship between tables but still only the material used in the projecttable are showing and the ones not used are not in the report.
my query has grouping and sum to sum the quantities used for each material.
any other solution?

 

[neileg]

Your grouping and summing will be excluding the null entries. Try applying the grouping and summing in the report instead of the query.

However, you appear to have a many to many relationship, i.e. each project can use many materials and each material can be used in many projects. You can't model this in Access with just two tables, you need a third table as a junction table.

 

[tselie115]

thx man.
in fact, my original report had grouping and summing instead of using it in the query.
but when i came to the point to add the value of all the used materials, which will be a calculated field in the report, its always showing 0 since u cant add calculated fields from a report so i used summing as alternative in the query and the total value will be calculated in the report.
can i calculate the sum of the calculated fields in the report?
thx

 

[tselie115]

please note that while im tryin i removed all the grouping and summing from the query and excecuted it but still, the items that have to record in the second table are not showing at all.

 

[neileg]

When you checked that the join was a left join, did you check in the query? The join type here will override the join type in the relationship diagram.