I was wondering if there was a way to setup a Reversed Crosstab query:confused: . I am running a query which is pulling data from an import table. But the one thing is the data that I import is already setup like a Crosstab. Ex:
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
This is my first post and i searched to try and find an answer on the forums but can't seem to find one so thought i'd ask to see if anyone is able to help. Sorry if this has been answered before and i've been a bit thick not been able to find it. anyway here's my question...
I have 2 fields i want to check in my database DOB1 and DOB2 (date of birth of applicant 1 and date of birth of applicant 2.
i've written a query that looks for any records where the data in DOB1 and DOB2 match the data in another record for DOB1 and DOB2. i hope that makes sense. this is what i've written.
In (SELECT [App1 DOB] FROM [Data] As Tmp GROUP BY [App1 DOB],[App2 DOB] HAVING Count(*)>1 And [App2 DOB] = [Data].[App2 DOB])
this seems to work but please feel free to correct me.
Now the part i'm struggling with is trying to find duplicates where the fields have been reversed. eg App1 DOB and App2 DOB are the same as App1 DOB and App2 DOB in another record but applicant 1's Date of birth has been stored as Applicant 2 and Applicant 2 is now stored as Applicant 1. i hope thats not to confusing.
Basically i'm trying to check for duplicate mortgage applications in the database but sometimes applicant 1 and applicant 2 are reversed.
I'm running pro bono a linked file database for a wildlife hospital in Australia. I have a Form in which there are several fields for vets to enter data about a wildlife patient. One of these is the Collection Plan (for the patient). This is a memo field from the TBL_Accession. The Collection Plan is written in straight English on the input form, but the data entered into the table is completely reversed, reading "etinuer ot ovra siht pu/p ot ronod". Translation for the line here is "donor to p/up this arvo to reunite".
I have a data entry form which have a date field.This field retains the existing date after the record is saved with vba after update event.
Private Sub Form_AfterUpdate() Me.ProductionDate.DefaultValue = Chr(35) & Me.ProductionDate.Value & Chr(35) End Sub
Almost after a year of perfect working i am facing a problem.When i save the record , in date field my date and month values are reversed if date is between 1-12.although my database stores perfectly valid date. for example if date is "10-09-2014" (dd-mm-yyyy) after saving form date field becomes "09-10-2014". but if date is "13-09-2014" The date form field retains this value .
New user of Access 2003 has inadvertently clicked "yes" to "Block Unsafe Expressions" and now can't even click a simple command button to print a report. :confused: Db has not a single macro, nothing fancy, mostly wizard code.
Other folks logging onto that PC can still function fine.
I'm assuming it has set him into Sandbox mode & we need to back out of that setting. I've searched a ton of GHudson posts on this forum, read everything Jeff Conrad links to, looked at the following MS articles.
Understand how to enable or how to disable Sandbox mode http://support.microsoft.com/default.aspx?kbid=294698
Enable or disable sandbox mode http://office.microsoft.com/en-us/assistance/HP010504321033.aspx
One article issues a dire warning about dangers of setting registry keys and the other doesn't seem to fix the problem at all. We're in a severely locked down environment and cannot download anything or change system settings -heck, can't even run ScanDisk anymore.
Will re-installing Access solve this, or does anyone know of a solution that will work given our limitations? Any help is appreciated!
: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!!!
Is there something I can put into the criteria of a date box to only use the last 6 months. I dont want have have to do this type of criteria: >12/1/2004. I want it to just use information from the last 6 months no matter what the current month is. If anyone knows what I am trying to say, please help me out! Thanks
I have a crosstab query where my column headings are the names of the months based on invoice dates. what i currently have: Month: MonthName(Month([AnnualIncome]![InvoiceDate])) so this spits out April May and June in the column headings. The problem is that it is sorting it alphabetically, not by the numerical value - so my column headings are coming out as april, june, then may. (I assume once I have july that will come in between april and june.)
Hi, I have a crosstab query, but for any product that has no entries on a certain date a NULL value is put in its place. Is there a way to have a 0 display there? Example of NULL:
DatePRODAPRODBPRODCPRODDPRODE 08/01/200513633 08/02/2005112643 Aug Total:216276
See how there is a NULL for 8/1 under PRODB? I would like that to be a 0
A crosstab query gives the totals for students in particular classes per the Class they belong to, i e
College A – Class1 4, Class2 12, Class3 17 College B - Class1 12, Class2 124, Class3 12 College C - Class1 5, Class2 16, Class3 7 College D - Class1 4, Class2 13, Class3 9 Etc etc
Experts, is there a way to an overall total of the lot, ie
Class 1 30, Class 2 48, Class 3 28, Class 4 etc etc
I have a crosstab query with the following column which calculates the number of days an employee took to perform a task. Problem is that it is also counting weekends and holidays.
Field: LogDate Table: tblWorkLog Total: Count Corsstab: Value
Is there anyway to exclude weekends and holidays from that value?
I have a table, tblHolidays, with the field, HolidayDate. Is there a function that I can use like the WeekDay and use that with the HolidayDate field so that I can only get the Number of Work days?
***Addition***
Sorry I would also like to clarify something I have only one date field. What's happening is the user logs in and inputs what he did for the day, and the date is logged.
So the crosstab query sums up those days, but I would like to exclude weekends and holidays.
I have a table with 20,000 names in column 1. Columns 2-8 are Yes/No questions. I would like to set up a query to see how many people answered Yes for any combination of questions. I think a crosstab is needed, but I just can't for the life of me think how to do it. Any ideas?
I am trying to create a crosstab query that counts how many records have events scheduled to start between each hour. The times are random during the hour and when I tried to create a crosstab I got a header row for the actual times.
I tried to do this manually, see attached JPGs. This does not work and returns all times for the date as you will see. In the JPG that time period should show 41 for Oct 19. Now if I do a select query and put the Between statement in the criteria no problem it shows only the 41 records however I know it shouldn't take 24 queries to get this done.
