Consolidate information

shawnf · Jun 1, 2016

I have a table like this
PN COUNT DATE
ABC 1/18/2016
ABC 1/20/2016
ABC 5/5/2016
XYZ 1/12/2016
XYZ 1/18/2016
XYZ 5/10/2016
HIJ 1/8/2016
HIJ 1/12/2016

All PNs have to be counted 4 times a year
the issue I have is there is a criteria that if a PN is counted within 62 days of each count that it does not count as an actual count.

I need to have this information sorted to one of these two options and I cannot figure it out. Notice that each of the second count dates from above are not included in the output as there was not 62 days that have lapsed since the first count

PN First Count Second Count Third Count Forth Count
ABC 1/18/2016 5/5/2016
XYZ 1/12/2016 5/10/2016
HIJ 1/8/2016

OR

PN Times Counted
ABC 2
XYZ 2
HIJ 1

Any help here would be greatly appreciated.

thank you

plog · Jun 1, 2016

First, you shouldn't have spaces in field or table names. Second, you didn't provide a table name. So, I used [YourTableNameHere] for the name of your table and I used [CountDate] for the name of [COUNT DATE].

To accomplish what you want, you need 2 subqueries. Here's the SQL for the first:

Code:

SELECT YourTableNameHere.PN, YourTableNameHere.CountDate, (SELECT MIN(CountDate) FROM YourTableNameHere As Sub1 WHERE Sub1.PN=YourTableNameHere.PN AND DateDiff("d", YourTableNameHere.CountDate, Sub1.CountDate)>=62) AS NextCountDate
FROM YourTableNameHere;

It determines the next record for each record in your table. Name the above query [TimesCounted_sub1]. Then use this SQL for the second sub query:

Code:

SELECT TimesCounted_sub1.PN, Min(TimesCounted_sub1_1.NextCountDate) AS Date3, Max(IIf(IsNull([TimesCounted_sub1].[NextCountDate]),1,2)) AS Count2

FROM TimesCounted_sub1 LEFT JOIN TimesCounted_sub1 AS TimesCounted_sub1_1 ON (TimesCounted_sub1.NextCountDate = TimesCounted_sub1_1.CountDate) AND (TimesCounted_sub1.PN = TimesCounted_sub1_1.PN)
GROUP BY TimesCounted_sub1.PN;

It looks for the 1st 2 valid counts of a PN and sets it up so that you can search for the 3rd and 4th. Name the above sql [TimesCounted_sub2]. Finally, the below query will produce your results:

Code:

SELECT TimesCounted_sub2.PN, Max([Count2]+IIf(IsNull([CountDate]),0,1)+IIf(IsNull([nextcountdate]),0,1)) AS TimesCounted

FROM TimesCounted_sub2 LEFT JOIN TimesCounted_sub1 ON (TimesCounted_sub2.PN = TimesCounted_sub1.PN) AND (TimesCounted_sub2.[Date3] = TimesCounted_sub1.CountDate)
GROUP BY TimesCounted_sub2.PN;

Let me know if it doesn't, and if so, provide sample data that it fails on.

shawnf · Jun 1, 2016

Thank you so much, I will try this out. And I do not have spaces in my headers. this was a quick model of what I have in the actual database. I will let you know if this works!

shawnf · Jun 1, 2016

Running Sub2 asks for Parameter. "Enter Parameter Value, TimesCounted_sub1_1.CountDate"

running final query gives error "The specified field 'TimesCounted_sub1.CountDate' could refer to more than one table listed in the FROM clause of your SQL statement

plog · Jun 2, 2016

Running Sub2 asks for Parameter. "Enter Parameter Value, TimesCounted_sub1_1.CountDate"

It can't find that specific field. Did you use the names I used for the fields and queries?

shawnf · Jun 6, 2016

I have changed it to include my field names.
I have attached images of the SQL and the final error I am getting.
I appreciate the help so far. If I could get this to resolve that would be amazing.

plog · Jun 6, 2016

You need to start with the first query and see if they run on their own. When you encounter one that doesn't you need to make sure both the field and data source name is avialable to that query.

Screenshots of the queries in Design view would help more.

CJ_London · Jun 6, 2016

duplicate post here

http://www.accessforums.net/showthread.php?t=60111

shawn - please read this link

http://www.excelguru.ca/content.php?184

arnelgp · Jun 6, 2016

another overkill!
paste the code in a module.
call it within your query:

select [pn],
fnPNCount("table1",[pn], 1) As [First Count],
fnPNCount("table1",[pn], 2) As [Second Count],
fnPNCount("table1",[pn], 3) As [Third Count],
fnPNCount("table1",[pn], 4) As [Fourth Count]
from table1;

Code:

Public Function fnPNCount(t As String, p As Variant, c As Integer) As Variant
    '
    't      is the table name
    'p      is the pn field value
    'c      is the count number
    '
    Dim d1 As Variant
    Dim d2 As Variant
    Dim d3 As Variant
    Dim d4 As Variant
    
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
        
    Set db = CurrentDb
    Set rs = db.OpenRecordset("select [count date] from " & t & " " & _
                    "where [pn] = '" & p & "' Order by [count date];", _
                dbOpenSnapshot)
    rs.MoveFirst
    If c = 1 Then
        d1 = rs(0).Value
    End If
    If c = 2 Then
        d1 = rs(0).Value
        rs.MoveNext
        Do While Not rs.EOF
            If DateDiff("d", d1, rs(0).Value) >= 62 Then
                d2 = rs(0).Value
                Exit Do
            End If
            rs.MoveNext
        Loop
    End If
    If c = 3 Then
        d1 = rs(0).Value
        rs.MoveNext
        Do While Not rs.EOF
            If DateDiff("d", d1, rs(0).Value) >= 62 Then
                d2 = rs(0).Value
                Exit Do
            End If
            rs.MoveNext
        Loop
        If Not IsEmpty(d2) Then
            rs.FindFirst "[count date] = #" & Format(d2, "mm/dd/yyyy") & "#"
            rs.MoveNext
            Do While Not rs.EOF
                If DateDiff("d", d2, rs(0).Value) >= 62 Then
                    d3 = rs(0).Value
                    Exit Do
                End If
                rs.MoveNext
            Loop
        End If
    End If
    If c = 4 Then
        d1 = rs(0).Value
        rs.MoveNext
        Do While Not rs.EOF
            If DateDiff("d", d1, rs(0).Value) >= 62 Then
                d2 = rs(0).Value
                Exit Do
            End If
            rs.MoveNext
        Loop
        If Not IsEmpty(d2) Then
            rs.FindFirst "[count date] = #" & Format(d2, "mm/dd/yyyy") & "#"
            rs.MoveNext
            Do While Not rs.EOF
                If DateDiff("d", d2, rs(0).Value) >= 62 Then
                    d3 = rs(0).Value
                    Exit Do
                End If
                rs.MoveNext
            Loop
        End If
        If Not IsEmpty(d3) Then
            rs.FindFirst "[count date] = #" & Format(d3, "mm/dd/yyyy") & "#"
            rs.MoveNext
            Do While Not rs.EOF
                If DateDiff("d", d3, rs(0).Value) >= 62 Then
                    d4 = rs(0).Value
                    Exit Do
                End If
                rs.MoveNext
            Loop
        End If
    End If
    rs.Close
    Set rs = Nothing
    Set db = Nothing

    fnPNCount = Choose(c, d1, d2, d3, d4)
End Function

shawnf · Jun 6, 2016

Thank you arnelgp for the response, I feel as if I am getting closer to the desired results.

I used the code, and it runs without bugs except it outputs the same date in First Count all the way down, same with Second Date and Third Date...the Forth Date column is empty, as I would expect it to be as I know we have not counted anything 4 times this year within the 62 day requirement.

Any idea what the problem is?
screen shot attached

arnelgp · Jun 7, 2016

can you post the sql.
are you sure you dont have same count dates with those pn's?

shawnf · Jun 7, 2016

Yes I am sure Those dates do not match the PNs. I was looking over the code and although my understanding of VB is limited I feel like it has to do something with the counter. I just left it as C and I am not sure where the info for the initial value of C comes from. I also do not know how to insert code into the forum with text boxes like others have done. hopefully this works ok.

the current code is bellow.
I just wanted to give some pseudo code as to how I envisioned this working if I knew how to write VB:

table = MI24As //sorted by date oldest to newest and by PN
Headers = PN, and DateCounted

//Set variable
A = PN
B = Date Counted
Set A to = first PN from MI24As
Set B to = first DateCounted from MI24As
Return A and B to Query as “PN” and “First Date Counted”
//Check A and B against next record
Does A = next records PN
If it does
Is B <= next records DateCounted
If it is change B to = this records DateCounted
Return B to query as “second date Counted”
If it does not
Check next record to see if A = next records PN
Loop until the next records PN does not = A
//returning the new value for B to the query as 3rd count…4th count and so on. This allows the program to skip a date if it does not meet the criteria and check the next date against the date before the one that failed the criteria, and not just check the date before each count date.
//Once the PN changes A will not = the next record
Change A to = next record PN
Change B to = the DateCounted of this new record
Return A as next line in query and B in “first Count Date” in this new line.
//Run the same loop as before checking the next record for matching PN and then to see if the date meets the 62 day requirement.

this is the current code:

Public Function fnPNCount(t As String, p As Variant, c As Integer) As Variant
'
't is the table name
'p is the pn field value
'c is the count number
'
Dim d1 As Variant
Dim d2 As Variant
Dim d3 As Variant
Dim d4 As Variant

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("select [DateCounted] from [MI24As]" & _
"where [PN] = [MI24As.PN] ORDER by [DateCounted];", _
dbOpenSnapshot)

rs.MoveFirst
If c = 1 Then
d1 = rs(0).Value
End If
If c = 2 Then
d1 = rs(0).Value
rs.MoveNext
Do While Not rs.EOF
If DateDiff("d", d1, rs(0).Value) >= 62 Then
d2 = rs(0).Value
Exit Do
End If
rs.MoveNext
Loop
End If
If c = 3 Then
d1 = rs(0).Value
rs.MoveNext
Do While Not rs.EOF
If DateDiff("d", d1, rs(0).Value) >= 62 Then
d2 = rs(0).Value
Exit Do
End If
rs.MoveNext
Loop
If Not IsEmpty(d2) Then
rs.FindFirst "[DateCounted] = #" & Format(d2, "mm/dd/yyyy") & "#"
rs.MoveNext
Do While Not rs.EOF
If DateDiff("d", d2, rs(0).Value) >= 62 Then
d3 = rs(0).Value
Exit Do
End If
rs.MoveNext
Loop
End If
End If
If c = 4 Then
d1 = rs(0).Value
rs.MoveNext
Do While Not rs.EOF
If DateDiff("d", d1, rs(0).Value) >= 62 Then
d2 = rs(0).Value
Exit Do
End If
rs.MoveNext
Loop
If Not IsEmpty(d2) Then
rs.FindFirst "[DateCounted] = #" & Format(d2, "mm/dd/yyyy") & "#"
rs.MoveNext
Do While Not rs.EOF
If DateDiff("d", d2, rs(0).Value) >= 62 Then
d3 = rs(0).Value
Exit Do
End If
rs.MoveNext
Loop
End If
If Not IsEmpty(d3) Then
rs.FindFirst "[DateCounted] = #" & Format(d3, "mm/dd/yyyy") & "#"
rs.MoveNext
Do While Not rs.EOF
If DateDiff("d", d3, rs(0).Value) >= 62 Then
d4 = rs(0).Value
Exit Do
End If
rs.MoveNext
Loop
End If
End If
rs.Close
Set rs = Nothing
Set db = Nothing

fnPNCount = Choose(c, d1, d2, d3, d4)
End Function

arnelgp · Jun 7, 2016

so your date field is DateCounted, here is the modified code, replace the one i gave you:

you do not need to modify the code.
just create a query the uses the function:

SELECT DISTINCT [pn], fnPNCount("MI24As",[pn],1) As [First Count],
fnPNCount("MI24As",[pn], 2) As [Second Count],
fnPNCount("MI24As",[pn], 3) As [Third Count],
fnPNCount("MI24As",[pn], 4) As [Fourth Count]
FROM MI24As;

Code:

Public Function fnPNCount(t As String, p As Variant, c As Integer) As Variant
    '
    't      is the table name
    'p      is the pn field value
    'c      is the count number
    '
    Dim d1 As Variant
    Dim d2 As Variant
    Dim d3 As Variant
    Dim d4 As Variant
    
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
        
    Set db = CurrentDb
    Select Case TypeName(p)
    Case Is = "String"
        Set rs = db.OpenRecordset("select [DateCounted] from " & t & " " & _
                        "where [pn] = '" & p & "' Order by [DateCounted];", _
                    dbOpenSnapshot)
    Case "Integer", "Single", "Double", "Boolean"
        Set rs = db.OpenRecordset("select [DateCounted] from " & t & " " & _
                        "where [pn] = " & p & " Order by [DateCounted];", _
                    dbOpenSnapshot)
    Case Else
        Exit Function
    End Select
    rs.MoveFirst
    If c = 1 Then
        d1 = rs(0).Value
    End If
    If c = 2 Then
        d1 = rs(0).Value
        rs.MoveNext
        Do While Not rs.EOF
            If DateDiff("d", d1, rs(0).Value) >= 62 Then
                d2 = rs(0).Value
                Exit Do
            End If
            rs.MoveNext
        Loop
    End If
    If c = 3 Then
        d1 = rs(0).Value
        rs.MoveNext
        Do While Not rs.EOF
            If DateDiff("d", d1, rs(0).Value) >= 62 Then
                d2 = rs(0).Value
                Exit Do
            End If
            rs.MoveNext
        Loop
        If Not IsEmpty(d2) Then
            rs.FindFirst "[DateCounted] = #" & Format(d2, "mm/dd/yyyy") & "#"
            rs.MoveNext
            Do While Not rs.EOF
                If DateDiff("d", d2, rs(0).Value) >= 62 Then
                    d3 = rs(0).Value
                    Exit Do
                End If
                rs.MoveNext
            Loop
        End If
    End If
    If c = 4 Then
        d1 = rs(0).Value
        rs.MoveNext
        Do While Not rs.EOF
            If DateDiff("d", d1, rs(0).Value) >= 62 Then
                d2 = rs(0).Value
                Exit Do
            End If
            rs.MoveNext
        Loop
        If Not IsEmpty(d2) Then
            rs.FindFirst "[DateCounted] = #" & Format(d2, "mm/dd/yyyy") & "#"
            rs.MoveNext
            Do While Not rs.EOF
                If DateDiff("d", d2, rs(0).Value) >= 62 Then
                    d3 = rs(0).Value
                    Exit Do
                End If
                rs.MoveNext
            Loop
        End If
        If Not IsEmpty(d3) Then
            rs.FindFirst "[DateCounted] = #" & Format(d3, "mm/dd/yyyy") & "#"
            rs.MoveNext
            Do While Not rs.EOF
                If DateDiff("d", d3, rs(0).Value) >= 62 Then
                    d4 = rs(0).Value
                    Exit Do
                End If
                rs.MoveNext
            Loop
        End If
    End If
    rs.Close
    Set rs = Nothing
    Set db = Nothing

    fnPNCount = Choose(c, d1, d2, d3, d4)
End Function

shawnf · Jun 7, 2016

You seriously just made my day!!!!!!!!!
I wish I could understand all that is going on here, but what I do know is, it works!

I hate even asking because I cannot imagine the amount of time you have put into this for me...and I seriously cannot thank you enough...but if there is a PN that has a blank count date (IE has not yet been counted this year) is there a way to have that included also by having the PN but all 4 count columns empty?

arnelgp · Jun 8, 2016

by all means include a pn with blank DateCounted.
see if the result is the one you expected.

Consolidate information

shawnf

New member

plog

Banishment Pending

shawnf

New member

shawnf

New member

plog

Banishment Pending

shawnf

New member

Attachments

plog

Banishment Pending

CJ_London

Super Moderator

arnelgp

..forever waiting... waiting for jellybean!

shawnf

New member

Attachments

arnelgp

..forever waiting... waiting for jellybean!

shawnf

New member

arnelgp

..forever waiting... waiting for jellybean!

shawnf

New member

arnelgp

..forever waiting... waiting for jellybean!

Similar threads

Users who are viewing this thread