Query to produce comparison between Required Material vs Purchased Reqst, PO, Deliv,

[jones]

Hi to all,

Please can anyone help me with my query probelem.

I'm making a dbase 2007 for my monitoring in the projects.

I Have four main tables: 1. Project Code 2. Material Code 3. Material Supply Plan 4. Purchased Request
and the rest are related to Purchased Request table that is why it doesn't give much headache.

My problem is that i'm trying to make a query based on these four table that when i click this query it gives me the comparison or group of material list with project wise that showing
the list of materials required quantity vs the purchased request quantity as project wise.



As in my attached i do the query and it gives me what i want but the problem is it shows the materials by project code not neccessary for the specific project which gives me a lot of blank rows and null value.

Is there a way that it shows in a query only what is required and what is requested.

Please see on my attached. Million thanks you in advance.

[June7]

Which query is giving you those results?

If you want the query results to show only one project, then apply filter to the query.

[jones]

Thank for your help Sir.
So far i've done two query which also different table link relation because i try to show every project code with materials code/description with quantity value. But whatever project code with materials which don't have quantity should not appear in the query.
As in my attached you will see that there are project code & materials that don't have quanity amount (should remove from query). So that when i filter per project code, only have quantity will appear.

These two query you will find in my sample has 4 different tables link relation. Please Sir, if possible to reconstruct the tables relation so that the red records with no data will not appear, I really appreciated.


1. qry MSP vs PR:POVR 3
2. qry: Material SP vs PR vs PO vs SRR

Project Code		Item Code	Description	Reqd Qty	PR Qty	PO Qty	DV Qty	RV Qty
70875		01-03-0001	Adesobit 1550 (1 x 15m/roll) A
70875		01-03-0002	Adesobit 1550 (1 x 15m/roll) 1.5mm Thick B	50
70875		01-03-0003	Adesobit 1550 (1 x 15m/roll) 1.5mm Thick C		38		350	500
70875		01-03-0004	Adesobit 1550 (1 x 15m/roll) 1.5mm Thick D
70886		01-03-0001	Adesobit 1550 (1 x 15m/roll) A	45.5	150	80	13	40
70886		01-03-0002	Adesobit 1550 (1 x 15m/roll) 1.5mm Thick B		150	60
70886		01-03-0003	Adesobit 1550 (1 x 15m/roll) 1.5mm Thick C		25	100
70886		01-03-0004	Adesobit 1550 (1 x 15m/roll) 1.5mm Thick D	400

Project Code	Item Code	Description	Reqd Qty	PR Qty	PO Qty	DV Qty	RV Qty
70875	01-03-0004	Adesobit 1550 (1 x 15m/roll) 1.5mm Thick D
70875	01-03-0001	Adesobit 1550 (1 x 15m/roll) A

These two records don't have quantity amount at all, should not appear in a query.

Project Code		Item Code	Description	Reqd Qty	PR Qty	PO Qty	DV Qty	RV Qty
70875		01-03-0001	Adesobit 1550 (1 x 15m/roll) A
70875		01-03-0004	Adesobit 1550 (1 x 15m/roll) 1.5mm Thick D

[June7]

See if this gets you what you want:

Under each of the quantity fields use criteria: Not Is Null

Put each on a separate criteria row - will look like stair steps. This will invoke the OR operator.

[jones]

Hi,

I think is not possible because it shows only those fields who has value, the rest is completely hide.

Project Code	Project Title	Item Code	Description	Reqd Qty	PR Qty	PO Qty	DV Qty	RV Qty
70886		01-03-0001	Adesobit 1550 (1 x 15m/roll) A	45.5	150	80	13	40

[June7]

Then you did not put each criteria on a separate row, they will appear to 'stair step'. It will work, I already tested.

[jones]

Dear Mr. Super Moderator,

I just want to super duper tera mega thank you, succcess. It's very very nice that you were there.

Thank you once again.