Hi,
Ive been through several of these threads, but I still cant figure this out.
I have a crosstab query in which I would like to put a criteria. I understood that I have to use parameters for this. But, the restriction I would like to impose on my crosstab query is not directly,but indirectly linked to the variables in the crosstab. I tried to add the other table in order to insert the directly linked variable, but this confuses the whole crosstab (I get duplicate rows).
Is there a way i can fix this?
Thank you
Stacey
I am trying to create a crosstab query, but can get it right.
Table1 = input Table __________________________________________________ ___________ name StartDate date Present A 1201 1201 P B 1202 1205 P C 1203 1206 P D 1204 1207 P E 1206 1209 P
Can get this result with Crosstab Query;
TRANSFORM NZ(First([Table1].[Present]),"a") AS FirstOfPresent SELECT Table1.name, Table1.StartDate FROM Table1 GROUP BY Table1.name, Table1.StartDate PIVOT Table1.date;
__________________________________________________ ____________ name StartDate 1201 1205 1206 1207 1209 A1201 P a a a a B 1202 a P a a a C 1203 a a P a a D 1204 a a a P a E 1206 a a a a P
But am looking to add NA for StartDate > Header Date(1201,1205,1206,1207,1209). See below. Does anyone have a solution or an alternate method
Looking to get this type of results __________________________________________________ ____________ nameStartDate12011205120612071209 A1201 Paaaa B1202 NAPaaa C1203 NAaPaa D1204 NAaaPa E1206 NANAaaP
Is there are way to add print time criteria to a CrossTab query? Everytime I try I get a message "The Microsoft Data engine does not recogonize [Forms]![FrmSalesCategoryByMonth]![Period] as a valid field name or expression". Any ideas?
I have a table that has three columns: Nationality, Gender, and Date. First I had the problem of grouping the Nationality column by Gender. That i resolved by creating a crosstab query. Now, I want to put a criteria on Date field so that when the query is run it will ask for the date input and will only show the Nationality and Gender result for specific dates. But when I write something in Criteria field it shows an error saying: "The Microsoft Access database engine does not recognize "[Registration Date]" as a valid field name or expression"..
I have a table of data going back to 2007 that needs to be looked at on a monthly/quarterly/annual basis. I am able to filter the data when running a normal query by using
Between [Start Date] and [End Date]
in the Criteria section of the Date field. I now need to apply this same idea to a crosstab query. My current set up is:
[Gender]- Group By / Row Heading [Plan Type]- Group By / Column Heading [Pmt Amt]- Sum / Value [Date]- Where / Criteria = Between [Start Date] and [End Date]
I am getting an error message that says:"The Microsoft Office Access database engine does not recognize '[Start Date]' as a valid field name or expression."Am I setting something up incorrectly or is it impossible to use input prompts in a crosstab query like this?
I am trying to have a command button load a form which will filter the records to those pertaining to a value selected in a combo box on a previous form and have the value for the combobox selection display in a textbox on the subform. However, I also want the subform to only display a new record, while still displaying the value selected from the combobox.
Basically, I want to select value on combobox, click button to open form and see a new record with the selected value displayed in a textbox.
So far I have used the wizard to create code for linked fields, and then pasted code for adding a new record, but the two don't seem to go together. Does order matter? Is there a better way of trying to make this work?
I have posted this a number of places and so far haven't been given any useful information. Can anyone help?
I use an Excel interface to retrieve data from an Access file that has approximately 1 million records. I use an MS Query from Excel to run an Access Query to retrieve the data.
I have 16 "Criteria" fields (columns) in Excel that I change with drop-downs. Each criteria column has 2 to 9 values set; a total of 75 values. I pass these criteria fields to Access via a table link in Access.
The criteria of the fields in the query that is run in Access to pull the data based on the table link is updated by using the Values In A List (IN) option.
When I refresh this query in Excel, it pulls the data fine, but may take 10 minutes to run. (I am using MS Office 2003, 1Gig memory). I have limited VBA knowledge. Is there a better/faster way to pull this data?
I am using Access as a front end (GUI) with a SQL 2005 database and have several linked tables. An user encountered an odd error that I cannot figure out.
He created a query that had criteria on a date. When he tries to run it the query will not do anything. If he closes out of the query and does any other operation he gets the "This Action will reset the current code in break mode." error and will have to close out access before he can do anything else.
I'm found some strange work-arounds but want to fix the problem the correct way. Attached are two examples - the first one causes the error the second one is the work-around that returns the desired results.
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
I'm working with a form in Access 2013 that uses the navigation buttons. I'm trying to have my criteria for a query link to a dropdown box on the form. I had no problems doing this in Access 2010 with normal forms, but I can't link the criteria, in the query to the Navigation buttons form box. I'm tried using
Hi all, I am utterly unsure if what I want to do is even possible:
I have two crosstab queries, qryRewCOCredit and qryWrapCOCredit which show the changeover (CO) times for the specified machine when they are NOT zero. (all zero entries don't show up).
There are many cases when there is a CO for the Rewinder on a specific day, but not for the Wrapper, and vice versa.
I want to make another crosstab query which performs a calculation. To keep it simple:
If (RewCOCredit>WrapCOCredit) Then 5-RewCOCredit Else 'WrapCOCredit>RewCOCredit 5-WrapCOCredit
I have a database with a number of linked tables that are linked to tables in different databases (not a back-end).for example, I have table1 that is linked to table1 in K:databasedb1.mdb.table2 linked to table2 in S:datadata.mdb.and so on...
However, recently we have moved all our databases to a new location.
K:databasedb1.mdb is now residing in O:masterdatabase and S:datadata.mdb is now residing in O:masterdata and so on...
I'm now in charge of relinking all those tables to point to the new location.I would do this in linked table manager one by one but we have 100s of tables linked to multiple different databases in different location.is there a way to create a VBA code that will automatically do this re-linking process?
so, 1. find unlinkable tables 2. search its new location under O:master 3. re-link it to the new location
Database names and tables names have not been changed. Just the location of databases.
I am trying to build a Form that will show an estimate (then eventually will be moved to a project if customer and employee aggree to price and project) in a Form F_Estimates is a M_Customers(Customer_ID) (Based on a Table) and thier info in a Subform. Also is the "projected costs" from parts out of the Parts(Part_ID) (Based on another Table) in a second Subform as a list that I need to calculate $$$ in (Dang that still sounds evil and definately NOT understandable even after edit... so)
Here's some basic info
Tables
EstimatesandParts - Table EstimatesandParts_ID : Autonumber Estimate_ID : Number Part_ID : Number
Parts - Table Part_ID : Autonumber PartNumber : Text (not a number due to some part#s have letters in them) PartName : Text Unit Price : Currency Description : Text
Estimates - Table Estimate_ID : Autonumber InvoiceNumber : Text (again can have letters in it) EstimateDate : Date/Time EstimateTime : Date/Time Employee_ID : Number Customer_ID : Number ProblemDescription : Memo
Customers - Table Customer_ID : Autonumber FirstName : Text LastName : Text CompanyName : Text Address : Text City : Text Province_State : Text Postal_ZIPCode : Text (CDN Postal codes are letter num letter...)
you can see the link table in the EstimatesandParts Table
Now I want to use that link to populate a subform in the F_Estimates form
Forms
SF_Customers - SubForm
(all boxes atm are text boxes on this form till I figure out the Parts section then will use same base for this so I can pick any customer in the database to be the customer for this estimate. Also will have ctrl button for making new customer with customer form and a refresh on Focus Gain bit of code)
FirstName LastName CompanyName Address City Province_State Postal_ZIPCode
SF_Parts - SubForm Default View -Continuous Forms
(want it to be a list of parts that I can grab prices and descriptions from then in a bit of code to calculate a cost of parts)
Part_ID : Combo Box Control Source - Part_ID Row Source Type - Table/Query Row Source - SELECT Parts.Part_ID, Parts.PartNumber, Parts.PartName, Parts.UnitPrice, Parts.Description FROM Parts ORDER BY Parts.Description;
(Pulls info from the table Parts for input into a list of parts to be used on that project)
PartName : Text Box UnitPrice : Text Box
(here's where I run into problems due to the fact that the form is not based on the parts table but rather the link table EstimatesandParts so I can't propogate the info to the 2 other text boxes, ps I dont care if they cant be text boxes and have to be linked or some other type I'm not "set" just need to find out how to make it work )
(have tried a couple things to complete this task)
(works AWSOME ... for ONE ROW then propogates the second selection to the first and second and third selection to first second and third and so on ...)
(tried to make control source for the txtPartName to)
=Forms!Parts!Partname
(Doesnt exist .. akkk, cant use ActiveForm either as it doesn't focus on the SubForm but the MainForm ... cry)
(Combo Boxes Select Customer and Employee from list of present ones of each)
SF_Customers SF_Parts
(Both SubForms on the main form)
Now this is an Exerp from my entire Database I like to work on one small problem at a time and I have made this its own little database till I figure out the problem then I will bring the info I learn back into the rest of the database and go from there ...
Hope you can help I have a feeling I will need to make a recordset and go from there but I'm just not able to wrap my head around that for some reason
Thanks in advance for ANY and ALL help that I get from here
I have set up a database that stores actions (i.e jobs). In the table; two of the fields are...'required completion date' and 'actual completion date'. I wish to lookup, by using a query, all of the open actions (those which havent yet been complete (i.e the 'actual completion date' is null)) and then later on all those which are overdue (i.e the 'actual completion date' is null And the 'required completion date' <today....this being the criteria for an overdue action).
However, I have used a form which has a combo box which contains the values open and overdue. When a selection has been made I want a form to display with the results depending on the selection that has been made. I am capable of creating a form based on a query, but am unsure of how to construct the query with the correct criteria based on the option that is selected from the form.
I created a form that allows the user to choose the criteria that they want to see on a report using =Forms!formname!controlname in my query. It works great but I want to also allow the user to choose nothing and return all records instead of limiting them to choose just one type of record. Is this possible? Before I created the form my query had the [Enter parameter] on one criteria line and [Enter parameter] Is Null on the next criteria line and that was working great for my use but I need to create a simple form for other users.
I have data for hundreds of stores. The data was pulled for the top 15 items by store, so I cannot obtain only the top 5 items that I need. How can I query this data to extract only the top (or bottom) 5 Subjects, by store, based on the percentage column?
Everytime a review is carried out it is added as a new record, however, I need the information to be presented in a crosstab so it looks something like this:
Im trying to use crosstab to help me out count how many pages are within a document... for example in my "break" field. there are records that contain D's(d=document), and blank records(blanks=pages)...im trying to use crosstab to help me count how many are within each document....but for some reason, its not coming out right...can anyone help me?
i keep getting this error when i try running my crosstab query... "Too many crosstab column headers(21521)" is it possible since i have so many records...i cant perform this task??
I want to combine the data from 2 tables in a report. I think I need to do this through a crosstab query, but I've no idea how as it needs to calculate it's data.
The row heading needs to be [DateWorked] from the "Hours" table.
Each Column heading needs to be every [StaffPIN] from the "StaffData" table.
I want it to display the number of hours each staff member worked for a perticular day. The "Hours" table holds each staff member's start and finish time. I have a function called workedHours that, given the start and finish time it will return hours worked (a shift can overlap 2 days so DateDiff wouldn't work).
I have a table with the following info: ID, CSR, Option 1, Option 2, Option 3, all the way to option 12.In the Option fields, you can enter a number from 1 - 12. What I'm looking for is a summary report that will give the number of times each number appears for each field. Something like this:
Hello All, Your Help Required. I have send you a Database, in which I have used Crosstab query, I just want when I select the report from switchboard, and enter the datefrom / dateto (fields names) than click the preview report. Required report is open.
I have faced following problems 1-When I have selected the report and click the preview button. Report is not opened. 2-I have used cross tab query and link with the switchboard. But when I have run the query this msg is appeared “Microsoft Jet engine Does not recognized these field(name)”
I have never tried this, it seems easy enough but in fact... I simply cannot find the answer.
I want to display a crosstab query as a (sub)form. The basis of this is that a form requires the columns to be fixed. The crosstab will offcourse grow and grow in the number of columns.
I would like to show the form as a datasheet to support this purpose. I was thinking maybe I can dynamicaly add the (new) column(s) before opening the form, but how do I do that?
I am am wondering if it is possible to create a crosstab query that displays alphanumeric values and not numeric (computational) values.
Ex: Table contains the following details:
Name Branch Bob 111 Bob 222 Joe 333 Pam 444
I want to use a crosstab so I can view the data as follows:
Name Branch1 Branch2 Bob 111 222 Joe 333 Pam 444
Is this possible? I've been playing around with it and it doesnt look doable. Perhaps there is a better way of getting the data into this format? Any suggestions would be greatly appreciated!
I'm trying to create a crosstab query with criteria that refers to a combo box on a form to allow me to filter data before the query is created. However when I refer to the combo box (e.g. = forms!frmSite!CmbSiteName) I am told that access does not recognise this as a 'valid field name or expression'. With a normal select query I dont have the same problem. Is there a way around this?
Admittedly this is my first time doing a crosstab query, but I have both my column and rowe headers set to group by, I kinda assumed this would group them in much the same way a pivot table does in excel
Any help on this would be great
Hmm, doesnt want to keep the spacing in it :( hopefully you can get the idea
