in the Criteria because an error pops up saying: "The Microsoft Jet database engine does not recognize 'above_code' as a valid field name or expression.
So I created a select query, call it QueryA', that would let me filter the data. The I created a crosstab query based on QueryA, but the same error message occurs.
I am stuck. If you could help me, I would greatly appreciate it.
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 set up a query to return the records of pupils with certain information, most of this information comes from a subform, (i have set up a form with multiple subforms to make data entry easier for the user) with these subforms many records have multiple subform records (i.e. Joe Bloggs has 3 different Approved hosts and 3 approved drivers) when i try to make a report it wants to print the same data 9 times (because there are 9 differnet combinations i asume) what i want to to is have the approved drivers in one list box and the approved hosts in another list box and have the report print only one!
Trying to create this thing is tying my brain in knots, so I'm back for help. Hopefully I'm missing something basic:I've finally been able to normalize my database by taking it offline for a few weeks, and already the amount of duplicative/erroneous data that has been cleaned up is staggering. Right now I'm trying to ensure that our data is as complete as possible in the following circumstance: tableGroups has PK GroupID tablePeople has PK PersonID tableRelationships creates a many-to-many relationship between the two, via PersonIDLookup and GroupIDLookup. It also has its own Autonumber PK. tableRelationships also has fields that describe the relationship: For example, FormerRelationship, MailingContact, and PublicContact are all boolean fields. What I want to do is get a list of all GroupIDs which do not have any entry that is marked PublicContact. Problem is that some may have Former contacts, or privately listed ones for internal office use only; over time our Public Contacts move, die, quit, etc and sometimes they have not gotten replaced. I'll need to do the same thing for MailingContact as well, but the procedure for one should be the same as the other. No one who is a Former contact should also be listed as Public/Mailing, but I can't promise the data is that clean at this point.The only thought that occurs to me right now is to make one query that shows GroupIDs that do have at least one matching relationship, and then create a Find Unmatched query that relates to that. Is there something simpler with only one query?
I have a union query that will run from the SQL design window but if I try and run it from the database window it crashes Access (2003). Any ideas? SELECT [TestLat]-0.5 AS Lat, tblTestData.TestLong AS [Long], tblTestData.TestTemp FROM tblTestData
UNION SELECT [TestLat]+0.5 AS Lat, tblTestData.TestLong AS [Long], tblTestData.TestTemp FROM tblTestData
UNION SELECT tblTestData.TestLat AS Lat, [TestLong]-0.5 AS [Long], tblTestData.TestTemp FROM tblTestData
UNION SELECT tblTestData.TestLat AS Lat, [TestLong]+0.5 AS [Long], tblTestData.TestTemp FROM tblTestData;
I am having trouble with some code I am using on a query. Part of the query comes from two fields in a form. The rest comes from a table. The query then populates a report. The code I have written works fine. The trouble starts when I try and enter in some code so that if some of the fields in the form is left blank, it just returns values based on the fileds that has data, so ignores any blank fields. I have tried to use examples illustrated in this Forum, but it does not seem to like it. I keep on getting the request to fill in the parameter value. I am sure it is something simple!
This is the code I have entered for the Invoice Date Field: Between [Forms]![Invoices]![Invoice date 1] And [Forms]![Invoices]![Invoice date 2] (this is so the user can enter a date range with the two fields)
I have a query called VOLTEST that is not bound to the form. VOLTEST sums a field in a table from a subform. When the input is finished in the subform and control goes back to the main form, I want the total from the VOLTEST to show in a field on the form that is not a tab stop. Basically, this is just for reference. I have tried to put =[VOLTEST]![SumOftest_value] in the control source of the field on the form but I keep getting "Name?" as the result instead of the sum total. I can run the query in the gotfocus of the first field in the tab order after the subform and I get the correct result in a datasheet view. So, I know the query is working correctly. I just need it to show the result in field on the form.
I am creating a database for a workplace for staff management. i want a query where the user can input a month or a date range and be able to view all of the staff on holiday during this period. I originally had a working query however it ignored staff that were on holiday during this period for example searched 11/2006 showed holidays in november but not staff who are on holiday from before and still on holiday during this time. At this moment in time the fields i have included for beginning the query is BookingID, StaffID, EventName,StartDate,EndDate
In MS Access 97 I have a basic query based on a single table. When I use "Between [Start Date] and [End Date]" as the criteria in a date field it returns incorrect records.
It works correctly on date fields that are not calculated but not on the date field that is calculated. If I type in the actual dates in the criteria (instead of the prompts) it returns the correct records.
Is there a solution to this behaviour? I need the users to be able to input the dates.
Thanks.
P.S. I have tried using the base query as a subquery but I still have the same issue in the second query based on the subquery.
I'm having a problem writing an update query that will look at a name field ie (Dan Wilson Sr) and flip the name to a shortname ie (Wilson Sr Dan) with the criteria based to only flip the name if it contains Jr or Sr. Below is an example of a select query I was using to test before updating my table.
SELECT * FROM [Goodrec - All] WHERE (instr(shortname,' JR')>0 Or instr(shortname,'SR')>0 Or instr(shortname,' III')>0) And instr(shortname,' MD')=0;
This query provides the following result for example, if name is "PAUL D TAYLOR SR" it is flipped to "SR PAUL D TAYLOR." I need shortname to display it like this "TAYLOR SR PAUL D"
:confused: I am trying to help someone with a complex problem (so it seems to me) but I will first ask about what should be a simple thing....
First goal: to COUNT the number of times a TYPE of visit is made. There are several different TYPEs but only interested in tracking 2 of them.
When a crosstab query is created - if one of the 2 parameters are not "met", a blank is returned. I have been reading posts about using NZ and IIf IsNull, etc to get past that - but none of them make any sense to me and the Access help suggestions do not work. Hope someone can make it clear with this information: (can't give more specifics to keep privacy intact)
The SQL was written by Access not by me. :)
Here is an example of the Crosstab SQL (which is using a previous query):
TRANSFORM Count([qryTest2.TYPE]) AS CountOfTYPE SELECT qryTest2.CID FROM qryTest2 GROUP BY qryTest2.CID PIVOT qryTest2.TYPE;
----------- qryTest2 SQL: (Grouping by to remove dups)
SELECT DISTINCTROW tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE FROM tblM LEFT JOIN tblC ON tblM.[M#] = tblC.[M#] GROUP BY tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE HAVING (((tblC.TYPE)="Out" Or (tblC.TYPE)="In")) ORDER BY tblM.CID, tblM.LNAME, tblM.FNAME;
I have a database which among other things records how jobs are received i.e.: Telephone, Email, Mail, Facsimile or Web.For each client I want to identify the percentages of each method of receipt against the total of jobs received and during different time periods.I have created a make table query for all jobs received between variable dates for a client entry of the name of the client and the start and finish dates are required to run the query.
I have a crosstab query set up to count each method of receipt and a final query to work out the percentages using the total from the crosstab query fields divided by the total of all methods.I have a macro set up to replace the table with new data when I want the stats for a different client between new dates, therefore the different methods of receipt may vary for the less active clients i.e.: they may only have telephone and email .
My problem is if I choose a client where we have not received a job by a particular method (say web or facsimile), the last query working out the percentages has fixed names to cover each method but naturally produces an error when it cannot find a corresponding method of receipt. I have experimented with NZ() without success.My question is can I either have preset standard names of the column field in a crosstab query? Alternatively in the query calculating the percentages, can I include code to ignore a non-existent field in the crosstab query.
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 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
I have a report based on a crosstab query and the data changes every time the report is run even though the data is not updated. Can anyone shed some light?
I need help with a crosstab query ( :eek: ). I would like the column headings to be the last 6 months, the row headings to be billers, and the data in the middle to be both the date that a payment was made (falling within the month headings) and also the amount paid in that payment. Sometimes there might be more than one payment to a biller in a month or there might be no payments to that biller in a month. All of the raw data needed is in one table. I have read about crosstab queries, tried to use the wizard, and looked at examples but I can't get it to work:confused: . Could someone show me how this should be written? :)
I need a query which displays data as a crosstab query would but gives me the capability to edit data in the query. Basicaly, I have a Resource column, a Month column, and an Allocation column (among others). I need the months to appear in columns and the allocations to be summed by month. (Months are never repeated though, so it's not really a sum...) Can anyone help me out on this?
My crosstab query doesn't take the textbox value from the form. It says it doesnt recognize [forms]![frmMain]![txtEndDate] as a valid field name or expression. How can I solve this?
TRANSFORM Sum(tblDowntimeHrs.downtimeHrs) AS downtimeHrsOfSum SELECT tblEquipmentType.Equipment_Type FROM tblEquipmentType INNER JOIN (tblCategory INNER JOIN tblDowntimeHrs ON tblCategory.category = tblDowntimeHrs.category) ON tblEquipmentType.type = tblDowntimeHrs.type WHERE (((tblDowntimeHrs.date)>=[Forms]![frmMain]![txtStartDate] And (tblDowntimeHrs.date)<=[forms]![frmMain]![txtEndDate])) GROUP BY tblEquipmentType.Equipment_Type PIVOT tblCategory.category_description;
in my select query with the same table, it works fine:
SELECT tblEquipmentType.Equipment_Type, tblCategory.category_description, Sum(tblDowntimeHrs.downtimeHrs) AS downtimeHrsOfSum FROM tblEquipmentType INNER JOIN (tblCategory INNER JOIN tblDowntimeHrs ON tblCategory.category = tblDowntimeHrs.category) ON tblEquipmentType.type = tblDowntimeHrs.type WHERE (((tblDowntimeHrs.date)>=[Forms]![frmMain]![txtStartDate] And (tblDowntimeHrs.date)<=[forms]![frmMain]![txtEndDate])) GROUP BY tblEquipmentType.Equipment_Type, tblCategory.category_description;
TRANSFORM Sum(VoosAeronTbl.ATE) AS SomaDeATE SELECT AeronaveTbl.Registo FROM MissaoTbl INNER JOIN (AeronaveTbl INNER JOIN VoosAeronTbl ON AeronaveTbl.MatriculaID = VoosAeronTbl.MatriculaID) ON MissaoTbl.MissaoID = VoosAeronTbl.MissaoID GROUP BY AeronaveTbl.Registo PIVOT MissaoTbl.MISSAO;
when I try to select between dates set on a form field like this...
TRANSFORM Sum(VoosAeronTbl.ATE) AS SomaDeATE SELECT AeronaveTbl.Registo FROM MissaoTbl INNER JOIN (AeronaveTbl INNER JOIN VoosAeronTbl ON AeronaveTbl.MatriculaID = VoosAeronTbl.MatriculaID) ON MissaoTbl.MissaoID = VoosAeronTbl.MissaoID WHERE (((VoosAeronTbl.Data) Between [forms]![ContAnFrm]![DataInicio] And [forms]![ContAnFrm]![DataFim])) GROUP BY AeronaveTbl.Registo PIVOT MissaoTbl.MISSAO;
...I get the follow error
microsoft jet database motor does not recognize [forms]![ContAnFrm]![DataInicio] as a field name or valid expression
I am trying to simulate the data as it is presented in Excel, but with Access. This might sound stupid, but I have to draw a chart in Excel by Automation from the data I retrieve in Access. So far, I have made a query, which returns the following (see SourceData.jpg (http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=10335&stc=1))
What I have to do next is present the data as follows (see ResultData.jpg (http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=10336&stc=1))
How do I do this in SQL? I have a book at home which I am sure explains this in the form of a Count function, or a crosstab query, but will only be able to check it on Thursday. Could somebody maybe suggest something in the meantime?
What I do know, is that the bottom “Summe” row cannot be done by one query. But the column “Total” must be able to be done with a Count function. (Or am I wrong?) :confused:
Its so hot and humid here and no air-con that I cannot think, so please help!!!
