Query : Best Way.

[SRSA]

Experts,


I have 2 queries that return material issued and material returned for all job numbers.

Issued Query
Job No Issued
1 20
1 10
2 13

Returned Query
Job No Returned
1 6

I want a query to return the difference and hence the material Used

Used Query
Job No Issued
1 24
2 13

what I need is an overview of how to do this, I'm struggling I think on 2 main issues
The job number can appear multiple times in both queries
The job number may never appear in the Returned query i.e. 0 material returned.

Much appreciate any help....

[SRSA]

formating didn't turn out too well, below should be better.
Issued Query
Job No_____Issued
1_________20
1_________10
2_________13

Returned Query
Job No____Returned
1________6


Used Query
Job No____Used
1_________24
2_________13

[Rixxe]

If i understand correctly you just want to find the difference between Issued query and Used query?

Research COUNT() and SUM(), this should give you a basic idea of how to go about the problem.

Also look into Group By, (but it might not be needed)

[SRSA]

Correct, I've had a bit of success using SUM, my main issue now seems to be that only some jobs appear in the Returned Query, i.e. nothing was returned. In this case i need the query to assume zero was returned. Please see attached, this shows my query working but only where there is issued and returned values, there should be many more where there was only issued material.

I hope I’ve explained clearly.

[Rixxe]

Are you saying you want the query to work, even when ONE of the Selected fields is zero/null?

In that case you need to allow for NULL values, if those fields are int/number fields, you should be able to allow NULL/Zero values.

[SRSA]

Not exactly, The value will not be NULL or Zero, it simply won't exist in the returns query i.e.

Issued Query
Job1:600
Job2:230

Returned Query
Job1:100

Used Query
Job1:500
Job2:230

I'm I making this more complicated than it needs....

Thanks again....

[Rixxe]

So you want to display Job No. even if there is no calculation?
Or do you want to display something which currently isnt displayed such as Null or Zero values? Or Calculations?

Make sure the final question is simple to understand please, struggling to understand the exact problem.

Cheers

[SRSA]

Issued Query - (currently working as expected) gives me the total material issued per job. Note : every job will be here.

Returns Query - (currently working as expected) give me the total material returned per job. Note : not every job will be here.

Used Query - (not working as required) this should give me the difference if the job number exists in both tables (Issued - Returned). If the job number does not exist in the Returns it should give me the Issued value (i.e. all material issued was used).

I want to know how much material was used per job, if material was returned i need to minus it from what was issued else I take the issued value.

[Rixxe]

In that case, my first thought is using an 'IF ELSE' structure.


E.g.
IF exists
(
(Used Query as Normal)
)
else
(
(Used Query When Returns Job No is NULL)
);

[SRSA]

After some more googling it seems i require join operator,

simples..........
http://www.techonthenet.com/access/queries/joins2.php