Attn GURUS - Crosstab/Maketable Help
Feb 21, 2008
I have a recordset with 335000+ rows. The database contains 8 different columns. I have a series of queries that validate each column element. I then use a UNION query to combine the mini queries because the validation results is the same, as well as the query structure. The validation results can either be, "PASSED, FAILED, or DROPPED".
I am trying to create a crosstab query to display the COUNT of each occurence of validation, per query. The problem is, for each query (row) in the cross tab, the total of each query is not the same, and is less than 335000 rows. One query could show 319000, another 323000... I don't know why this is doing it, because if I run each query separately, it says each one contains 335000 rows, and each row has a validation result.
Any suggestions?
View Replies
ADVERTISEMENT
Apr 19, 2006
the following works great, when I have text in the field. I want to do, is just the opposite
If Nz(Me.NTP) Then
Me.Label159.Visible = Not Me.Label159.Visible
Else
Me.Label159.Visible = True
End If
end sub
View 4 Replies
View Related
Mar 9, 2006
I havent used Access for sometime so please bear with me.
I have a text box called txt18 on a subform which is used to display the total no of records within subfrmPSR. I have used =Count() in the control source.
My 2nd text field called txt17 (unbound) I would like it to count as users use the move next and previous buttom to navigate thru the record. As they move through the records I would like txt17 field to count from the first record to the current record in the set.
I imagine that a piece of code will need to go in OnCurrent of the subform referring to txt17.
I.e
txt17 txt18
2 of 6
txt17 will be the only field that changes.
Any help would be appreciated.
View 3 Replies
View Related
Apr 3, 2006
I have "unchecked" all the confirm options under tools, but when I run the make table query it still displays "The existing table will be deleted before you run the query, do you wish to contiue? I would like it to just run without this message, or at least give a message of my choosing. Is this possible?
Thanks!
View 3 Replies
View Related
Jul 15, 2006
Hi Guys,
8 posts on the subject over a number of years and the answer seems to have been no, you can't have an auto number field in a maketable query.
Is there any reasonable way to create a sequential count of the records shown in the maketable query, not the underlying records?
Background:
I have a maketable query that selects fields from 2 tables which are ordered in a specific way. The sigma button is clicked to reduce this recordset to unique entries and this is what goes into the created table.
This newly created table is a row heading for a pivot table query and I want it ordered by the method used in the query. An autonumber or some method of counting/incrementing the records would do the trick.
I am currently creating the table and then adding the Autonumber field manually, which is hardly a solution at all. :confused:
An afterthought before I post: The reason I want the autonumber field is that the pivot table shows the records in alphabetical order rather than the order the table holds the records. Is there any way of forcing the pivot table to use the records as ordered in the table. A note of caution here, I get very confused when dabbling with pivot tables. :eek:
Any thoughts/solutions welcomed. And if I'm asking the wrong question, let me know! :)
View 3 Replies
View Related
Jul 19, 2006
I am basically making a form to streamline the processes and functionality of my database project, so basically it's just a form that runs queries.
However, whenever it runs an action query, it always brings up a yes or no prompt for whatever action it is doing. Is there any way to disable these prompts (i.e. default yes to everything) or is this just an annoyance I will have to deal with?
Thanks.
View 2 Replies
View Related
Jul 2, 2015
All of the types? Including Memo?
View 2 Replies
View Related
Sep 21, 2007
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
Please help!!!
View 2 Replies
View Related
Mar 23, 2006
I have a table that holds review information on staff:
tblReviews:
StaffID (Number)
ReviewDate (Date/Time)
Completed (Yes/No)
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:
StaffID Review1 Review2 Review3
17 01/01/2006 04/04/2006 05/05/06
20 01/01/2006 04/04/2006 05/05/06
25 01/01/2006 04/04/2006 05/05/06
Other then creating a table and playing with a bit of vba does anyone have a solution?
Thanks.
View 4 Replies
View Related
Sep 16, 2007
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??
heres a cpy of my db
View 4 Replies
View Related
Nov 26, 2007
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).
Can anyone help?
View 9 Replies
View Related
Dec 19, 2007
Hello,
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:
Option 1: 1 = 20 times, 2 = 5 times, 3 = 15 times
Option 2: 2 = 5 times, 2 = 10 times, 3 = 0 times
So on and so on. Would this be done with a crosstab query, or am I way off?
Thanks in advance for your help.
Alex
View 4 Replies
View Related
Aug 17, 2005
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)”
Thanx
ami
View 2 Replies
View Related
Oct 6, 2005
Does anyone know what the maximum rows & columns are for a crosstab query as I cannot find them anywhere?
When I create one that is too big, it just gives the message of how many columns I have rather then how many I'm allowed.
Any help would be greatly recieved.
Regards
Carly
View 3 Replies
View Related
Dec 15, 2006
Hmz,
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?
Greets & Thanks
Yours
View 4 Replies
View Related
Sep 23, 2005
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!
Super Thanx.
O.
View 5 Replies
View Related
Oct 2, 2005
Hi all
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?
Cheers
Rhys
View 4 Replies
View Related
Oct 4, 2005
Hi, I have a crosstab query I have set up, calculating the cost of a product per month
it should look like
1 2 3 4
gek22 £55 £66 £77 £88
er44 £99 $100 £101 £102
tt66 £103 £104 £105 £106
but instead it looks like
1 2 3 4
gek22 £55 £88
gek22 £66
gek22 £77
er44 £99
er44 $100 £102
er44 £101
tt66 £103 £106
tt66 £103
tt66 £104 £105
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
View 3 Replies
View Related
Dec 6, 2005
I have a UNION qry in which I have Out, Incoming and internal invoices as RELATIONSHIPs, and based on it a CROSSTAB qry, and now there are 2 new categories which are goods that were returned - and there are 2 because:
1. is return from Internal and
2. is return from Outgoing invoices.
I need to calculate the current InStock (based on articalNumbers) with this in mind:
InStock = Incoming - OUTgoing - Internal + returnfromInternal + returnFromOutgoing
However, I cant seem to get a returns to the same crosstab qry in order to include them in calculation
the real names of variables are not important but the principle on which I should base my qry is the problem for me (please have in mind that I first started working in access about 2 months ago)
Is there anything wrong with the principle of my work here?
t i a...
View 1 Replies
View Related
Jan 20, 2006
OK, been awhile since I posted anything here. I have a weird one, hope there is a solution.I have a form that uses a crosstab query for it's data. The problem is the crosstab query has a column heading that (in my example) represents warehouses of which there are ten. So when the form was originally created it had ten text boxes for quantity values by product for each warehouse. The problem is depending on when the query is ran there will be times when there are no values for one or more warehouses, when this happens the crosstab query does not show those warehouses at all as column headings, therefore the text boxes on the form do not have a corresponding field on the query and, because of that it displays the - #Name? in the text box.:mad: How would one go about dealing with this situation??Thanks!
View 2 Replies
View Related
Mar 29, 2006
Hi there I have the following predicament,
I want to run a crosstab query on a table that looks like the below:
ID STORE RECEIVED ITEM RESPONSE
1, 123, 2006-03-30, Name, Tony
1, 123, 2006-03-30, Position, Baron
1, 123, 2006-03-30, Name, Simon
1, 123, 2006-03-30, Position, Mgr
1, 321, 2006-03-30, Name, Sarah
1, 321, 2006-03-30, Position, Analyst
The crosstab query as it stands only pulls back the first and last records as it wont allow grouping on the value field, which in this case is the response field. All other fields are row headings.
The report looks like this:
1, 123, 2006-03-30, Tony, Baron
1, 321, 2006-03-30, Sarah, Analyst
Any ideas on how I can bring back both reccords, eg Group on the all fields? It should look like this:
1, 123, 2006-03-30, Tony, Baron
1, 123, 2006-03-30, Simon, Baron
1, 321, 2006-03-30, Sarah, Analyst
Thanks for your time!
Cheers
Tony
View 1 Replies
View Related
Jul 6, 2006
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?
View 1 Replies
View Related
Aug 30, 2006
I have a crosstab query where I can compile the totals per day that I want. What I need to do is then group these days into weeks by the following Sunday for a week ending total. How can I link each day for its corresponding week ending date. I attatched an image if it helps.
View 4 Replies
View Related
Jan 25, 2007
I have a table with the following info:
Date/shift/name
1-1-07/1/tom
1-1/07/1/Bill
1-1-07/2/frank
1-1-07/2/Ben
1-1-07/3/Scott
1-1-07/3/Terry
1-2-07/1/tom
1-2/07/1/Bill
1-2-07/2/frank
1-2-07/2/Ben
1-2-07/3/Scott
1-2-07/3/Terry
1-3-07/1/tom
1-3/07/1/Bill
1-3-07/2/frank
1-3-07/2/Ben
1-3-07/3/Scott
1-3-07/3/Terry
What I want is the date to be displayed horizontally and the the names vertically with the shift as the intersection:
1-1-07 1-2-07 1-3-07
Tom 1 1 1
Bill 1 1 1
Frank 2 2 2
Ben 2 2 2
Scott 3 3 3
Terry 3 3 3
is this possible with a crosstab query or do I have to go about doing it some other way? If a crosstab query is possible how do you go about doing it?
Any help would be greatly appreciated.
View 3 Replies
View Related
May 30, 2007
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? :)
Something like this is what I need:
_______________Jan______________Feb_____________Ma r__________Apr
Power............1/3....200..............2/4...250...........3/7....225........4/5.....250
Lease............1/15...1200..........2/15...1200..........3/16..1200.......4/20...1200
Insurance.......1/16...175.............2/20...175...................0.....................0
.............................................. 2/26...350
Water/Gas.......1/6...150.............2/10...175...........3/3...150.........4/10...175
Thanks a lot...
View 10 Replies
View Related
Jun 29, 2007
Hi,
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?
Thanks!
View 1 Replies
View Related
