I'm new to Access and am trying to work out the best way to achieve the following.
I need a way for users to be able to filter the data which is displayed by forms or output in reports. For example, suppose that a user wants a list of all customers in either of two specific towns with a specific date of birth.
Obviously this could be achieved using a query and setting the criteria of the towns column to be:
"Anytown 1" or "Anytown 2"
and setting the criteria of the Date of Birth column to be the specific date required, e.g.
"08/08/2006".
However, so far everything about my database has been very user friendly with forms for data entry and presentation, so I don't really want to force my users to start creating their own queries.
The alternative I'm thinking of is as follows. Provide a query to pull together the required information (from various tables) but without any specific criteria. Then create a form with a field for each of the columns in the query, and allow the user to type their critieria into these. Then, place If statements as the criteria of the query to check if there is anything typed into each box on the form and, where there is, set it as the critieria for the query, then run the query.
With the above in mind, I have two questions.
1) Is there a better, more "standard" way to achieve a system of allowing users to create customised filtered data without requiring them to write queries?
2) In some cases, e.g. like with my specification of a town above, it would be good if the user could select the town they want to filter for from a drop-down combo box, rather than typing it in and risking entering details incorrectly. However, this would obviously allow them to select only one town, and not two as was required in my example. Is there an equivalent to a combo box which would allow multiple selections?
Thanks in advance if anyone can help me - and please remember that I am new to Access so whilst I want the best possible solution, I need a plain English explanation!
I have a very simple database used by 3 users at a time. It is split into BE and FE. One user accesses the DB with queries only so I have created a DB with the master file linked - and literaly nothing else, She will then create and save all of her queries here. But - I want to keep her from accidently updating the file from here. I can't seem to find a simple solution to simply not allowing her to update. Any ideas? Thanks - Dika
I have a database that allows multiple entrys of the same information. I also have a report based on a query that pulls out said data.
I just need my query to pull out every record regardless if it is indentical to a previous one. I found under queries > design > properties there are two options unique values and unique records. According to a source online, these are what control allowing duplicates through, but I have swapped them around with no luck. Currently they are both set to "no".
Is there some other setting I have to switch up in order to allow the duplicates to come through?
The first relates to the fact that in the crosstab is a field that is set to Yes/No. It appears as a textbox with a value (0 or 1) in it. I want to replace this with a tickbox but when run neither the textbox nor the tickbox will allow any changes. Is it possible to set this to be editable? I cannot see how this would relate back to the actual data table but if it is possible then this should become clearer.
The second issue relates to the fact that the crosstab obviously creates the number of columns in relationship to the maximum number of values of the relevant field. So, I have a field that can have values from 1 to 15 but in most cases the actual value runs no further than 9 and 10. So for those records where the maximum number is not being used I want the unused tickboxes NOT to appear. Again, is this possible.
In connection with the second, if I have (as I do) this group of 15, which is in fact made up of 3 groups ranging from 1-8, 1-4 and 1-3. So number 8 may not be required in the first group and numbers 3 and 4 in the second. I would prefer there not to be a strange gap between the datasets when run. Is it possible to set up a continuous form so that all the potential boxes are in place, that I run code on load that puts the values in from the query, hiding the end tickboxes and so collecting the groups together - then allowing the changes to be detected through code and re-assigned to the table?
I need to know if I can store data in more than one field, without allowing Duplicate entries.
Example: We have to send out letters with a claim number on it, sometimes we need multiple claim numbers on a letter, up to about 10 claim #'s is the most I've seen thus far.
We mail merge into Word to create the letters.
However, these claim numbers cannot be duplicated within the database and in order for this to work with the mail merge, I need several fields to store the claim numbers.
To be more specific. I probably have a bad data structure as I was kind of rushed and a little inexperience with putting the database together. It's for work and sometimes management just doesn't want to wait, they are so negative.
In brief, most everything is in 1 table and I know from reading this forum that I shouldn't do that, but that's another story.
Further, a claim# is tied together by the same claimant, the relationship code is what separates the claimant with the family, the insured id# is used for all claimants within the family.
You can also tie the claim# to a provider id#, some providers such as clinics and large hospitals have multiple providers that use the same provider id#, so a suffix code is used to separate the providers within that same #.
Please feel free to ask additional questions and I will give you the additional information you need to help me. Thanks again.
P.S. - Last year I created the database and this year I am improving it, so expect more posts from me in the future. I really appreciate all the help I get from everyone here!!!!
I need to know if I can store data in more than one field, without allowing Duplicate entries.
Example: We have to send out letters with a claim number on it, sometimes we need multiple claim numbers on a letter, up to about 10 claim #'s is the most I've seen thus far.
We mail merge into Word to create the letters.
However, these claim numbers cannot be duplicated within the database and in order for this to work with the mail merge, I need several fields to store the claim numbers.
I have a simple table with two fields in that table called:
1. BankName 2. BankNumber
Each bank has a bank number. For example Bank XYZ and all of its branches have the same Bank Number 123. There are 5 banks I have listed in a combo list under the BankName field. I can also type in a different bank in that same field if it is not listed in the combobox list.
Now, I want the BankNumber to automatically populate based on what I choose under BankName. If the BankName is manually entered (for banks that are not in the combobox), or if the BankName field is blank, I want the BankNumber field to be able to enter a number manually.
For example, if I go to the BankName field and under the combobox I select Bank XYZ, i want the BankNumber field to automatically populate as 123. If the BankName has a bank name that was manually entered, i want BankNumber field to allow me to manually enter a number.
Thanks for ur help. I couldn't figure this simple request out.
I'm trying to build a form based on a table where the user can also enter data to update the table but with data fields not originally in the table. Below is a summary of what I have and what I need:
1.) A file that will be uploaded daily into a Table (ie name Denials extract table). 2.) A form bound to table Denials extract 3.) Additional fields that will be updated by users that is not included in this table b/c they are not available in the system we are getting the data extract from. 4.) The user will look up a key field that will allow them to review it in our system. Once they "work" the record, there will be multiple fields that they will update with this information that are not in the bound recordsource (table Denials extract).
Do I create another table with these fields even though they will be blank at first and make them a subform that will have a "primary key field" that will link the main form with the subform? Will they need to "update" a whole new table? Not really sure how that would work since they will be updating fields not in the bound table.
I have a form that a user fill out to populate fields in my database...how can I make it so that user MUST fill in certain fields before they are allowed to save data?
I need to stop users from taking shortcuts when entering data, and skipping a lot of fields. So i would like to be able to specify the minimum fields, and an error message must show when they try save incorrectly to alert them to this.
I have an Access 2010 database where we have a SQL Linked Table with a column that is nVARCHAR(20) Not Null data type. We have created a form for data entry. Currently when the user tries to erase a value or choose not to define a value we get the following error.
"You tried to assign the Null value to a variable that is not a Variant data type."
This field should accept a blank value "" as the user may not want to set the value. We do not have control over the DB schema, so how can I work around this issue in access?
Hi everyone, I am new to writing queries in access and I have got stuck with this one.
I have 2 tables containing similar data from 2 different paradigms.
The main columns I am interested in are not unique - labelled ID and Delta Ex (a score). The same ID values can occur more than once in the same table, so I am interested in calculating the average Delta Ex score for each ID value in each table.
I then want to ask what ID values occur in both table 1 and table 2 and then calculate the average score of each common ID values across both tables. i.e. ID 1 score = avg(table1.delta ex, table 2.delta ex).
I then want to rank the top 50 scores.
So far I have the following to find common ID values, with a score above a threshold set by the input string threshold. These are ordered with in descending score value.
SELECT [MODEL 1].[ID], [MODEL 1].[Delta Ex] FROM [MODEL 1], [MODEL 2] WHERE ((([MODEL 1].[Delta Ex])>[threshold]) And (([MODEL 2].[Delta Ex])>[threshold]) And (([MODEL 1].[Accession])=[model 2].[accession])) ORDER BY [MODEL 1].[Delta Expression] DESC;
With a second query I can select the top 50 scores:
SELECT TOP 50 [query 1].ID, [query].[Delta Ex] FROM [query 1] ORDER BY [query 1].[Delta Ex] DESC;
However, I can't for the life of me work out how to obtain an avg score for each ID value.
Ok access noob here and a first post as well...I have to filter a table that has children and teachers.I made a form with a combo box of teacher names then filteredon the teacher name.in the subform I have to create a matrix of the children thatare assigned to that teacher.it would be something like thisteacher date child name start endthe teacher and child name are in tblmainthe date start and end values have to be in tbldata1so the beginning they want to choose a teacher and a dateso I shifted and put the teacher in a combo box and tied it to nothingbut the subformso combo box teacher and field datelinks to subform are teacher;datecombo box(teacher name)==> Ms. Eve text box(date)==> 01/01/06but they want a filtered list on teacher so the subform would have to beteacher date child name start endMs. Eve 01/01/06 mary lamb Ms. Eve 01/01/06 billy bullMs. Eve 01/01/06 bob jonesthen they could put in start and end for each childI don't see how I can get the child names filtered thru a query (thisI can do) BUT then to be able to edit the fields and put this datainto a new table.IS there a way to do this?a big help for any ideas or pointers as I am lost on this
I have a button that opens a second form and shows filtered data based on the selection of a ComboBox from the initial form. That all works nicely, I now wish to sort the Data in the new form by [POID].
The code I'm using looks like this;
Private Sub Command5_Click() On Error GoTo Err_Command5_Click
Dim stDocName As String Dim stLinkCriteria As String
I have a large database with over a million entries. I am trying to filter certain years (04-12) and certain states (western US). After the two filters are on my entries are down to 328,000. Now, I want to save the 328,000 to a new database.
uh.. I guess the title pretty much sums it up... Is there a way to enter data in a text box or something once, and have it applied to all the filtered records?
I have it set up so that we can sort by project number, and it displays only the invoices that havent been assigned to a bill (we recover the expenses form our parent company). I dont want to have to enter the same bill number to each of the filtered records individually.
I have a centralised database that imports data from several other databases at the click of a button to produce a "global" query of data.
This is presented as a datasheet form which I am then able to filter. This is a subform to my main form.
I have a button on my Main form with a macro to ExportWithFormatting to excel. I was under the impression that exportwithformatting exported the filtered data.
How to export my filtered query data to Excel and not the whole query data?
I have a PIVOT table that is used to select courses. I have some vba code that exports this to pdf, but it exports the entire data not the filtered data. Is this possible just to export the filtered data?
I'm very new to Access and I'm attempting to write an expression in a query that will calculate the % of the count of "Exchange" field (Exchange is a text field and is grouped and the count based on each unique name) where the total count is based on the filter where "Group" = 'FS' or 'S'.
The below seems to work, but there is a better way of going about this (especially if I have to add more filter criteria). I added a pic of the query I'm trying to build.
PercentofTotal: (Count([Exchange])/DCount(Count([Exchange]),"[Holdings]","[Asset Group] = 'FS' OR [Asset Group]='S'"))
My company wants me to run a high level report which ask for three filters. However as its high level they want it should run with a click instead of choosing filters from three drop down ...
As this report comes out as a graph I can't use report wizard to run tabular report. Any smart way that they click a button and get filtered data in graph form.
I have an Excel file with a name range "DBIAS" which identifies all database data.
Then I have an Access file with a form to import that database (better, that named range) into an Access table. While importing, I have to filter some records or grouping by some field.
I cannot run correctly a VBA code to get data (filtered and/or grouped) from that name range and save those records to an existing or a brand new table.
I could get those data as DAO.recordset and printed out with "Debug.print" on immediate window, but I cannot complete the final step: writing those records to a table.
I am currently working on a form that exports a query that changes after a user set filter is applied, and am able to get the filter to apply and the query to export. The problem I am having is that the worksheet that the query is copied into retains all previous data, and if the earlier query export included more records, they remain as they were, is there any way of getting them to be blank.
I want to export my query onto a worksheet that has current data, need to delete current data or delete worksheet so that only the selected data is shown.
My code currently is:
Private Sub Command67_Click() Dim strWhere As String Dim strFile As String Const strcStub = "SELECT NomT.shkFirstName, NomT.shkSurName, NomT.shkCompanyName, NomT.shkAdd1, NomT.shkAdd2, NomT.shkPostCode, NomT.shkRegion, NomT.shkCountry, NomT.shkAdd3" & " FROM NomT" & vbCrLf With Me.FilterSub.Form
I have a suppliers table and a products table. Two forms, Stock form with combobox to list Suppliers and a sub form with combobox that lists Products.I want to select a supplier from the Stock form and then the combobox in the subform to only list products directly sold by the Supplier.Have dabbled in SQL as follows:
SELECT Products.ProductID, Products.ProductName FROM Suppliers INNER JOIN Products ON Suppliers.SupplierID = Products.SupplierID WHERE ((Stocksubform.SupplierID=Stock.SupplierID)) ORDER BY Products.ProductName;
productId ( which is filtered from combo boxes on main form) . productId contains two columns 1 bound column(id) ( hidden with width 0 ) 2 column (desription) ( shown with width 2 )
Now after entering the data when i come back to the form again . It hides the data from the productId field on subform , because data in combo box (on main form) doesn't match. Although the row is showing up , only the text in field (productId) is hidden.
Now when i change data in combo box on main form to match the ProductId , then the productId field text shows again. after changing combo box it hides . What i need is the existing data should not hide when i change the combo box selection.
see pic 1 where product matches and pic 2 where not. please note i am using master child relationship on the form/subform.
I have have 2 feilds in my database, there can be the same value in one feild but not both.....for example say NAME and ADDRESS i can have the same NAME for more then one entery and i can have the same ADDRESS for more then one entery but i cant have the same NAME And the same ADDRESS....anyone know how to do this? :confused:
