Queries :: Get Top 10 NPI Per State And County
Feb 27, 2015
I am trying to get the Top 10 NPI's by State and By County based on the Current_Year_Members_Outreached. Below is the query I created but I haven't been able to verify if the results are correct because when I execute it runs and some results show but in the lower right corner of the screen it says "Formatting Page" and seems to be taking forever.
My source table only has 67k records which doesn't seem like a lot. So, I have two questions:
1. Does my query below look correct based on what I am trying to achieve?
2. Is there a better way to right this query to get better performance?
Code:
SELECT
A.STATE,
A.COUNTY,
A.NPI,
A.[SumOfCurrent Year Members Outreached]
FROM Qry_Provider_Distinct AS A
[Code] ....
View Replies
ADVERTISEMENT
Sep 5, 2013
I want to get the sum of sales of the top 5 best agents by state.
I have been able to do the following, however, this only gives me the state of "vic". I want it to show all states.
SELECT TOP 5 filedata.Debtor, Debtors.FullName, Sum(filedata.NetTotal) AS SumOfNetTotal, Debtors.State, Count(filedata.BorQ) AS CountOfBorQ
FROM filedata INNER JOIN Debtors ON filedata.Debtor = Debtors.Code
WHERE (((filedata.BorQ)="b") AND ((filedata.DepDate) Between [Forms]![F-financemenu].[startdate] And [Forms]![F-financemenu].[enddate]))
GROUP BY filedata.Debtor, Debtors.FullName, Debtors.State
HAVING (((Debtors.State)="vic"))
ORDER BY Sum(filedata.NetTotal) DESC;
which returns
DebtorFullNameSumOfNetTotalStateCountOfBorQ1429ABC company147150VIC11360XYZ Company107204.55VIC32534MNO organisation68799.86VIC31168PQR business58934.67VIC31326FGH industries32480VIC2
(count of BorQ) is the number of sales
I have tried using UNION ALL and changing the State but that only caused syntax errors.
View 2 Replies
View Related
Mar 22, 2013
I want my form frmintake to lookup with values from another table and populare county once the zip codes has been entered.
I have a table called tblintake and another tblcounty.
tblintake has zip codes entered manually
tblcounty has zip codes and counties for all regions
how does this work out in dlookup.
=DLOOKUP[Zip Code],[tblCounty],[zip Code]=[County])
View 1 Replies
View Related
Jul 13, 2005
Ok, I know you guys have heard about this problem quiet a few times, my search told me that. But my problem has a bit of a twist. The message appeared when I tried to open the database yesterday, so I went to the backups and tried to open them. All the backups for the past month give the same message. The problem is with the FE only, the BE is working fine. The backups are connected to the same workgroup as the FE and BE. Has anyone seen this before and know what might of happened?
View 8 Replies
View Related
Mar 28, 2006
I am trying to determine the state that a job is located in. If the ProjectID begins with a 2 then it is in California. If it does not then the job is located in Nevada.
State: IIf(Left([tblMainFrontierUnits].[ProjectID],1)="2","CA","NV")
What is wrong with this statement? I am getting a compile error.
View 5 Replies
View Related
Aug 26, 2005
Does any one know of a place online where you can get the tables (for free I hope) for a city state zip comboboxes, or at least the tables for all cities in the US.
View 8 Replies
View Related
Dec 9, 2005
If the check box is null (grey) - the default value, I do not want to include it is the sql statement. If I use the code below I get the error 94 Invalid use of null. How else can I exclude this ststement is the check box is grey.
thanks
If CheckStaged Then
strWHERE = strWHERE & " AND s.Staged = " & CheckStaged
End If
View 2 Replies
View Related
Dec 11, 2005
i have 2 questions.
1) how can i have a record automaticly fill in the next date from the privous record? (Example if record 1 has date:10/14/05 i want record 2 to fill in in that same feild 10/15/05?
2) how can i have a reccord automaticly fill in the same number from the privios record? (Example if record 1 has Number :34 i want record 2 to fill in in that same feild 35?
I keep on changing these numbers, so its vitel that it repeats the privous record
David
View 3 Replies
View Related
Jun 7, 2006
I am getting the following Error:
"This database is in an unexpected state; Microsoft access cannot open it.
"This database has been converted from a prior version of Microsoft access by using the DAO CompactDatabase method instead of the Convert Database command on the tools menu. This has left the database in a partially converted state.
"If you have a copy of the database in its original format, use the convert database command on the tools menu to convert it. If the original database is no longer available, create a new database and import your tables and queries to preserve your data. Your other database objects can't be recovered."
The database is on the server. Nothing was changed. Someone in our IT department says they simply opened it and then closed it. I think that they had their access program set to compact and repair on close.
I have tried to import the tables but am gettting the same errors.
I have the original database but it is an old backup that is missing data.
Can someone HELP
View 1 Replies
View Related
Jan 14, 2005
Hello out there:
We have a application that imports information from a csv file into our customer order table. After we process the order we have to export this inforrmation to our UPS shipping program to process the shipments.
Problem is that the UPS program only recognizes the abbreviations for state names. Is there a way to run a macro, code or update query that will change the state names to the correct abbreviated version before we do our export?
Thanks immensely,
Ivan
View 2 Replies
View Related
Mar 3, 2007
Do you know of any knows list that contains cross-refs of all Countries,state/province,region so that I can build cascading combox for my site.
This is very strange. It is a concept we see all over the internet websites, but the least documented!!!!
View 1 Replies
View Related
Feb 21, 2007
Hi All!!
I need help with the code for a combobox. The thing is that I've created a form where the user has to select the country, then the state and finally the city. What I need is that once the user selects the country, the combobox of the state shows only the states of that country and then the combobox of the city shows only the cities of the chosen state. I'd appreciate all the help given. Andrew.
View 1 Replies
View Related
Jan 15, 2013
I have a database will all the FM stations in the USA/Canada, I wish to plot the frequency and calls to the correct city on the state map as per the attachment, can it be done? If so, what are my steps and what maps can I use?
View 2 Replies
View Related
Mar 1, 2005
I'm stuck on something that should be simple, but maybe it isn't:
Problem 1:
To speed data entry in a contact table, I want an entry in the Zipcode (postal code) field to automatically populate the City and State fields from the data in a Zipcode table (Zipcode-PK, City, ST (2 letter code)). The Zipcode table has only one city /st for each zipcode, so it can be the primary key (always a five digit number, sometimes with leading zero(s)).
Sometimes there are several names for a City associated with a particular Zipcode, so I want the City field to be able to be overwritten with a different city name if the data entry person needs to do that. Alternatively, I could establish a separate primary key for the zipcode table, and allow several choices for the rare zipcode that has more than one city name in use.
If I did that I wouldn't have to store the city and st in the contact table (which is better database design, I realize), but I really do want to be able to store the city and state in the contact table because there will be times when the right zipcode isn't known, and the city and state are known, and forcing the input of a possibly incorrect zipcode would be undesirable.
Problem 2: I've got a table with 4,000 records where I have the zipcode, but the city and state information were lost, and I want to be able to re-populate those fields by writing, I guess, an update query that taps the info in the zipcode table.
Problem 3: When entering addresses, sometimes there won't be info about a certain zipcode, and I need some automatically generated dialog that allows adding city and state info for a new zipcode that won't slow down the entry of contact records.
Any help would be greatly appreciated. Thanks in advance. Send an email if you prefer.
HenryTD
View 3 Replies
View Related
Jul 13, 2005
Apologies first if there is an easy answer to my problem.
I have set up an option group with 3 checkboxes with choices Yes, No, Maybe. On data entry you can click any of the three boxes but after clicking one how do you revert to a situation with all 3 checkboxes cleared.
I don't want to use a default checkbox as that would add another row but I just want to clear out the boxes so it looks as though none of them have been ticked.
I hope somebody understands my problem.
View 2 Replies
View Related
Aug 24, 2004
I am trying to create a suitable expression, either on the form itself or in the underlying query, that calculate the balance after each transaction, like a bank statement. What is the best way to do it?
Paul
View 2 Replies
View Related
Dec 8, 2012
i want to have a button where the operator can cancel when on a current record. i want an if. if form has changes made but not saved then undo else close.
is the record state 'dirty'?
View 2 Replies
View Related
Feb 24, 2014
I'm trying to do something with Excel and Access. From Excel 2007, I need to open an Access database exclusive, import from Access to Excel a table, do some work within Excel, and then start a macro within the Access database. Briefly, here’s what I have
1) To open the database exclusively:
Set connDB = New ADODB.Connection
With connDB
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Mode = adModeShareExclusive
.Open strdbpath 'path to database
End With
2) To import from Access:
strSQL = "SELECT * FROM [" & tn & "];" 'tn is Access table
If connDB.State = adStateOpen Then
Set objRS = New ADODB.Recordset
objRS.Open strSQL, connDB, adOpenForwardOnly
On Error Resume Next
objRS.MoveFirst
If Err.Number = 0 Then
On Error GoTo 0
fieldCnt = objRS.Fields.Count
For fieldNum = 0 To fieldCnt - 1
ws.Cells(1, fieldNum + 1).Value = objRS.Fie(fieldNum).Name
Next fieldNum
ws.Cells(2, 1).CopyFromRecordset objRS
End If
End If
3) To start a macro:
DoCmd.RunMacro "Daily Import"
Everything seems to work until the ‘DoCmd’ statement where I get a “You can’t carry out this action at the present time” error message.
View 8 Replies
View Related
May 8, 2013
In ACC2010: A subform has a control for date of birth with an AfterUpdate Event. When the date picker is used to populate the control the user must either click outside the control or press the tab key to trigger the control's AfterUpdate event. This event runs some calculations, the results of which are stored in a separate table. When the calculations are finished and focus returns to the form, the form's state depends on whether a keypress or a mouse click was used to trigger the event.
The form's state is indicated by whether calculated controls on the form are empty - mouse clicks leave contents as before the event, tab causes the contents to disappear. In addition, if a keypress is used the user must click twice to trigger the form's BeforeUpdate event. A single click is required to trigger that event if a mouse click occurred after using the date picker.
How to get thekeypress to behave the same as a mouse click?
View 2 Replies
View Related
Jan 1, 2014
I tried this code to change the label to display the full name of the state on the form. Is there a shorter way than putting 49 more states after the if?
[CODE]Private Sub cmdStates_Click()
DoCmd.OpenForm "frmChurchesAll"
DoCmd.ApplyFilter "qryFindState"
lblTxtSt = txtState
If txtState = "FL" Then
lblTxtSt = "Florida"
End If
lblTxtSt.BackColor = 15658720
lblTxtSt.Visible = True
lblStatesof.Visible = True
lbAllChurches.Visible = False
End Sub /CODE]
D7
View 8 Replies
View Related
Mar 2, 2014
when i select rows in a ListBox control on a form, using access VBA, some rows behave exactly the opposite of how they're supposed to behave-- they APPEAR unselected when i select them, and selected when i unselect them.
Code:
oListBox.Selected(lngRow) = True
However, when i check their Selected property with VBA they return the expected value (meaning the row Selected value is TRUE if i set it to TRUE, if tho it LOOKS UNselected).
Code:
Print oListBox.Selected(lngRow)
->True
Access ListBox Control Under VBA Control Behaving Wacko - YouTube.It seems that the more times i requery the listbox, the worse the problem gets.
Code:
oListBox.Requery
This problem seems unrelated to the number of times i select rows. Only seems related to multiple requeries. At first, it does not happen. Then with repeated requeries, the problem gets progressively worse. Note, 'Row Source Type' is set to 'Table/Query'. 'Multiselect' is set to 'Simple'.
i'm not using the "Form_" syntax anyplace (which can created unintended instances of forms). this listbox source-query contains a VBA function in a module, which in turn calls the code-behind of another form.
View 6 Replies
View Related
Jun 19, 2014
I have the following code which errors on line
Code : .OpenDataSource Name:=CurrentProject.FullName, SQLStatement:="SELECT * FROM [qryMailMerge]"
The error (Error has occurred: The database has been placed in a state by user 'Admin' on machine 'W74XXXXXX' that prevents it from being opened or locked) appears in the Word document.
Code:
Public Sub CreateWordToPDF(strWordFile As String, strPDFFile As String, strSQL As String)
'Call CreateWordToPDF("C:Doc1.doc", "C:Doc1.pdf", "SELECT * FROM [qryMailMerge]")
Dim objWord As Word.Application
Dim docWord As Word.Document
'Open MS Word using early binding.
Set objWord = New Word.Application
Set docWord = objWord.Documents.Open(strWordFile)
[Code]......
View 8 Replies
View Related
Mar 13, 2007
hi,
Everyone know this error? the database has been placed in state by user "Admin"!
We are using a shared database in Access 2003 that is still in Access 2000
format. When more than one user has the database open (by using the vb.net), the following error
message appears "The
database has been placed in a state by user 'Admin' on machine XXXXX that
prevents it from being opened or locked."
How to slove this error? please help! Urgent!:confused:
View 3 Replies
View Related
Jan 1, 2014
Im trying to work on an db in Access 2007 that was migrated from Access 2003 (in fact its been migrated several times starting from Access 97). It executes and runs with no problems in both versions.
The problem is when trying to open some queries (not all) - Access is unable to open the query in design mode and gives me this error :
" is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.
However, as I cant open it - I cant check it. Im pretty sure none of the fields have invalid characters (they do have spaces) and Im not sure how long is too long....
View 6 Replies
View Related
Jul 18, 2013
I'm using Access 2003 and excel 2003.
We currently manually run 5 different queries then copy and paste this data into 5 separate tabs on 1 workbook, I'm trying to automate some of this process if possible.
I am trying to use the 'transferspreadsheet' action within a macro to run a query and post it into a template excel file, using this code:
Trasfer Type Export
Spreadsheet Type Excel 8-10
Table Name (query Name)
FIle Name (FIle location)
Has field names No
Range Blank
----
This does seem to work and puts the data on a new tab on the specified workbook.
However I have a few questions:
1. Can you specify which query gets put onto which tab in excel? The tabs have different fixed names.
2. Can you specify which Cell the data gets pasted into to? As each tab has a set of headers and titles which need to remain.i.e would need to get query 1 to start in cell A4.
3. How would you expand the above out so that it runs all 5 queries, would you just add in multiple transfer spreadsheet actions in the same macro?
View 1 Replies
View Related
Dec 2, 2014
I have 3 cross tab queries that are completely identical with the exception of the field that they pivot. Each field is searching for the same values just in different columns, with the end goal being to get the sum of the values for each pivoted column. I'm wondering rather than having 3 almost identical queries is there a way to use a crosstab to sum the values from each of the three fields rather that having 3 queries which then have to be aggregated in a fourth?
QUERY1
Code:
TRANSFORM
IIf(Count(PT_LEVEL.UNIT) Is Null,0,Count(PT_LEVEL.UNIT)) AS CountOfUNIT
SELECT
PT_LEVEL.INF_YEAR,
PT_LEVEL.INF_MONTH,
PT_LEVEL.UNIT
[code]...
View 2 Replies
View Related
