I'm not following.
If you're trying to join data between "source file" and "city master", I guess I would probably create two recordsets and use a temp table or maybe recordset.fields.append to bluff a join.
I'm struggling mightily to wrap my head around this one. I've got ideas, but every time I sit down to do it I end up going down a rabbit hole. I've got a bunch of numbers like:
I want to parse them as a FROM and a TO like:
power query and power bi are both free. Power Query is strictly query plus some pretty functional transformation functions. Power BI contains the same functionality as powerquery but also adds nifty dashboardish type display.
So---I've got a coding problem. I've got a solution. I'm looking for a second opinion if this is the right way to handle it, or if there's a better way:
The set up:
-It's access. The code lives in a class.
-The overall process is:
1) Feed the class a bunch of parameters
Maybe a guru level sql guy could do something with this. My approach might start with sql to fetch the right data but for adding logic to the output you might be best off with a recordset. You could pretty reliably get the data, loop through it and account for various conditions, and construct...
this won't win points for style
For Each ws In ThisWorkbook.Worksheets
Select Case ws.Name
Case "Projects", "Small Works", "Tech & Crate", "Tech Only"
Sub dowork(sWSName As String)
Use "#" to work with dates. Also, use "/" in between elements of a date instead of the "-". So like:
sSQLSting = "SELECT * From [Sheet1$] WHERE endDate >= #01/05/2017# and endDate < #28/05/2017#"
Lemme answer your next question too:
you'll get input using a variable like
Ah. I use an email for that. Just at the end of a long routine I throw on a quick email that says "your stuff is done". It also works for any constituent users you may have who insist being notified of it too.
If there's no need to get cute:
1) put your data in a dynamic named range
2) create 2 pivot tables with the desired sort on and set filter to top X
3) Using getpivotdata() set up grids on your output sheet
4) simple....super simple....vba to refresh the pivot tables based on the named...