Results 1 to 8 of 8
  1. #1
    srwafa is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    7

    Update Table with the sale date where product was previously bought by same customer.

    Hi there,

    I have a small problem that needs your help.

    Background
    I have a db that is connected to 4 tables in a SQL back-end through an ODBC connection. The tables connected are bdo_customer, dbo_inventory, dbo_products, and dbo_sales. I then created a query "qrySales" to pull the fields I need from these tables.

    Click image for larger version. 

Name:	2019-07-04_12-52-46.jpg 
Views:	17 
Size:	22.3 KB 
ID:	38988

    Objective
    I need to create a report that shows when a product was previously bought by a customer for each instance they have bought the product. In other word, for each instance of a sales record I need to show the preceding date the customer has bought the exact same product.

    Current Solution
    I have created a SELECT query that creates the report that I need and it works 100%, but the major challenge is that this query takes too long to run as it will be connected to a customer form through a subform. Now if an end-user navigates different customer records and given the time to run this query the whole application is taking too long to get information. The SQL view for the query is:

    Click image for larger version. 

Name:	2019-07-04_13-12-37.jpg 
Views:	16 
Size:	210.6 KB 
ID:	38989

    HTML Code:
    SELECT qrySales.[BP No], qrySales.[Sales Date], (SELECT
    Max([Sales Date])
    FROM qrySales AS OldInvoices
    WHERE OldInvoices.[Sales Date] <
    qrySales.[Sales Date]
    AND OldInvoices.[BP No] = 
    qrySales.[BP No]
    AND OldInvoices.[Item No] = 
    qrySales.[Item No]) AS [Previous Invoice], [Sales Date]-[Previous Invoice] AS Lag, qrySales.[Item No], qrySales.[Product Name], qrySales.[Sales Units], qrySales.Doses, qrySales.[Sales Amount], qrySales.[Discount Allowed], [Sales Amount]-[Discount Allowed] AS [Net Sales]
    FROM qrySales
    ORDER BY qrySales.[Sales Date] DESC;

    Proposed Solution
    Now I have thought of writing a VBA code that fires action queries at program start to create a table with the information described in the Objective then reference all forms and subforms to this table instead of the query. The action queries are (1) DELETE query that deletes the table created at previous start up (2) APPEND query that adds latest records but excluding the Previous Invoice (3) UPDATE query that would then update the table with the records for Previous Invoice records. The First two are OK but the third is not working for me. I have the following code for the UPDATE query and it retaining empty

    Click image for larger version. 

Name:	2019-07-04_14-08-01.jpg 
Views:	15 
Size:	107.6 KB 
ID:	38990


    HTML Code:
      UPDATE [Product Sales], [Product Sales] AS [Old Invoices] SET [Product Sales].[Previous Invoice] = DMax("[Sales Date]","Product Sales","[Old Invoices].[Sales Date] < '" & [Product Sales].[Sales Date] & "' AND [Old Invoices].[BP No] = '" & [Product Sales].[BP No] & "' AND [Old Invoices].[Item No] = '" & [Product Sales].[Item No] & "'"), [Product Sales].Lag = [Sales Date]-[Previous Invoice];
    This UPDATE query must achieve what has already been achieve by the Current Solution which is working with issues.
    My sincere apologies for the length explanation I wanted to make clear the problem as well as the solution needed.



    A VBA solution or any other alternative solution is also welcome


    Thank you in advance

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    the issue is perhaps having a subquery on a query - since you only want a max date, base your subquery on the source data

    and if your table is very large, consider putting in a 'backstop' to reduce the number of records returned - subquery might include the criteria 'where saledate>dateadd("yyyy",-1,date())' for example

    indexing - are saledate and fields used in the criteria indexed?

    and is this required for a report across the business? or just as a source for a form to display information for a single customer? if the latter, apply the customer id criteria to the main query in the recordsource, rather than a filter

    consider an alternative subquery using TOP and ORDER BY

    i.e. subquery is

    Code:
    (SELECT TOP 1 [Sales Date]
    FROM qrySales AS OldInvoices
    WHERE OldInvoices.[Sales Date] < qrySales.[Sales Date]
    AND OldInvoices.[BP No] = qrySales.[BP No]
    AND OldInvoices.[Item No] = qrySales.[Item No]
    ORDER BY [Sale Date] Desc) AS [Previous Invoice]
    if you can put your subquery into the criteria your full query might look like this (not tested and will only return the latest one, not for a history per customer/item)

    Code:
    SELECT
        Q1.[BP No], 
        max(Q1.[Sales Date]) as Current, 
        max(Q2.[Sales Date]) as Previous, 
        Current-Previous AS Lag, 
        Q1.[Item No]
    
    FROM qrySales Q1 INNER JOIN qrySales Q2 
        ON Q1.[BP No] = Q2.[BP No] AND Q1.[Item No] = Q2.[Item No]
    WHERE Q2.[Sales Date]<Q1.[Sale Date]
    GROUP BY
        Q1.[BP No], 
        Q1.[Item No]
    Finally, since your BE is SQL Server, consider using a stored procedure or passthrough query

    edit: just played around with the last query - this will return every sale date where there is a previous sale date (i.e. single sales excluded)
    Code:
    SELECT
        Q1.[BP No], 
        Q1.[Sales Date] as Current, 
        max(Q2.[Sales Date]) as Previous, 
        Current-Previous AS Lag, 
        Q1.[Item No]
    
    FROM qrySales Q1 INNER JOIN qrySales Q2 
        ON Q1.[BP No] = Q2.[BP No] AND Q1.[Item No] = Q2.[Item No]
    WHERE Q2.[Sales Date]<Q1.[Sale Date]
    GROUP BY
        Q1.[BP No], 
        Q1.[Item No]
        Q1.[Sale Date]

  3. #3
    srwafa is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    7
    Thanks Ajax for your response,

    If I followed your solution well it seems to address the SELECT query's position. Similar to the one I have below which is actually working and giving me the table in figure 1 below. The problem with this solution is its taking to long to fetch the data, but it is getting the job done nonetheless. So to speed up execution I want the query results to be added to a table so that other reporting operation can can fetch from the table instead.

    Click image for larger version. 

Name:	2019-07-04_13-12-37.jpg 
Views:	14 
Size:	210.6 KB 
ID:	38994


    This SELECT query is working

    Code:
    SELECT qrySales.[BP No], qrySales.[Customer Name], qrySales.[Sales Date],
    
    (SELECT Max([Sales Date])
    
    FROM qrySales AS OldInvoices
    
    WHERE OldInvoices.[Sales Date] < qrySales.[Sales Date]
    
    AND OldInvoices.[BP No] = qrySales.[BP No]
    
    AND OldInvoices.[Item No] = qrySales.[Item No]) AS [Previous Invoice], 
    
    [Sales Date]-[Previous Invoice] AS Lag,
    
    qrySales.[Item No],
    
    qrySales.[Product Name], 
    
    qrySales.[Sales Units],
    
    qrySales.Doses,
    
    qrySales.[Sales Amount],
    
    qrySales.[Discount Allowed],
    
    [Sales Amount]-[Discount Allowed] AS [Net Sales]
    
    FROM qrySales
    
    ORDER BY qrySales.[Sales Date] DESC;
    The UPDATE query is the one not working

    Code:
    UPDATE [Product Sales],
    
    [Product Sales] AS [Old Invoices]
    
    SET [Product Sales].[Previous Invoice] =
    
    DMax("[Sales Date]","Product Sales",
    
    "[Old Invoices].[Sales Date] < '" & [Product Sales].[Sales Date] &
    
    "' AND ]Old Invoices].[BP No] = '" & [Product Sales].[BP No] &
    
    "' AND ]Old Invoices].[Item No] = '" & [Product Sales].[Item No] & "'"),
    
    [Product Sales].Lag = [Sales Date]-[Previous Invoice];

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Just my opinion - I agree with populating local table(s) because subqueries/nested queries can increase run time. So can (too many) calculations - all of which can be too much for network traffic. Then there are potential issues where your "simple" query mines every record of linked tables, then does the processing/calculations and returns a subset However, I also think

    (1) DELETE query that deletes the table created at previous start up
    No. Repeatedly deleting/recreating a table is not a good practice.
    (2) APPEND query that adds latest records but excluding the Previous Invoice
    No? Just append without criteria, thus you don;t need to filter on any particular field value, but this requires special handling: turn off warnings (one of the rare times I might do this and should have a properly written error handler to ensure they are not left off) OR use .Execute method without dbFailOnError parameter. Eiterh way, just allow the query to append what's new. To do that, the table needs PK and/or indexes to prevent duplicates. Access will just silently append only the new records.

    (3) UPDATE query that would then update the table with the records for Previous Invoice records
    I guess so if the append only adds new and the old might have been updated. You could set an Updated flag in a table and check it each time db opens and prompt. If you have a pc that's always logged on to, you can run the updates via Task Scheduler in the middle of the night and be ready for the next AM.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    The UPDATE query is the one not working


    suspect the issue is because you have spaces in your names so you need to surround them with square brackets

    DMax("[Sales Date]","Product Sales",


    you also appear to have some brackets the wrong way round.

  6. #6
    srwafa is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    7
    Thank you for the response. I really appreciate your time bud...

    Quote Originally Posted by Micron View Post

    Repeatedly deleting/recreating a table is not a good practice.
    I agree with you there especially when the reports may concurrently be accessed by at least five users at the same time. However is this a practice issue or there are other long term problems.

  7. #7
    srwafa is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    7
    Thanks bud...

    suspect the issue is because you have spaces in your names so you need to surround them with square brackets
    Still in the jungle here... I have tried removing spaces with no luck.

    I'm thinking there is something wrong with the aliases. This is the error message I'm getting...

    Click image for larger version. 

Name:	2019-07-05_20-59-03.jpg 
Views:	9 
Size:	18.7 KB 
ID:	39016

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Seems that you have at least 2 tables involved that have the same name(s). Distinguish which is which by including the table name as in tblName.fldName

    If that was a question about repeatedly recreating a table, then yes. It is a commonly held belief that it contributes to db bloat and corruption.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 03-26-2017, 01:26 PM
  2. Replies: 2
    Last Post: 11-11-2016, 08:57 AM
  3. Last purchase price of a product sale
    By EMAS in forum Queries
    Replies: 4
    Last Post: 12-22-2015, 02:01 PM
  4. Replies: 5
    Last Post: 03-23-2014, 09:01 AM
  5. Stock Control/ Customer/ Sale database
    By Halwa in forum Access
    Replies: 1
    Last Post: 06-06-2012, 02:49 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums