microsoft Accesss Basic Coding

Hi,

So what are you wanting to achieve?  Looking at the two tables in your database sample, they are both identical...

If you are simply wanting to loop through the table, I can write some code for you to do that but it doesn't solve anything.  You need to get more specific about your objective here so we can help you.

~Tala~

You are corre
Customer      Product      Orderdate      NextOrderdate
11111      HOTEL      02/05/2017      
Customer      Product      Orderdate      NextOrderdate
22222      PLANE      02/28/2017      
Customer      Product      Orderdate      NextOrderdate
55555      VIP      02/15/2017

you are correct I included the wrong final table.  I have to business rules.  The first rule .  Was there an order placed. the second was there in orders place 30 days after the first order.
myclasstest.accdb

Classwork?

Do you have a question?  The table has changed one has 15 and the final should have 12 records.
myclasstest.accdb

Respectfully.

I asked if your question is for a homework or classwork project.  I did so because your database name "myclasstest" and the content within it appears as coursework content.  I apologize in advance if you feel that I am making an incorrect assessment.

We often find people coming to Experts Exchange to get their homework done for free.  In these cases, the terms and conditions of Experts Exchange does not permit us to answer homework or classwork related questions.  We are happy to help, but we cannot do it for you.

If you click on "Request Assistance" in your question and provide a reason for your request, one of the moderators will be able to look into your question further and should provide guidance as to whether or not we should continue helping you.

~Tala~

I am not in school.  This is classwork I am doing for a project at work.   To solve a business problem.

Okay.

Please accept my sincerest apologies.  May I suggest in future that you name your project files with something other than the words "class" or "test" as it automatically puts a flag up for us?

With that out of the way, I am looking into your sample db and have two questions.

1. What is the purpose of the field named "NextOrderdate" in tblOrder?
2. The 30 day rule you mention - do you need to consider business days only i.e. Mon~Fri and exclude Sat~Sun or does it not matter?

~Tala~

NP - I understand.  The NextOrdate can be removed from the table.  The 30 day rule can include weekend days.

Noted, will post something back soon for you to review.

Ok,

So, I made a few modifications to your sample db.  I added "Customers" and "Products" tables and made some adjustments to your "Orders" table as well.  

So, in the Customers table, I added an "OrderFlag" field with a default value set to 0.  In the "Orders" table, I also added an "OrderDateFlag" field with a default value set to 0.

Referring to the attached db, access the code IDE using "Alt+F11".

You will then see a module named "modRules"

Inside this module is a subroutine named "UpdateBusinessRule1".

When you run that subroutine , it will loop through your Customers table and update the "OrderFlag" field with either a 0 or 1 (0 = FALSE, 1 = TRUE) according to the business rules you posted initially.  Note that as a personal preference, I avoid using "Yes/No" or "True/False" fields - I can change that if you wish.

Please let me know if you are comfortable doing this, otherwise I can add a form and you can use a button to do it.

After you have finished reviewing, let me know if you have any questions about the logic I have presented, then we can move onto the second part using the order dates.

~Tala~
myclasstest_v1.accdb

I follow what your doing in the module.  You can  proceed with second part with the date logic.  Thanks

Great!

Now for your Order Date rule.

So for each order in the Order table, in date sequence, we will update the "OrderDateFlag" with a 0 or 1.  If the value is 1, then the previous order was placed less than 30 days before.  

I will post up a revised version for you later on.

~Tala~

Cannot look at your table right now (iPhone), but the query to determine how many customers have placed a second order within 30 days would look something like:

SELECT A.CustomerID
, A.OrderDate
, (SELECT Count(*) FROM yourTable
   WHERE (yourTable.CustomerID = A.CustomerID)
   AND (yourTable.OrderDate > A.OrderDate)
   AND (yourTable.OrderDate < DateAdd("d", 31, A.OrderDate))) as OtherOrders
FROM yourTable as A
WHERE A.OrderDate >= #1/1/17#

But if a customer has placed three orders during this period, you will get three records that might look like:

CustID    OrderDate     OtherOrders
1                1/1/17                  1
1                1/25/17                1
1                2/10/17                0

HTH
Dale

Hi,

So it turns out that this was a little trickier than I initially thought!

I have the following code working but I will need to refine it so that it loops through all the orders for each customer.

To test it, you can call it from the immediate window i.e. Call UpdateCustomerOrderDate(11111)

Sub UpdateCustomerOrderDate(lngCustomerID As Long)
'update the order date criteria in the order table

'On Error GoTo Error_Handler
    
    'declare recordsets and connections ->
    Dim db As Database
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset

    'setting objects ->
    Set db = CurrentDb
    Set rs1 = db.OpenRecordset("SELECT * FROM tblOrders WHERE (((tblOrders.CustomerID)=" & lngCustomerID & "))", dbOpenDynaset)
    Set rs2 = rs1.Clone
    
    'validate recordset count ->
    If rs1.RecordCount > 0 Then
        'move to first record ->
        rs1.MoveFirst
        rs2.Bookmark = rs1.Bookmark
        rs2.MoveNext
                
        'check the number of days between the customer order dates ->
        If DateDiff("d", rs1.Fields("OrderDate"), rs2.Fields("OrderDate")) <= 30 Then
            'set the flag value to 1
            With rs1
                .Edit
                !OrderDateFlag = 1
                .Update
            End With
        Else
            'set the flag value to 0
            With rs1
                .Edit
                !OrderDateFlag = 0
                .Update
            End With
        End If
    End If

'error handler ->
Err_Handler_Exit:
    rs1.Close
    rs2.Close
    Set rs1 = Nothing
    Set rs2 = Nothing
    Exit Sub

Error_Handler:
    If Err.Number = 0 Then
       Resume Err_Handler_Exit
    Else
       MsgBox ("Error " & Err.Number & " (" & Err.Description & ")"), vbOKOnly
          Resume Err_Handler_Exit
    End If
End Sub

Select allOpen in new window

Will sleep on it and finish it up tomorrow for you.

~Tala~

Forgot to mention that you will need to set a reference to the DAO recordset library in your project explorer.

~Tala~

Hi,

So, your requirement for Business Rule # 2 was a little trickier than I had initially thought.  The reason for this was due to the sequential comparisons of order dates for each customer id.

Please find attached a revised version for you with two subroutines :

UpdateBusinessRule1
UpdateBusinessRule2

The first subroutine updates the OrderFlag in the Customer table with a flag value of 0 or 1 based on whether or not the customer has actually placed an order with your company.  Results as follows :


The second subroutine updates the OrderDateFlag in the Orders table with a flag value of 0 or 1 based on a comparison of the most recent order date and the previous order date. Results as follows :


You should be fine with the ADO and DAO object library references if you use this version but if not, please let me know.

~Tala~
myclasstest_v2.accdb

Hi,

Just following up to see how you went with testing the solution I provided for you?

Please let me know if you have any issues otherwise feel free to close this question.

-Tala-

Tala- Had to make an emergency trip out of town. sorry about that.

I did find a couple of issues with logic for UpdateBusinessRule2.   Customer 11111 Order date flag should be set = 0 for order 13 it's more than 30 day after the first order.  Customer 66666 all Order date flag should be set = 0.  There all 30 days past the first order date.

Hi centralmike,

Thanks for getting back.

What date format are you using there?

-Tala-

should have been mm/dd/yyyy

So, the results image that I posted earlier was from a machine that uses a different international date format.  Please accept my apologies for any confusion.

The results for UpdateBusinessRule2 - I have changed the logic in the solution to reflect your instructions.

Using the earlier guidelines, please run a test on your own data and let me know if this works for you.

~Tala~
myclasstest_v3.accdb

Good Morning Tala, Somewhere we removed the Product column from our table orders.  The first product the customers has to order is the VIP package which is product id = 1.  than any proceeding products.  I have added a newtblorders to the database for validation.  I know you created the table but it was never referenced in the output.
myclasstest_v4.accdb

Morning,

Sorry, I am not sure I follow you - is this a question or do you need me to modify something because the solution I provided does not work?

The product did not hold any relevance as your business rules were based entirely on OrderDate which is why it was excluded.

I can drop the code into your original database and you can run it on your original table Final_tblOrder instead if you wish.  Just keep in mind that the OrderFlag and OrderDateFlag fields would need to be added to the table so that it can work.

Is this what you want to do?

~Tala~

Sorry for the confusion.  I think I need to add to business rule 2.  The first order has to be the vip product.  If the VIP is not the first product ordered, I need exit out of both of the business rules.  I was showing an example in the NewtblOrders.

Scope change at the 11th hour is bad for business centralmike!

Before I can make any changes, I need you to confirm that the changes I made to your database structure (i.e. tblOrders, tblCustomers, tblProducts etc...) are acceptable for you, or do you wish to revert back to the single table with 15 records in it from your first post?

Sorry about the scope change. Didn't realize the product table was not being used.  I confirm the change you made to the database structure works great.  I accept your changes.

Okay. Please keep this in mind when posting new questions in future. We are volunteers here and many other people need our help too.

I shall post up a new version for you to test out later on.

-Tala-

Ok, so after looking into your additional business rule using the VIP product, I have modified the database and the source code to accommodate this addition for you.

Please note that I added the VIP product rule to the customer table as it logically fits there based on whether or not a customer actually placed an order with you.  If a customer did not place an order, all three of the business rules you defined would return FALSE because an order was not placed to begin with, which leaves us with no products or order dates to test with.  

Also, please note that during testing, I found that the VIP product rule does not actually return any FALSE results as all the customers in your sample data ordered the VIP product on their first order with you.  

See below:


The order date rule remains the same as it worked previously.

You can run the processing of all the business rules you have specified using the single ProcessBusinessRules subroutine inside the attached database.

Please test it out and let me know how you go.

~Tala~
myclasstest_v5.accdb

Tala, I expected the final output would produce one row per customer.  Customer 11111 and 33333 produce to rows of data each.

CustomerID      CustomerName      OrderRuleTest      ProductRuleTest      OrderDateTest
11111      Customer A      PASS      PASS      FAIL
11111      Customer A      PASS      PASS      PASS
22222      Customer B      PASS      PASS      FAIL
33333      Customer C      PASS      PASS      FAIL
33333      Customer C      PASS      PASS      PASS
44444      Customer D      PASS      PASS      FAIL
55555      Customer E      PASS      PASS      FAIL
66666      Customer F      PASS      PASS      FAIL

i am not sure if you were stating I should create a group by query looking for pass in all three business rules.  Then I should get the following results.
CustomerID      CustomerName      OrderRuleTest      ProductRuleTest      OrderDateTest
11111      Customer A      PASS      PASS      PASS
33333      Customer C      PASS      PASS      PASS

I have confirmed the logic is working great.  Thanks for all your help.

Good stuff.

Please don't forget to close this question and if you need any further assistance, feel free to open a new one.

~Tala~

I am having trouble assigning points.

please close an assign maximum points.

done

Final solution was provided in comment #a42100322

Great help and knowledge

complete