Appending Dates

Leesha
5StarLounger
Posts: 1069
Joined: 05 Feb 2010, 22:25

Appending Dates

Post by Leesha »

Hi,
I'm not quite sure where to begin to describe what I need, so here goes.

I have a database with tblSales-temp which contains [id], [service_date], [net_sales], [Store_Id] and [TransactionCount]. The user gets a spreadsheet each month with all of the same info as listed above, however it thel [net_sales] and [TransactionCount] is the total for the month, not broken out by each day of the month. Currently they are dividing the [net_sales} and [TransactionCount] by the total days in the month and than manually filling in the results for each day of the month. This is extremely time consuming as there are multiple stores and they are growing in number each month.

How would I go appending the data to the table so that each day of the month was listed in short date format for each store in the excel spreadsheet being imported. The [net_sales] and [TransactionCount] would be divided by the total number of days in the month. [TransactionCount] would be round up or down to the nearest whole number and the [net_sales] would be rounded up or down to two decimal places.

Thanks,
Leesha

User avatar
HansV
Administrator
Posts: 69449
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Appending Dates

Post by HansV »

I'm sorry, you will have to provide much more detailed information, preferably in the form of a sample database and worksheet.
Regards,
Hans

Leesha
5StarLounger
Posts: 1069
Joined: 05 Feb 2010, 22:25

Re: Appending Dates

Post by Leesha »

Hi Hans,
I'm uploading a DB that has the information already imported into tblSales_Temp. There is a second table called tblSalesByServiceDate. This table already has store 29 (first store in tblSales_Temp) uploaded to it so you can see what I need. All of the stores in tblSales_Temp need to be appended to tblSalesByServiceDate.
Thanks!
Leesha
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 69449
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Appending Dates

Post by HansV »

Here is a VBA procedure that you can run. Please test it on a copy of your database.
Don't run the procedure twice - you'll get duplicate records!

Code: Select all

Sub AppendData()
    Dim dbs As DAO.Database
    Dim rst1 As DAO.Recordset
    Dim rst2 As DAO.Recordset
    Dim dtm1 As Date
    Dim dtm2 As Date
    Dim days As Long
    Dim dtm As Date
    Dim mth As Long
    Dim sales As Currency
    Dim cnt As Long
    Set dbs = CurrentDb
    Set rst1 = dbs.OpenRecordset("tblSales-Temp", dbOpenForwardOnly)
    Set rst2 = dbs.OpenRecordset("tblSalesByServiceDate", dbOpenDynaset)
    Do While Not rst1.EOF
        mth = rst1!Month
        dtm1 = DateSerial(Year(Date), mth, 1)
        dtm2 = DateSerial(Year(Date), mth + 1, 0)
        days = dtm2 - dtm1 + 1
        sales = Round(rst1!Net_Sales / days, 2)
        cnt = Round(rst1!TransactionCount / days, 0)
        For dtm = dtm1 To dtm2
            rst2.AddNew
            rst2!Service_Date = dtm
            rst2!Store_ID = rst1!Store_ID
            rst2!Net_Sales = sales
            rst2!TransactionCount = cnt
            rst2!Store_Name = rst1!Store_Name
            rst2.Update
        Next dtm
        rst1.MoveNext
    Loop
    rst1.Close
    rst2.Close
End Sub
Regards,
Hans

Leesha
5StarLounger
Posts: 1069
Joined: 05 Feb 2010, 22:25

Re: Appending Dates

Post by Leesha »

Thanks Hans! I can't wait to see it work. I added the code to the on click function to a form but get an error "item not found in this collection" and this is highlighted, "mth = rst1!Month". I am not sure how to fix. I would kill to be able to write code like this.
Leesha

User avatar
HansV
Administrator
Posts: 69449
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Appending Dates

Post by HansV »

In your sample database, the table tblSales-Temp has a field named Month.

S3592.png

Does it have a different name in your real database?
If it is named Month, does it make a difference if you change the offending line to

mth = rst1![Month]
You do not have the required permissions to view the files attached to this post.
Regards,
Hans