Advanced Query with Inner Joins

A

aondrusek

New member
Local time
Today, 14:30
Joined
Jul 7, 2014
Messages
9
Hello All! This is my first form post and I'll admit that i've been lurking and learned nearly everything I know about access VBA from this site.

I am setting up a database that keeps track of parts, breakdowns, and maintenance for machines at our factory (CNH-Saskatoon).

We use 4 triangles filled into a square to visually represent activities/part/month. I originally created a seperate table to represent a filename to change a picture to. Data was added to this via SQL statements and was only joined via partID. I would like to create a query that generates this data so that when information is changed or deleted it updates the triangles appropriatly.

When I use the following SQL statement it creates a new line for each months expression. Ideally I just want 1 line for each part containing all the data needed. I was thinking of checking if the next line was the same and then updating and deleting the rows accordingly. But.... This seems like the wrong approach; any other ideas??

SELECT tblGroups.AssetID, tblGroups.GroupID, tblSubs.SubID, tblParts.PartID, tblParts.PartName, tblParts.PartClass, IIf(Month([PMdueDate])=1,1,0) & IIf(Month([EWODate])=1,1,0) & IIf(Month([PMcompDate])=1,1,0) & IIf(Month([NoteDate])=1,1,0) AS January, IIf(Month([PMdueDate])=2,1,0) & IIf(Month([EWODate])=2,1,0) & IIf(Month([PMcompDate])=2,1,0) & IIf(Month([NoteDate])=2,1,0) AS February, IIf(Month([PMdueDate])=3,1,0) & IIf(Month([EWODate])=3,1,0) & IIf(Month([PMcompDate])=3,1,0) & IIf(Month([NoteDate])=3,1,0) AS March, IIf(Month([PMdueDate])=4,1,0) & IIf(Month([EWODate])=4,1,0) & IIf(Month([PMcompDate])=4,1,0) & IIf(Month([NoteDate])=4,1,0) AS April, IIf(Month([PMdueDate])=5,1,0) & IIf(Month([EWODate])=5,1,0) & IIf(Month([PMcompDate])=5,1,0) & IIf(Month([NoteDate])=5,1,0) AS May, IIf(Month([PMdueDate])=6,1,0) & IIf(Month([EWODate])=6,1,0) & IIf(Month([PMcompDate])=6,1,0) & IIf(Month([NoteDate])=6,1,0) AS June, IIf(Month([PMdueDate])=7,1,0) & IIf(Month([EWODate])=7,1,0) & IIf(Month([PMcompDate])=7,1,0) & IIf(Month([NoteDate])=7,1,0) AS July, IIf(Month([PMdueDate])=8,1,0) & IIf(Month([EWODate])=8,1,0) & IIf(Month([PMcompDate])=8,1,0) & IIf(Month([NoteDate])=8,1,0) AS August, IIf(Month([PMdueDate])=9,1,0) & IIf(Month([EWODate])=9,1,0) & IIf(Month([PMcompDate])=9,1,0) & IIf(Month([NoteDate])=9,1,0) AS September, IIf(Month([PMdueDate])=10,1,0) & IIf(Month([EWODate])=10,1,0) & IIf(Month([PMcompDate])=10,1,0) & IIf(Month([NoteDate])=10,1,0) AS October, IIf(Month([PMdueDate])=11,1,0) & IIf(Month([EWODate])=11,1,0) & IIf(Month([PMcompDate])=11,1,0) & IIf(Month([NoteDate])=11,1,0) AS November, IIf(Month([PMdueDate])=12,1,0) & IIf(Month([EWODate])=12,1,0) & IIf(Month([PMcompDate])=12,1,0) & IIf(Month([NoteDate])=12,1,0) AS December
FROM (tblGroups INNER JOIN tblSubs ON tblGroups.GroupID = tblSubs.GroupID) INNER JOIN ((tblSMPs LEFT JOIN tblPMs ON tblSMPs.SMPID = tblPMs.SMPID) RIGHT JOIN (((tblParts LEFT JOIN tblEWOs ON tblParts.PartID = tblEWOs.PartID) LEFT JOIN tblNotes ON tblParts.PartID = tblNotes.PartID) LEFT JOIN tblSMPparts ON tblParts.PartID = tblSMPparts.PartID) ON tblSMPs.SMPID = tblSMPparts.SMPID) ON tblSubs.SubID = tblParts.SubID
WHERE (((tblGroups.AssetID)=[TempVars]![tvAssetID]) AND ((tblGroups.GroupID) Like [TempVars]![tvGroupID]) AND ((tblSubs.SubID) Like [TempVars]![tvSubID]));

Outputs as:
PartName January February March April
Part1 0000 0100 0000 0000
Part1 0000 0000 0100 0000
Part1 0000 0000 0000 0100

(When there are multiple dates for same part)


What I want:
PartName January February March April
Part1 0000 0100 0100 0100

Thanks so much in advance!
 
Welcome to the forum.

So basically what you're trying to do is condense the Parts?

By the way in place of your IIF(), here are two shorter equivalents:
Code: 
Abs(Month([PMcompDate])=1)

(Month([PMcompDate])=1)*-1
 
Thank you! Here is the final result I am working towards (In a different way)

Untitled.jpg

I essentially just need to know if there is a Month(PMdueDate) on that date so I can change the square image to Triangle0100.png or Triangle0000.png
the problem is if I join the tables LEFT, RIGHT, or CENTER then it creates a new row. This causes this to happen:

Untitled1.jpg

I just want one row for each part.
 
Do the 4 triangular quadrants mean anything? I mean do each of those triangles in the square get filled up as well?
 
I've just looked at your chart again and I think those are not quadrants. You just want to check that there is at least one occurrence of 1 in that record for each month and paint it yellow accordingly.

If this is the case, then you don't need all those IIFs. For Jan:
Code: 
Abs((Month([PMdueDate])=1) + (Month([EWODate])=1) + (Month([PMcompDate])=1) + (Month([NoteDate])=1))
Then check if it's greater than 0.
 
Yes, the right is represented if There is a [EWOdate] for that part the bottom is represented if there is a [PMdueDate] and top if there is a [PMcompDate] each of these has there own picture code for example if there were all three it would be 1110, if blank it would be 0000. I have attached the file as there is some cool functions that someone may need and stumble on.

This one is what I had before I decided to remove stand alone 'triangle table' It is functional untill an item needs to be changed;

This one is the current update in which im trying to remove the 'triangle table' entirely;

Nvm.. Wont let me upload here is the code that changes pictures to help get a better understanding.

Code: 
    For row = 0 To 5
        tCol = 6
        If IsNull(listTriangles.Column(0, tRow)) Then
           TF = False
            Me.Controls("imgPart" & row).visible = TF
            Me.Controls("txtClass" & row).visible = TF
            Me.Controls("txtName" & row).visible = TF
            For col = 0 To 11
                Me.Controls(row & "-" & col).visible = TF
            Next col
        Else
            TF = True
            With Me.Controls("imgPart" & row)
                .visible = TF
                .Picture = dbRoot & "PartPictures\" & listTriangles.Column(3, tRow) & "p.png"
            End With
            With Me.Controls("txtClass" & row)
                .visible = TF
                .Value = listTriangles.Column(5, tRow)
            End With
            With Me.Controls("txtName" & row)
                .visible = TF
                .Value = listTriangles.Column(4, tRow)
            End With
            For col = 0 To 11
                With Me.Controls(row & "-" & col)
                    .visible = TF
                    .Picture = dbRoot & "AppPictures\Triangle" & listTriangles.Column(tCol, tRow) & ".png"
                End With
                tCol = tCol + 1
            Next col
        End If
        tRow = tRow + 1
        DoCmd.RunSQL "UPDATE tblTempLedger SET PartID=" & listTriangles.Column(3, tRow) & " WHERE Row=" & row & ";"
    Next row
 
I see, so they are actually 4 quadrants. Your image doesn't depict that.

I can think of a few ways but I'm thinking of the most efficient way of doing it so give me some time. In the meantime put some criteria to get rid of those rows (not fields but entire records) that are just zeros, i.e. we want to remove:
Part1 0000 0000 0000 0000

One of three ways:
Code: 
CInt(January) + CInt(February) + [COLOR="Red"]...etc..[/COLOR]. + CInt(December)
CLng(January & February & [COLOR="Red"]...etc...[/COLOR] & December)
Instr(1, January & February & [COLOR="blue"]...etc...[/COLOR] & December, "1")
Under the criteria put > 0
 
Again, Thank you so much for the fast replies! I actually need the rows with 0000 As they will set the picture to Triangle0000.png which is a blank square thus refreshing it if there was previously an item. I think this is mainly a problem with joins. I simplified it out to a simple SELECT '*' FROM tbl WHERE Month(EWOdate) = 3 but it returns 6 of the same items since it is joned to 6 records from another table. I need to somehow reduce this to the one by changing my joins.
 
Yes I know that you need it but what I'm about to show you will require a lot of computational power that's why I want you to get rid of the anomalies. The less records the faster it will compute. You can do a LEFT JOIN to the final query later and check for Null.
 
Im thinking it may just be easiest to have a seperate table for the triangle data. I will just have to make sure I add SQL code to UPDATE the "tblTriangles" every time any data in those 3 tables are changed. Trying to do a query is proboly the right way but access will not let me since it thinks I am missing important data when all I need to know is if certain data exists. So it could be an access limitation if anything...
 
aondrusek said:
I think this is mainly a problem with joins. I simplified it out to a simple SELECT '*' FROM tbl WHERE Month(EWOdate) = 3 but it returns 6 of the same items since it is joned to 6 records from another table. I need to somehow reduce this to the one by changing my joins.
Click to expand...
A join won't cut it in this case because you're dealing with row wise checks as well.
 
Let's take January as an example. For each quadrant for January, all you want to do is calculate the Max() of it. So get the Max of this field:
Code: 
Abs(Month([PMcompDate])=1)
This would obviously be Grouped By PartName. Repeat the same process for each quadrant for each month. Painful I know.

That will condense your query and give you what you want.
 
This is the dbStructure BTW; I need to check the if a month(date) exisits within tblEWOs.EWOdate, tblPMs.PMdueDate, tblPMs.PMcompDate, and tblNotes.NoteDate linked to a single PartID. (I think you understand this now, but it seems to get worded a little cleaner evertime I try to explain it)

Untitled3.png
 
vbaInet said:
Let's take January as an example. For each quadrant for January, all you want to do is calculate the Max() of it. So get the Max of this field:
Code: 
Abs(Month([PMcompDate])=1)
This would obviously be Grouped By PartName. Repeat the same process for each quadrant for each month. Painful I know.

That will condense your query and give you what you want.
Click to expand...

That will work. From my understanding now I need to make a seperate query for each month though and use DISTINCT on the expression[Max()] Column to prevent dulpicates, Then use a SQL command to create a table of picture filename strings based on the items in that query :S. Believe it or not it is simpler than the route I was attempting before haha!

Thank you so much for your Help! I will post finished result sometime this week :).
 
Almost there but not quite.

1. Use the query in your first post but without all the IIFs or Abs(). Remove all those.
2. Filter your query to remove all those rows (entire rows) that have 000000000000000. Remember one of those three methods I showed in one my posts (using CInt() or Instr())?
3. Create a new query based on the query in step 2 and get the Max() of each quadrant for each month Grouped By PartNumber. You can call the Max quadrants Ja1, Ja2, Ja3, Ja4, Fe1,... etc.
4. Create another query based on step 3 and merge Ja1 & Ja2 & Ja3 & Ja4 to form January. Do the same for February and so on.
5. Create yet another based on step 4 and do a LEFT JOIN so that the zero records you excuded from step 2 will now be Null.
 
Take it step by step and it will make sense. Don't try and do it all in one.
 
Thank you, much much clearer in the step by step approach. Time to grab a coffee and get clackin away!
 
You must log in or register to reply here.

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom