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.
View RepliesI have created a query of 382 records. After filtering I now have a result set of 32 records, which I want to save as another query.
When I select Save As and rename, I get my original 382 record query with a new name.
How do I save the 32 record result set?
I am trying to create a saved export on a table i have. However i want to filter so when i do the saved export it exports the table with the filtered result only.However when i filter my table and export it and save the export. The first export comes out with the filtered results but when i go to my list of saved exports and export it again, it doesnt come filtered and its just an export of the whole table.
View 1 Replies View RelatedI 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.
Hi
I want to save an open form as a data access page. Is there any code to do this and bring me directly to the file location dialog or do I have to use the DoCmd.RunCommand acCmdSaveAs command and then choose data access page from the dialog box?
Also, is there a way of saving a form as a data access page, without opening the form?
Many thanks
AL
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.
Any help would be very much appreciated.
J
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!
Gary
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
View 3 Replies View RelatedI 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
stDocName = "FRM_AWMPonum"
stLinkCriteria = "[ClientID]=" & Me![Combo0] & " AND IsNull(DelDate)"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Command5_Click:
Exit Sub
Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click
End Sub
What changes do I need to make to the code to sort the data?
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.
Thanks
I have a list of dates in my Form1.
When I click on a date it opens Form2 to show that dates specific details.
I would like a macro on Form2 that appends the filtered results to another table.
I think I am just struggling with the references to Form2 in the append query criteria.
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?
View 8 Replies View RelatedI'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 am trying to save specific data from a data entry form to a specific depending on what the user selects from a dropdown/combo box.
So if the user selects "test company from the combo box, that data (from that form) will save to the Test company data 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
[Code] .....
This has most definitely been asked before but I really cant find anything on the forum! Ive created a data entry form which has a save button which the user must click each time to save the record. However how do I refresh the form i.e. empty all data from the previous record without exiting the form. Ive tried to combine the undo macro but that simply deletes the previous record. Any help please!!!
View 3 Replies View RelatedI 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;
I have a form with subform (datasheet) .
On my subform i have one field which shows the
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.
create a datasheet from 2 odbc linked tables which I will be exporting to Excel. The resulting datasheet will have four columns, 'Purchase Order Number' 'Stock Code' 'Delivery Date' 'Unit Price'. The information I require in the datasheet is a unique stock code (one occurrence of each stock code) with the most recent unit price based on the last delivery date for each product. The linked tables contain many Purchase Orders for the same stock codes over a time period of a number of years where the unit price has varied.
View 3 Replies View RelatedI have a form "release_details" having fields date, version, cksum ,comments,labels, and is link to a table,
it has a button "mai"l ,on clicking this button a new form is open which has a button "send mail" on clicking this a mail is sent and pops a message "mail sent".I need when "send mail" is click it should also save the fields of form
"release_details" to the table.
Thanxx
Hello, I'm new to this whole microsoft access program, so I'm learning from scratch. My question is I can't figure out how to save the database, so that I can go back into it later. This is a tutorial that I'm working off of in my online class, so once I exit out and go back in, all the info that I inputed is gone.
View 2 Replies View RelatedIs there a simple way to make sure all fields on a form are filled before the data is saved to the table? I looked into using thew IF is Null Then but for 30 fields that is alot of code. Does anyone know a easier way?
Jim
This is going to be a hard one...
We recently switched over to A2003. As far as my db everything went ok except for one thing.
I have a continuous form that we enter data into. When you open a new record the form only displays one empty row. Because we enter tons of rows that come from an AS400 system I made a button on the top of the form that switches from continuous form view to datasheet view.
Private Sub Label59_Click()
Me.[Part #].SetFocus
DoCmd.RunCommand acCmdSubformDatasheet
End Sub
Within datasheet view I am able to paste many rows at once. However, after the change to A2003 it still "appears" to work. Many rows are pasted in but if you exit the db and come back to the record all of the data is lost and you are back to one blank row in continuous form view.
When a record is added to the continuous form each record is assigned an autonumber primary key. I think that this is where it is failing. Within Access help is the following under "Cutting And Copying Data", "Copy or move records or data from multiple fields to a datasheet": To paste the data as new records at the end of the datasheet, click Paste Append on the Edit menu.
I think this is what I need to do but I don't allow my users access to any menus so I create command buttons or auto running code in place of menu items that they need.
Can anyone think of some code to run a Paste Append of these records or of any other method in which I might restore the multiple paste funtionality I had with A2K?
What's Up,
Got a quick question, is there a way to have a field in a subform update a field on the primary form's table? In other words, enter the data once and it stores it twice, on the subform table and the primary form table.
Thanks for any help.
Trey