I have a crosstab query. There is a field for row headings that specifies QUARTER (Q1,Q2, Q3,Q4) and another field for row headings that specifies YEAR(2015,2016,...etc)
I want to be able to have the user input the criteria in to a box and filter the crosstab query to then display the filtered results.
Right now on my column headings I have MONTH.
When I try to reference the form in the criteria, I always get the error
"The Microsoft Database Engine does not recognize ..... as a valid field name or expression"
Does this mean that you can not filter crosstab queries?
Below is the code I have for a crosstab query. This shows me totals for System Groups by Month. The columns are months. This also gives me a grand total accross all of the months. What I would like to do is get a Top Ten based on the GrandTotal. Any suggestions on how to accomplish this? Thanks for all help.
PARAMETERS [Forms]![Queries_ReportsFRM]![StartDateTxt] DateTime, [Forms]![Queries_ReportsFRM]![EndDateTxt] DateTime, [Forms]![Queries_ReportsFRM].[FaultCategory] Text ( 255 ); TRANSFORM Sum([Trends-1-3TON-MONTH].Totals) AS SumOfTotals SELECT [Trends-1-3TON-MONTH].SystemGroup, [Trends-1-3TON-MONTH].FaultCategory, Sum([Trends-1-3TON-MONTH].Totals) AS GrandTotal FROM [Trends-1-3TON-MONTH] GROUP BY [Trends-1-3TON-MONTH].SystemGroup, [Trends-1-3TON-MONTH].FaultCategory PIVOT [Trends-1-3TON-MONTH].YearMonth;
Below is the code to my crosstab query. I want to sort descending) on the GrandTotal. I can apply a sort on the results of the query but cannot sort in the query. It is my understanding that this is due to this being a crosstab query. Has anyone any advice on how I can somehow automatically descend sort on the GrandTotal Column? PARAMETERS [Forms]![Queries_ReportsFRM]![StartDateTxt] DateTime, [Forms]![Queries_ReportsFRM]![EndDateTxt] DateTime, [Forms]![Queries_ReportsFRM].[FaultCategory] Text ( 255 ), [Forms]![Queries_ReportsFRM].[SystemGroupProblem] Text ( 255 ); TRANSFORM Val(Nz(Sum([Totals]),0)) AS SumOfTotals SELECT [Trends-1-3TON-WEEK].SystemGroup, [Trends-1-3TON-WEEK].FaultCategory, [Trends-1-3TON-WEEK].Problem, Sum([Trends-1-3TON-WEEK].Totals) AS GrandTotal FROM [Trends-1-3TON-WEEK] GROUP BY [Trends-1-3TON-WEEK].SystemGroup, [Trends-1-3TON-WEEK].FaultCategory, [Trends-1-3TON-WEEK].Problem PIVOT [Trends-1-3TON-WEEK].YearMonthWeek;
I have a table with 3 fields Line address, Availability, Relevance Date Every month i put in the data given and i have back from june (so i dont actually have 12 months.
At the moment i am grouping by line address(Row header) and datepart("m",[relevance date]) - (column header) and have the availability as a value and sum. Because i only have data from june it only gives me 6,7,8,9,10. How do i get it to always display 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 and just put the availability into the relevant months and put nothin in the others.
Current SQL: -
TRANSFORM Sum(Monthly_cleansed_data.Availability) AS SumOfAvailability SELECT Monthly_cleansed_data.[Line Address] AS LineID FROM Monthly_cleansed_data GROUP BY Monthly_cleansed_data.[Line Address] ORDER BY Monthly_cleansed_data.[Line Address] PIVOT DatePart("m",[Relevance Date]);
I have set up a query and a Crosstab Query based on that Query. This works fine, but I am trying to filter the data in the crosstab query to only include the account info for the account the user is viewing.
My crosstab looks something like this :
Account # Result 1 Result 2 Result 3 Result 4
I can static a criteria in the base query (Criteria Account = "1") and it works, but I need the criteria in the query to change based on the record the user is viewing on a specific form. I tried to reference the form ; Forms![formname].[account] but the crosstab query will not run that way. Is there a way through code to change the criteria in a query for a specific field ?? Or do I need to create a recordset and use SQL statements ?
I have set up a query and a Crosstab Query based on that Query. This works fine, but I am trying to filter the data in the crosstab query to only include the account info for the account the user is viewing.
My crosstab looks something like this :
Account # Result 1 Result 2 Result 3 Result 4
I can static a criteria in the base query (Criteria Account = "1") and it works, but I need the criteria in the query to change based on the record the user is viewing on a specific form. I tried to reference the form ; Forms![formname].[account] but the crosstab query will not run that way. Is there a way through code to change the criteria in a query for a specific field ?? Or do I need to create a recordset and use SQL statements ?
When I query my database I want to return groups of records. The groups differ from one another based on one field (Employee ID) -- So one group with Employee ID# 1 may have 2 records and another group with Employee ID# 2 may have 5 records, etc. My question is: What is the best way to display these groupings and allow the user to scroll or navigate thru the different groups easily? Any help is appreciated.
I have been doing a lot of searching on the forums, and I can't seem to find an answer to my problem.
I have a form that uses a query to create an e-mail. This e-mail lists all of the different tests to perform on a sample of a product. My table set up is as follows:
tblProducts ProductID ProductName
tblTests TestID TestName
tblSamples SampleID ProductID TestID
The purpose of this is to send out a sample of a specified product and perform 1 to XX tests on it.
If there's a post out there that addresses this, please point me in that direction as I've had no luck.
I have my queries listed in a table. On my form I have them listed in my combo box. The user selects the query they want and hits the command button "Run". I would like to show the selected query in a subform on the main form and then give the user the option of selecting another command button to export the query to Excel.
I thought it would be as simple as setting the recordsource of the subform to the query name in my combo box, but that doesn't work.
Current code to run query in separate window:
Command button "Run" Private Sub Command9_Click() DoCmd.OpenQuery Me.Combo2.Column(1)
Attempt at getting what I want:
Privat Sub_Command9_Click() Forms!frmQuerySelection!sfrmQuery.Recordsource = Me.Combo2.Column(1) or perhaps sfrmQuery.Recordsource = Me.Combo2
I have created a query with parameters keyed in from unbound textboxes on a form. Does anyone know of a way to display the results (queried) in the sub-form of the same form (main) the textboxes reside? I have the query set up to create a table but when I use that table (or the query itself) in the subform I get the error message:
"The database engine could not lock table "tablename" because it is already in use by another person or process."
This seems appropriate because I am querying for results, but is there any way to get the results of a query into a form, rather than the more common pop up datasheet? I have tried several macros to open and close the tables - queries - copied tables ..re-opened forms....etc, but can't get it to work.
Thanks, for any help, I have spent some time on this one!!!
Hi, can anyone tell me how to doubleclick on field (on a form) and it opens up a form that shows bounded filtered results. I have a sub form which has details supporting summary numbers. I want my user to doubleclcik on the currency field and the detail supporting that sum total, pops up automatically. Any help please? :confused:
Hi, I have created a form (Form1) based on a table (Table1) and also a Query based on this table (Query1). I calculate a field in the query (Expr1: [column1] + [column2]) Now i have created a text box on Form1 that should display Expr1. I know I could also calculate the value directly via the expression builder, but I would prefer to simply display Expr1 from my query. Which is the syntax I have to use in the Expression Builder? I tried "= [Query1]![Expr1]" but it does not work. Thanks for your help Tigrou
I am trying to allow end-users to only use buttons to navigate and display records in my database (so they don't know access is in the background) I have query that runs when they press a button but after they run it there is no way to get back to the main interface screen i.e. there is not a place to put another button and close the query. I was looking into creating a form to display the query results in datasheet view but it is not appearing the way I want. I inserted a subform and the results got better but I also do not like the way it displays because the user can see the subform, simple solution to get my data to display in the form so a user can view the results similar to datasheet view?
In my form I have a dropdown box. There are 7 values and based on the value it will run that query in sql server. I would like to have the query open in the form instead of opening up on a different tab. Currently on the command button click there are a bunch of if statements depending on what is selected on the combo box. Each value has a query associated with it because there are 7 different sql server DB's. The code is slightly different with each query.
I have a form with a subform (FindBuffer_Form) which I built from a Query (FindBuffer_Query) within the query criteria I have the following (Like "*" & [Select the main Batch Number] & "*")in order to search a particular number.
I want to be able to run the Query from a command button on the main form and have the results appear in the subform, If I have the Source object of the subform set to Query.FindBuffer_Query I get prompted twice to answer the Criteria before the form opens, after the form opens the subform is generated as per the search criteria.
Ideally, I want the form to open with no data in the subform run the query from command button and have the result in the subform.
We have a button running a SQL query via VBA, how can I make it so the results doesn't show up in a table/preview?Also, I know it's for the SQL forum but how can I make a text box to show the results of a query to display it on screen?
I've created a database to keep track of all our Microsoft licences for the company. Just to make sure we're fully licenced, to allocate where needed, and to help reallocate when we replace a pc.
I have 2 tables, 1 hardware (listing PC ID,location etc.) and 1 software (listing software type, software licences, and to which PC ID allocated).
What i'm trying to do is filter the pc's missing either an Office licence or a Windows licence or both, so i can base a report on the results.
I made a form that lets users search for records in a database and displays them in another form. In that form I've made 3 buttons to let users narrow down the results to show only certain items within the search results.
Now, 2 of the buttons show specific items and the third one toggles the form to display all the records. So lets say I'm filtering records by whether or not they contain the words "car" and "truck." If a record has "car" but not truck and I click the button to sort by "truck" then the search result goes blank, which is ok. But if I click "car" or "show all" to display everything again then the search result stays blank.
This doesn't happen with records that contain both "car" and "truck."
For every button I have something like this: DoCmd.OpenForm "AdminSearchResults", , "SOW", "VendName = '" & VendName & "'", , , "'VendName'"
I know my explanation may suck, but if anyone can help me, that would be great, lol
I am using a form in which i am filtering the results in the list box based on the textbox value. I am dynamically switching 3 row sources for the list box.
My Need is that the results produced in the listbox should get filtered again when typing in the second text box i.e based o the country name.
I am trying to create a query that filters results only outside of 1 standard deviation of the mean. Is there an option for this in the criteria spot? I'm fairly new to Access and I'm not sure if I can reference a particular cell in the criteria.
Is there a way to search for a term within a form and goto a record that matches that term assuming there was only one matching result (Unique ID, for example).
But NOT filter the results so that user can still navigate as usual after the search has been carried out.
