Crosstab Query Blues

Oct 21, 2005

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.

This is my goal

Oct 19, 2005

T1_TO: 7:00AM - 7:59AM = 4
T2_TO: 8:00AM - 8:59AM = 2
T3_TO: 9:00AM - 9:59AM = 0

and so on around the clock.

I thought this would be easy and I still think it is, I am just missing something.

RichB

View Replies


ADVERTISEMENT

I've Got The RUNNING SUM Blues!!!

Feb 12, 2007

Hello,

I have a report which is similar to a bank statement. I have a transaction date and then either a credit or debit depending on whether the customer made a payment or if I am invoicing them.


I have created an additional field in the report which calculates AmountDue - TotalPaymentAmount and called this field Balance.
I then attributed a running sum based on this Balance field.
This works great.................BUT!!!!!
If a customer has 100 transactions the report will be well over 10 pages long.
Is there a way I can limit the report to just perhaps the most recent 20 transactions or 20 days?

I tried and failed in the report's query retrieve the payments which occur >datenow(-20) (basically in the last 20 days). This worked ok in the sense that only the last 20 days worth of transactions appeared in the report......BUT!......the first running sum of the report does not carry over from the previous transaction..it starts from 0!!!!

Please help me get over the RUNNING SUM blues!
Rob

View 4 Replies View Related

Short Cut Blues / Command Line Switches / WIF

Feb 14, 2006

Hi Folks,

This may not be an Access problem per se, and if not, I appologize in advance.

I have a FE/BE split database with multiple users. I have found out how to add a command line switch to the shortcuts (the shortcut must go to the application first for any switches to work) which opens the database front end nicely. Problem is, I can't get it to connect to the appropriate WIF.

I have tried copying the text from "Microsoft Access Inside Out" which didn't work as it has "/wrkgroup" rather than "/wrkgrp". My set up wouldn't accept that.

Anyway I'm half way there but stuck now. I don't get any error messages when I use the shortcuts but Access stays joined to whatever WIF it was connected to the last time it was running.

I have searched this site but couldn't find anything regarding this specific problem, in the recent past at least.

Any ideas what I'm doing wrong?

I have copied the details from the 2 shortcuts below. The first is to my database and the networked WIF, the second to Access to return the desktop PC to the default WIF. I have also added the Access version number and operating system version numbers.

This is the shortcut to my database with the workgroup switch set to the WIF created for this database:

Target:
"C:Program FilesMicrosoft OfficeOFFICE11MSACCESS.EXE" "C:EDT DBWC-ED0023001R0.02a-fe.mdb" /wrkgrp "K:EDT FOLDEREDT DBED.00.23.001_WIF.mdw"

Start In:
"C:Program FilesMicrosoft OfficeOFFICE11"

Network Drive WIF Path:
K:EDT FOLDEREDT DBED.00.23.001_WIF.mdw

This shortcut is supposed to restore Access to the default WIF on the desktop PC:

Target:
"C:Program FilesMicrosoft OfficeOFFICE11MSACCESS.EXE" /wrkgrp "C:Documents and SettingsQPUserApplication DataMicrosoftAccessSystem.mdw"

Start In:
"C:Program FilesMicrosoft OfficeOFFICE11"

Desktop WIF Path:
C:Documents and SettingsQPUserApplication DataMicrosoftAccessSystem.mdw

Access path:
C:Program FilesMicrosoft OfficeOFFICE11Access.exe

Access Version:
2003 (11.6355.6408) SP1

Environment:
OS NameMicrosoft Windows XP Professional
Version5.1.2600 Service Pack 1 Build 2600

Thanks in advance,

Keith.

View 9 Replies View Related

Crosstab Query Based On Crosstab??

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

Subform Blues - Data Entry Setting Itself To 'No' & Requerying 1 Subform From Another

Dec 5, 2006

2 Subform problems

I have a data entry subform that is only supposed to show an empty record ready to be populated, and a display records subform that is supposed to show all the records. The subforms are both on the same tab of a tab control on my main form.

Problem 1:
The data entry subform shows all the records rather than a blank record. Something on my main form is causing it to show the records when it should not. Any ideas? The Data Entry is set to Yes.

To try to isolate the problem, I created a new form and added the subform to it where it behaves properly:confused:

I then added Me.DataEntry = True to the form open to see if that would solve my problem but it still sets the data entry to no.

If I have the properties box open when in form view of my main form, I can set the data entry to Yes and it works fine until I move to the next record of the main form when it resets to no. Teraing my hair out here.:mad:

My final attempt was to search the entire project to see if there is a "DataEntry = False" somewhere but there isn't. What is setting this property? Any ideas where I should look?


Problem 2:

After entering data in the first subform (data entry form), I want to re-query the second subform but I just can't get the syntax right. I have wrestled with the "Syntax for subs" document downloaded from http://www.mvps.org/access/forms/frm0031.htm (Microsoft MVP site) but to no avail.

My main form is called fdlgPrjDetails, the data entry is via fsubPrjCommentsUsersDataEntry and the subform I wish to requery is fsubPrjCommentsUsers.

None of the attempts below worked giving a cannot find control error.


Private Sub Form_AfterUpdate()
On Error GoTo ErrHandler

Me.Requery

'Me!fsubPrjCommentsUsers.Requery
'Me!fsubPrjCommentsUsers.Form.RecordSource.Requery
'DoCmd.Requery ([fsubPrjCommentsUsers])
'DoCmd.Requery [fsubPrjCommentsUsers]

ExitHere:
Exit Sub

ErrHandler:
MsgBox Err.Number & " - " & Err.Description & Chr(13) _
& Chr(13) & "Error in fsubPrjCommentsUsersDataEntry: Err 003"
Resume ExitHere
End Sub


Any Ideas?

Both problems have me stumped so I'll be grateful to anybody with a scoobie on this.:)

View 10 Replies View Related

Blank Fields In Crosstab Query Based On Previous Query

May 31, 2006

: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;

Thanks for you time! :)

View 1 Replies View Related

Queries :: Query Error When Data Changes In Crosstab Source Query

Aug 12, 2014

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.

View 9 Replies View Related

Crosstab Query

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

Crosstab Query

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

Crosstab Query

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

Crosstab Query

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

Crosstab Query

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

Crosstab Query?

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

Crosstab Query

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

Crosstab Query Help

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

Crosstab Query

Aug 16, 2007

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;

thanks.

View 1 Replies View Related

Crosstab Query And Between

Nov 21, 2007

i have this crosstab query that works fine

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


Help
thanks

View 3 Replies View Related

Query Using Count, Maybe Crosstab?

Jun 28, 2005

Hello,

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!!!

J

View 4 Replies View Related

Crosstab Query For Last 6 Months

Jun 28, 2005

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

Bowes

View 4 Replies View Related

Sorting Crosstab Query

Jul 12, 2005

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.)

Any help would be REALLY appreciated.

Thanks in advance.

View 4 Replies View Related

0 Values Ina Crosstab Query

Aug 3, 2005

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

View 4 Replies View Related

Overall Totals In A Crosstab Query

Aug 26, 2005

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

please?

View 2 Replies View Related

Crosstab Query - Dates?

Oct 10, 2005

Hello,

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.

View 5 Replies View Related

Need Help Setting Up A Crosstab Query

Oct 11, 2005

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?

View 3 Replies View Related

Crosstab Query And Nulls

Oct 25, 2005

In my Accounts table I have a field called Status. This field is populated by single letters. A=Active, P=Paid and so on. I have a status (R) that is occasionally used. It won't show up in my crosstab queries since we currently do not have an account that is in R status. How can I get my crosstab to show the R status as having 0?

View 3 Replies View Related

Crosstab Query Problem

Nov 12, 2005

I’m struggling with the following cross tab query:

IDDateProduct Net Weight
11/11/05 Prod A1000
21/11/05 Prod A3000
32/11/05 Prod B1500
42/11/05 Prod C1200

Product Type is grouped according to the individual types for that particular date and quantities summed.

I’ve achieved this but I’d also like to add the weight of Prod A and Prod C together for each day, grouping them together with a sum of their weights.

I suspect that a IIf or SQL statement may be the way forward but I’m unable to make any headway.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved