This is perfect. I will use your one as it does exactly what I need and does not need the steps to Excel.
Appreciate the time you took to create this. Thanks :)
Gareth Jones
Last Activity: Mar 12 '17, 05:23 PM
Joined: Feb 28 '11
Location:
-
I've got it :) I started from scratch and it though it may be easier to append the folder path using Excel. So the method I used to automate the whole process was to first import the folder structure into Access using button1 in the DB. Then I exported this folder structure into Excel. I then used the below code to import the content in:
...Code:Sub step1() Dim wsMstr As Worksheet: Set wsMstr = ThisWorkbook.Sheets("sheet1")Leave a comment:
-
I see what you mean. There may be a way of appending the table name to each table individually before appending them all together.
Something along the lines of for each table ID in tbldirectory2, append the ID to the table with the same ID. I can then combine all these into one table which I have done already. That is all I need to do TBH.Leave a comment:
-
Thanks for taking the time on this. I have uploaded the DB for you to look at. The DB just contains the functions to make this work as a concept.
First I click button1 which runs the function in module 1. This imports the directory listing of the specifc folder including subfolders, which creats [tbldirectory]
Button2 then creates a new table from [tbldirectory] that combines the path and CSV file name into [tbldirectory2]....Leave a comment:
-
We are a step closer as this now adds the table ID to the main table, however it doesnt add the contents of the other tables. Is there a way we can incorporate SELECT * from the other tables as well as appending the table name?
What I believe I need is something like
But...Code:strSQL = "INSERT INTO [MainTable] SELECT *,([MyID]) VALUES('" & ![ID] & "_" & strTableName & "')"Leave a comment:
-
No problem, these are all test names for now, but there is a table containing all the table names called "tableoftab les" with [ID] and [tablename]
The fields in each table I am appending from has [field1] and [field2]. These copy append fine into a table called "maintable" .
I have added a field called [tablename] into this "maintable" as it is the same name as the field in "tableoftab les"...Leave a comment:
-
I've tried doing this but unfortunately I keep getting syntax errors. All the fields from other tables copy across ok as they are picked up in the SELECT *. The only issue is the table name variable rs!tablename. I have added a field to the new table called "tablename" however I dont believe this makes a difference. I believe I need to specify what field to append the tablename to, but I am sure of the syntax.
Appreciate...Leave a comment:
-
I have tried all ways of using Chr$(34) but cannot seem to get the syntax right. I have gone a slightly different way below and am agonisingly close now...
When I hover over the strSQL I can see
"INSERT INTO maintable select *, "993", from "993""
All the rs!tablename variables are correct.
...Code:Dim db As DAO.Database Dim rs As DAO.Recordset Dim cstrAppend
Leave a comment:
-
I am even closer now. I have used the below code to append all the tables into one:
...Code:Dim db As DAO.Database Dim rs As DAO.Recordset Const cstrAppend As String = "INSERT INTO maintable SELECT * FROM " Set db = CurrentDb() Set rs = db.OpenRecordset("tableoftables") Do Until rs.EOF db.Execute cstrAppend & rs!TableName, dbFailOnErrorLeave a comment:
-
Thanks both for replying. I tried the code from AIRCODE and I am 90% there now.I have a list of tables with the contents of the CSV's, and each table name corresponds with the ID of the path in the directory table.
I have tried and tried however to find a way of linking the table name with the ID of the directory table. Is there a way of doing this? I presume it will involve some sort of INSERT INTO query where I would insert the...Leave a comment:
-
Import CSVs and location from multiple folders and sub folders
Hi,
I am trying to import a number of CSV's from a number of folders including subfolders. The snag is that I need to record the folder name with the file contents from each folder.
I can import the file name and contents when it is from one folder, but not multiple.
I am able to import the full directory list including the folder/subfolder names but not the contents of the actual files. I have used... -
Got it :) I inserted all the rows I needed in Excel using the below function once I got the count of rows per 15 mins using the above queries.
...Code:Public Sub CopyData() ' This routing will copy rows based on the quantity to a new sheet. Dim rngSinglecell As Range Dim rngQuantityCells As Range Dim intCount As Integer ' Set this for the range where the Quantity column exists.Leave a comment:
-
Yes however I wasnt entirely sure if I was doing right. Am I right in saying I need to calculate the mod 15 on the date/time, or the count of the date/time per 15 mins? When I just try the mod 15 on the date/time, the results arent right.
I have tried the below which gets me 50% there
...Code:SELECT CDate(Format([time1],"Short Date"))+(CDate(Format([time1],"Short Time"))*1440\15)*15/1440 AS TimePeriod, Count(table1.time1)
Leave a comment:
-
Thanks both. So essentially what I am looking for is to count how many records are in every 15 min interval in my table, and insert/add an additional 5% of this count.
So for example, if there are 55 records in a 15 min interval, I need to add an additional 2.75 (3) records to the original amount of 55. These can be 'dummy' records and the content of the record is irrelevant. The reason I cant just add 5% to totals is that this raw...Leave a comment:
-
No problem, just to check is this to be done in a function or a query?
ThanksLeave a comment:
-
Thanks for answering. Is there a way of doing this on mass? I wasn't very clear in the OP, as there are approx 800k records.
ThanksLeave a comment:
-
Add records depending on count of 15 min interval
Hi all,
Is it possible using a function or query, to group records by 15 minutes, count how many records there are in this interval and add a percentage of these (e.g. 5%) records to uplift the amount. I have tried a different methods however none appear to be working.
Thanks
Gareth -
Thanks all for continuing to look into this.
After a relatively small amount of manual work, I have the table where I need it, and have been able to workout the duration etc and the report is now effectively finished.
It would still be good to see where I have gone wrong with this function however so below is the current code. I am now using the employee reference instead of the name, which is a reference no. with no...Leave a comment:
-
I have tried inserting the debug line between 20 and 21 however its still jumping to the end and erroring. I tried making it a DAO recordset as well however its still doing the same. I believe Stewart has hit the nail on the head in that I should be able to do this with a subquery however I couldnt get the syntax right. The ID is the primary key in the table and each person has a unique ref number too which I can group on. I will have another go...Leave a comment:
-
I have done some more testing and those 2 lines still say the same after passing past them. It jumps straight from line 21 to 42. It then gives the error, and after clicking ok, it goes to line 43 and then starts again. This is the only code and only function in the DB. It is based on a simple table and not a query. Once it works, I can copy the function to the real DB. I may end up having to delete the records I don't need manually however its...Leave a comment:
No activity results to display
Leave a comment: