Problem with inner joins

[ChrisSedgwick]

Good afternoon,

I'm really struggling with my query and I hope I can explain well enough for you to be able to help me.

I've been asked to product a report that shows our 'Material Allowance' was for all Jobs where, the Job is complete. (We have a field called '% Done' and when it shows 100, this indicates that the job is complete).

I've then got to find out what was the Total Spent on Materials, again for each Job that is complete.

Here's my problem, and this is where I;m going to find it hard to explain. The fields that I require in my query are spread our over different tables. This works for many reasons, however it seems not now I want to produce a report.

Included in my query are my tables:

Orders
Customer
Jobs
Product
Materials Requested
Materials/Orders
Phase

Here's what fields I need from which tables:

Job No from Jobs
Order No from Orders
Supplier from Orders
Total from Materials/Orders
Project Name from Jobs
Customer from Customer
% Done from Product

Everything works fine up until I try to insert the % Done fields, to then create a condition that only shows me the records that show as 100%. The query will not run because of 'Inner Joins' between 'Phase' and 'Product'. However, when I change the joins, the query returns a load of duplicate records.

I'm not sure I've explained as clearly as you'd like, but it's difficult to put it into writting without getting too compliacted. On the surface, is there someone that has an idea about my issue and how I can begin to solve it?

I do appreciate it's not the best explaination, however anything you need, just ask and I 'll give you the answers.

Many thanks,
Chris.

 

[jdraw]

It would be helpful to readers , if you could:
-post a jpg of your tables and relationships, or
-post a copy of your database ( remove anything confidential first) and supply
only enough records to illustrate the issue/problem.

Good luck.

 

[ChrisSedgwick]

Hi,

I did think the explination was a little sketchy! I do apologie. I've attached a screenshot showing the query and how it set up and how I want it to work.

I've also include the SQL Statement below...

SELECT [Materials Requested].[Materials Requested ID], [Materials Requested].[Job No], Orders.[Order No], Orders.Supplier, [Materials/Orders].Total, Jobs.[Project Name], Customer.Customer, Product.[% Done]
FROM ((Orders INNER JOIN (((Customer RIGHT JOIN Jobs ON Customer.[Customer ID] = Jobs.[Customer ID]) INNER JOIN [Materials Requested] ON Jobs.[Job No] = [Materials Requested].[Job No]) INNER JOIN [Materials/Orders] ON [Materials Requested].[Materials Requested ID] = [Materials/Orders].[Materials Requested ID]) ON Orders.[Orders ID] = [Materials/Orders].[Orders ID]) LEFT JOIN Phase ON Jobs.[Job ID] = Phase.[Job ID]) INNER JOIN Product ON Phase.[Phase ID] = Product.[Phase ID]
WHERE (((Product.[% Done])=100));

When I run the query it displays duplicate records, I've also attached a screenshot to show this.

Does this help any further?

Chris.

 

[namliam]

For starters you could clean up that relationship window "a little"

Second your presentation of your SQL can use some cleaning up. Atleast you used the code tags

SELECT [Materials Requested].[Materials Requested ID]
     , [Materials Requested].[Job No]
     , Orders.[Order No]
     , Orders.Supplier
     , [Materials/Orders].Total
     , Jobs.[Project Name]
     , Customer.Customer
     , Product.[% Done]
FROM       ((Orders 
INNER JOIN  (((Customer 
RIGHT JOIN     Jobs ON Customer.[Customer ID] = Jobs.[Customer ID]) 
INNER JOIN [Materials Requested] ON Jobs.[Job No] = [Materials Requested].[Job No]) 
INNER JOIN [Materials/Orders] ON [Materials Requested].[Materials Requested ID] = [Materials/Orders].[Materials Requested ID]
                             ) ON Orders.[Orders ID] = [Materials/Orders].[Orders ID]) 
LEFT  JOIN Phase     ON Jobs.[Job ID] = Phase.[Job ID]) 
INNER JOIN Product   ON Phase.[Phase ID] = Product.[Phase ID]
WHERE (((Product.[% Done])=100));

Third, using spaces or other special characters in your column names is a bad idea.

The way I read it
1 job can have many phases
1 phase can have many products

Which would make it only logical that your JOB_NO will duplicate per each product

Why would changing the join on phase to Product change the outcome when you have %Done = 100%?

 

[ChrisSedgwick]

Hi,

I'm working on a company database that was already in place before I came here, otherwise there would have been no way I would have used special characters etc. I can only work with what I have in front of me, which does seem a little difficult.

I've tried the code you sent and it says "Join Type Not Supported"

It's difficult in the sense that I know the result i'm trying to get, however finding it tough to explain. I couldn't provide a copy of the database due to the size.

 

[jdraw]

I agree with namliam - the relationships window could be cleaned a little.

When I look at your data there seems to be duplication in the first 4 entries (and others).
I think there may be some table design issues.

eg Jobs with phases/phasing, multiple dates

Could be a sign of hidden entities.

A clear description of your business, in simple business terms, may help readers.

 

[jdraw]

Did you do a compact and repair, then zip?

Also a subset of data would be sufficient --- only a few companies, or jobs, etc.
Just enough to show the issue with some real data.

 

[ChrisSedgwick]

I can totally agree that there are some major design issues, As I mentioned I'm not an expert on this and it was a database that was designed long before I joined the company. If it can't be done, that's fine I'll look at an alternative, I just thought it'd be easier to build a report where the data is actually stored.

As a business, we manufacture, supply and install washrooms, cubicles, vanity units healthcare furniture commercially. The database that we use, stores all the jobs, quotes, stock levels, purchasing and more. It's design is poor, I know but to set up a new database and migrate all the current database wouldn't be viable at present.

I can try to attached c opy of the database, however would it matter that it's a split database? As the backend sits on our server.

Thanks,
Chris.

 

[ChrisSedgwick]

This is what it looks like when I run it without using the condition 100% done. It gives me all the jobs and the total cost of materials. Which is great, however I want to filter out jobs that are completed ONLY. Which is the reason for trying to use the 100% field.

 

[namliam]

Just make a copy of the "real" database, remove most records leave just enough (or enter some mockup data if you want) to show the issue.

The sql I posted was just reformatted (mostly enters here and there) to make it actually somewhat readable.

Will assume you want Query2 but without the duplicates.
The problem is actually in your understanding of the database and its relations....
I -THINK- what you want is NOT the jobs that have a product that is 100%.

You want jobs where either for one phase or for all phases ALL products are at 100%.

Your current setup kindoff looks like

Jobno 
1

Phase
PhaseID phase Jobno
1       XYZ   1
2       ZZZ   1

Product
ProductID PhaseID Perc
1         1       50
2         1       100
3         2       100
4         2       100
5         2       100

Here it will show products 2-5 and duplicate jobno 1 four times

where in actuallity I expect you only want to see Phase 2 or NOTHING at all as the job isnt dont in its completeness.

 

[ChrisSedgwick]

namliam,

This is exaclty what I want - it's just getting to that stage. I'm sorry that I'm not educated well enough on this. This is my reason for coming to the forum to try to understand and learn this kinds of issues.

I'll try to attach a copy of the database for you to take a look.

Chris.

 

[namliam]

I agree with namliam - the relationships window could be cleaned a little.

Referencing your query5.png as far as cleaning goes...
One would make sure no lines go behind tables and/or preferably dont cross eachother.

One simple move of the Order table to the far right would do just that and make things that much more "pleasing" to the eye.
For query5 it doesnt really make such a huge difference, however apply the same rulez to Query1 and it makes the world of difference to understanding your structure.
Particular for someone that looks at this the first time.

 

[namliam]

namliam,

This is exaclty what I want - it's just getting to that stage.

I gave two options, answering "Yes" doesnt really help.

How you have to do this is by making a query seperately of your Phase and Product table first to see if a phase is complete or not.
Then join that query back into this one.

Something along the lines of

Select JobNo
     , PhaseID
     , Sum(IIF(%Done=100%,1,0)) IsDone
     , Sum(IIF(%Done=100%,0,1)) InProgress
From Phase
Join product on phase.phaseid = product.phase_id
Group by Jobno,phaseID

You can then either add to this query

Having Sum(IIF(%Done=100%,0,1)) = 0

Or after joining this query back to your Original one add
InProgress = 0

Offcourse you need to fix the column names (cba with [] etc) and you probably need to replace the , in the IIF by ;
Us Dutch folk are unique in that (almost)

If you want "Job complete" only, then simply remove the phaseid from the query

 

[namliam]

You can also use a query like:

Select JobNo
     , PhaseID
     , avg(%Done) PercDone
From Phase
Join product on phase.phaseid = product.phase_id
Group by Jobno,phaseID

Which will obviously return something other than 100% if not all is 100%

 

[ChrisSedgwick]

I've attached a copy of the database for your reference.

The query is being set up in a front end copy though, will this matter?

 

[namliam]

No difference if its FE or BE, have you tried working the query(s) as I suggested?

 

[ChrisSedgwick]

Giving me an error?

Did you manage to get access to the database I attached in my previous post?

 

[namliam]

I gave two options, answering "Yes" doesnt really help.

How you have to do this is by making a query seperately of your Phase and Product table first to see if a phase is complete or not.
Then join that query back into this one.

Something along the lines of

Select JobNo
     , PhaseID
     , Sum(IIF(%Done=100%,1,0)) IsDone
     , Sum(IIF(%Done=100%,0,1)) InProgress
From Phase
Join product on phase.phaseid = product.phase_id
Group by Jobno,phaseID

You can then either add to this query

Having Sum(IIF(%Done=100%,0,1)) = 0

Or after joining this query back to your Original one add
InProgress = 0

Offcourse you need to fix the column names (cba with [] etc) and you probably need to replace the , in the IIF by ;
Us Dutch folk are unique in that (almost)
If you want "Job complete" only, then simply remove the phaseid from the query

Also you may need to adjust the table names and use INNER JOIN instead of JOIN....

Or "simply" make the query in the designer....

P.S. I didnt yet try the DB.... think the query should get you started regardless

 

[ChrisSedgwick]

Ok, so I've done it in designer and go it to show all the phases that are complete.

How do I now join this on to my original query?

SELECT Phase.[Phase ID], Product.[% Done]
FROM Phase INNER JOIN Product ON Phase.[Phase ID] = Product.[Phase ID]
WHERE (((Product.[% Done])=100));

 

[ChrisSedgwick]

Apologies, this is what it looks like now. I've read your previous message back...

SELECT Phase.[Phase ID], Product.[% Done], (IIf([Product].[% Done]=100,1,0)) AS Done
FROM Phase INNER JOIN Product ON Phase.[Phase ID] = Product.[Phase ID];

Is this the point where I need to join it back to my original query?