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
Appending Dates
-
- Administrator
- Posts: 78241
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Appending Dates
I'm sorry, you will have to provide much more detailed information, preferably in the form of a sample database and worksheet.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1484
- Joined: 05 Feb 2010, 22:25
Re: Appending Dates
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
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.
-
- Administrator
- Posts: 78241
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Appending Dates
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!
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
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1484
- Joined: 05 Feb 2010, 22:25
Re: Appending Dates
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
Leesha
-
- Administrator
- Posts: 78241
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Appending Dates
In your sample database, the table tblSales-Temp has a field named Month.
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]
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.
Best wishes,
Hans
Hans