Where the values (12, S) are the number of hours worked for that staff member on that day or a summary of what they did.
I have created a Function:
WorkedHours(DateWorked, StaffPIN)
This returns the number of hours worked by a saff member on a perticular day as an int.
I know I need to use a crosstab. My 1st attempt had [DateWorked] as the row heading, [StaffPIN] as column heading and the expression "expr: WorkedHours([DateWorked],[StaffPIN])" as the value with 'expression' as the crosstab property. It kept throwing the error "Data type mismatch in criteria expression". There are no Null fields in the table it's performing the query on.
For my 2nd attempt I created a simple query that had the fields I needed for the crosstab plus the extra field "Hours: WorkedHours([DateWorked],[StaffPIN])". I could then use a crosstab query on this simple query to create something aproaching the table I need. This new crosstab had [DateWorked] as the row heading, [StaffPIN] as the column heading, [Hours] as the value with 'Last' as the crosstab property.
1. Do I have to use a seperate query to calculate the WorkedHours() or can I do it in the crosstab?
2. I don't want the crosstab to average, sum or count etc. the data. I just want it to display the hours worked but I have to choose something in the crosstab properties. What do I select? (I selected 'Last' and it seems to work but I'm not sure why)
3. How do I get it to display something other than the number of hours worked for certain shift types? (I have all the shift types in a table with a flag for the ones that need to be calculated, the absence of this flag would mean it needs to show a summary eg 'S' for a sick shift, 'H' for a holiday shift)
I know I've asked for a lot of help recently, I really appreciate everyone's help. :D
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
I currently have a crosstab query that compares data for two years. The results look like this.
month 2004 2005 January 98% 95% ..... Decemeber 98
What I am getting at is that the query returns null values for the fields that do not have data recorded for the months in 2005 because we have not reached them yet. Is there a way for me to make sure that at least the value 0 is entered in the null value areas?
Hi all, I know this is a real easy one, but I am not the smartest when it comes to access. Can you help me out.
I am running a crosstab query to count and sum records in my database. I have a fied called "Amount". In my form the user is not always required to enter an amount. When I run the query, I would like the results to exclude the records that have a null value or $0.00 in the "Amount" field.
How do I format the query to exclude those records?
This is almost a cross-forum post. I started a post in the reports forum to see if it were possible to sort/group the data by field values. Everyone knows this can be done row-by-row, but I was looking for a way to do it horizontally as well.
It should look something like that where the years are values of [year] field and the seasonal quarters are values of [quarter] field.
I have been searching for something that will help me do this when I ran across information on the "crosstab" query. This, in principal is what I am looking for BUT I am not dealing with numeric data here. Is there still a away to make a crosstab query/report with text fields where there would normally be numeric data?
I have a columns that do not incur a count value within the time period of my query, yet i still want them to be displayed with a 0 or no value in the query view, how do i do this?
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 report in which a textbox generates numerical values and letter values. I want to...On report load - if textbox = numbers then hide otherwise show if it contains letter values.
I'm using Access 2010. I need to calculate a score based on values selected in a table by looking up corresponding values in other tables. I have a "Project" form to create new entries into the Project table (see Table 1). When I create a new project record, I will select values for the Payback and Need fields by selecting options from a list. The Payback list is pointed at Table 2 and the Need list is pointed at Table 3. In the below example, I created the "ABC" project and selected "1 year" for the Payback field and "Repair" for the Need field. Pretty simple.
Now that I have the "ABC" project loaded to my Project table, I'd like to create a report that will show a "score" for this project. The score should be calculated as follows: Payback Impact + Need Impact. In this example, the score should be 30 (Payback Impact of 20 + Need Impact of 10).
I have the following dataset in a table called NR_PVO_120. How do i pick out a number (which can change but let's say, 6) of UNIQUE OtherIDs without excluding any OtherIDs under any fax numbers?
So, if you pick OtherID from Row7 you then also must pick OtherIDs from rows 8 and 9 because they have the same fax number. Basically, once you pick an OtherID you're then obligated to pick all OtherIDs that have the same fax number as the one you picked.
If the number requested (6 for this example) isn't possible then "the closest number possible but not exceeding" would be the rule.
For example, if you take OtherIDs from rows 1-10 you will get 6 unique OtherIDs but row 10 shares a fax with rows 11 and 12. You either need to take all 3 (but that will raise the unique count to 8, which isn't acceptable) or skip this OtherID and find one with a fax that has no other OtherIDs and that isn't on the result set already. My result of 6 UNIQUE OtherIDs will need to contain ALL OtherIDs under any fax the existing OtherIDs are connected to.
So one solution is to take rows 1-6, 26. Another is to take rows 1-4,10-14.
There will be many possibilities (the real dataset has tens of thousands of rows and the number of people requested will be around 10K), as long all OtherIDs connected to all faxes on the result set are part of the requested number (6 in this case) any combination would do.
A few notes.
1.Getting as close as possible to the requested number is a requirement.
2.Some OtherIDs will have a blank fax, they should only be included as a last resort (not enough OtherIDs for the requested number).
This is for a fax campaign, we need to make sure no fax number is faxed twice, that all people connected to that fax number are contacted under one fax sent.
In Access 2010 I have a Data Entry Form on which I have an unbound textbox in the header that the user can put a default date in. In the body of the form is a bound textbox that records the date and the default value is set as =defaultdatestat (obviously the name of the box in the header).
Problem: The default date shows up perfectly until a value is put in any of the other text boxes.
For further info : If you put values in text boxes default value disappears; if you then push escape the default value reappears when the values in the text boxes disappear.
I have a query with an INNER JOIN and ORDER BY that is working great. Now, using the same JOIN, I need to update values in one table with the values in another. I thought it would be simple until I learned you can't do an ORDER BY with an UPDATE. Is there another way to achieve the same result? If you remove the 'ORDER BY', the statement below doesn't produce an error but the results are not correct:
UPDATE TableA INNER JOIN TableB ON (Left(TableA.CDN,6))=(TableB.CDN) SET TableA.HCC = TableB.HCC WHERE TableB.HCC Like '241*' AND TableB.BBB = 'X' AND TableA.CCC = "1234" AND TableA.HCC IS NOT NULL ORDER BY TableB.HCC, TableA.CDN;
I have several result fields which are all drop down lists. I want each result field's drop down list values to be different depending on the selected value of the Test1 drop down list.I came up with using the .rowsource keyword. My syntax seems to be fine but I'm not getting any values under the result fields when I run the form.Here is my code so far:
Private Sub Test1_AfterUpdate() If Me.Test1 = "Stress Echo" Or Me.Test1 = "Stress SPECT" Or Me.Test1 = "Stress PET" Or Me.Test1 = "Stress MRI" Then Me.Test1Result2.RowSourceType = "Value List" Me.Test1Result3.RowSourceType = "Value List"
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:
I'd like to create a query which will consist of simple SELECT statements as follows:
SELECT [table1].[field1], [table2].[field1], [table2].[field2] FROM table1 INNER JOIN table2 ON ([table1].[fieldX] = [table2].[fieldX]);
The challenge arises b/c instead of joining on equal values, such as the following: [table1] INNER JOIN [table2] ON [table1].[field1] = [table2].[field1]
I would like to join based on equivalencies, such as: [table1] INNER JOIN [table2] ON [table1].[field1] = 34 is equivalent to [table2].[field1] = 2;
I do not know the proper syntax, so this is where I need help. I tried to search online without any success.
I'm trying to determine the SQL to return only those records in a table which have duplicate values in each of two fields, but different values in a third field. Here's an example:
Code:
AcctNum FoodType FoodClass ------- -------- --------- A123 Apple Fruit A123 Apple Fruit A123 Grape Fruit A456 Potato Vegetable A456 Potato Perishable A789 Carrot Vegetable A001 Banana Fruit
For the above table, I'm trying to return records which have multiple entries for AcctNum + FoodType, but DIFFERENT values for FoodClass. So for the above table, the query would return:
It returns these two records because there is more than one record with for the AcctNum + FoodType (i.e. 'A456' + 'Potato'), but DIFFERENT values for FoodClass (i.e. one record has 'Vegetable' while the other has 'Perishable').
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?
