Cross tab query solution

I have table Named OutStationTab having the data as below field

EName - Text Field
OsDate - Date Field
OsAmt - Numeric field

I want to make a cross tab query in which 
RowHeading- EName
Column Heading-  Expr1: Format([OsDate],"dd-mm-yyyy")
Value- OsAmt


Note the data is entered from 15th Date of a month to 14th date of next 
month. One Employee take 1 outstation charges only in a day.

I am able to make the crosstab query and its working perfect. Like I filter 
the query (From 15-02-2010 to 14-03-2010), then made the CrossTab 

query. It works perfect.
It does not display the date on colum in which no one is on outstation.
BUT my requirement is that to display like all the day like - 15/02 16/02 
17/02 till.....14/03 . It should NOT miss any day inbetween the 

period. 

Please advise method to do above, so that all the date inbetween the start 
and end date should display.

And also how to handle report. As the month changes and date count/column 
also changes (like decrease/increase)

Regards

Irshad

0
Utf
3/27/2010 12:19:01 PM
access.queries 6343 articles. 1 followers. Follow

4 Replies
964 Views

Similar Articles

[PageSpeed] 37

On Sat, 27 Mar 2010 05:19:01 -0700, Irshad Alam
<IrshadAlam@discussions.microsoft.com> wrote:

The typical solution is to set the ColumnHeadings property of the
query.
You may also be able to join with a table that does have all dates.

-Tom.
Microsoft Access MVP


>I have table Named OutStationTab having the data as below field
>
>EName - Text Field
>OsDate - Date Field
>OsAmt - Numeric field
>
>I want to make a cross tab query in which 
>RowHeading- EName
>Column Heading-  Expr1: Format([OsDate],"dd-mm-yyyy")
>Value- OsAmt
>
>
>Note the data is entered from 15th Date of a month to 14th date of next 
>month. One Employee take 1 outstation charges only in a day.
>
>I am able to make the crosstab query and its working perfect. Like I filter 
>the query (From 15-02-2010 to 14-03-2010), then made the CrossTab 
>
>query. It works perfect.
>It does not display the date on colum in which no one is on outstation.
>BUT my requirement is that to display like all the day like - 15/02 16/02 
>17/02 till.....14/03 . It should NOT miss any day inbetween the 
>
>period. 
>
>Please advise method to do above, so that all the date inbetween the start 
>and end date should display.
>
>And also how to handle report. As the month changes and date count/column 
>also changes (like decrease/increase)
>
>Regards
>
>Irshad
0
Tom
3/27/2010 1:19:21 PM
If this is for a report (or maybe even a form) I would use the solution 
found at http://www.tek-tips.com/faqs.cfm?fid=5466. You would need to change 
all the date intervals from quarter to day. Use a text box on a form to 
enter either the beginning or ending date.

-- 
Duane Hookom
MS Access MVP


"Irshad Alam" <IrshadAlam@discussions.microsoft.com> wrote in message 
news:50FE4299-D677-4722-BE0D-862FFB577A21@microsoft.com...
> I have table Named OutStationTab having the data as below field
>
> EName - Text Field
> OsDate - Date Field
> OsAmt - Numeric field
>
> I want to make a cross tab query in which
> RowHeading- EName
> Column Heading-  Expr1: Format([OsDate],"dd-mm-yyyy")
> Value- OsAmt
>
>
> Note the data is entered from 15th Date of a month to 14th date of next
> month. One Employee take 1 outstation charges only in a day.
>
> I am able to make the crosstab query and its working perfect. Like I 
> filter
> the query (From 15-02-2010 to 14-03-2010), then made the CrossTab
>
> query. It works perfect.
> It does not display the date on colum in which no one is on outstation.
> BUT my requirement is that to display like all the day like - 15/02 16/02
> 17/02 till.....14/03 . It should NOT miss any day inbetween the
>
> period.
>
> Please advise method to do above, so that all the date inbetween the start
> and end date should display.
>
> And also how to handle report. As the month changes and date count/column
> also changes (like decrease/increase)
>
> Regards
>
> Irshad
> 
0
Duane
3/28/2010 4:20:17 AM
Dear sir,

Thanks for your advise.

I tried the method shown on the website. But not fully sucessful.

My Sql View is below :

TRANSFORM Max(Query1.EMoney) AS MaxOfEMoney
SELECT Query1.ENo, Query1.Ename, Query1.Ecode, Sum(Query1.EMoney) AS TotalAmt
FROM Query1
GROUP BY Query1.ENo, Query1.Ename, Query1.Ecode
ORDER BY "D" & DateDiff("d",[EDate],[FromDate])
PIVOT "D" & DateDiff("d",[EDate],[FromDate]);

As I posted test data from  1-Feb2010 to 10-Feb-2010, It viewed ok as shown 
on web.

But the problem arises, as said on the web I change the Query properties 
colum heading of that field as below :

"D1/2","D2/2","D3/2","D4/2","D5/2","D6/2","D7/2","D8/2","D9/2",........."D28/2"

All the data of amount disappears, only the rows remain ok. Below is the sql 
view after puting the colum heading:

TRANSFORM Max(Query1.EMoney) AS MaxOfEMoney
SELECT Query1.ENo, Query1.Ename, Query1.Ecode, Sum(Query1.EMoney) AS TotalAmt
FROM Query1
GROUP BY Query1.ENo, Query1.Ename, Query1.Ecode
ORDER BY "D" & DateDiff("d",[EDate],[FromDate])
PIVOT "D" & DateDiff("d",[EDate],[FromDate]) In 
("D1/2","D2/2","D3/2","D4/2","D5/2","D6/2","D7/2","D8/2","D9/2","D10/2");


Please advise

Regards

Irshad


"Duane Hookom" wrote:

> If this is for a report (or maybe even a form) I would use the solution 
> found at http://www.tek-tips.com/faqs.cfm?fid=5466. You would need to change 
> all the date intervals from quarter to day. Use a text box on a form to 
> enter either the beginning or ending date.
> 
> -- 
> Duane Hookom
> MS Access MVP
> 
> 
> "Irshad Alam" <IrshadAlam@discussions.microsoft.com> wrote in message 
> news:50FE4299-D677-4722-BE0D-862FFB577A21@microsoft.com...
> > I have table Named OutStationTab having the data as below field
> >
> > EName - Text Field
> > OsDate - Date Field
> > OsAmt - Numeric field
> >
> > I want to make a cross tab query in which
> > RowHeading- EName
> > Column Heading-  Expr1: Format([OsDate],"dd-mm-yyyy")
> > Value- OsAmt
> >
> >
> > Note the data is entered from 15th Date of a month to 14th date of next
> > month. One Employee take 1 outstation charges only in a day.
> >
> > I am able to make the crosstab query and its working perfect. Like I 
> > filter
> > the query (From 15-02-2010 to 14-03-2010), then made the CrossTab
> >
> > query. It works perfect.
> > It does not display the date on colum in which no one is on outstation.
> > BUT my requirement is that to display like all the day like - 15/02 16/02
> > 17/02 till.....14/03 . It should NOT miss any day inbetween the
> >
> > period.
> >
> > Please advise method to do above, so that all the date inbetween the start
> > and end date should display.
> >
> > And also how to handle report. As the month changes and date count/column
> > also changes (like decrease/increase)
> >
> > Regards
> >
> > Irshad
> > 
0
Utf
3/28/2010 7:47:01 AM
Remove the Column Headings property so you can actually compare what the 
crosstab generates vs what you have entered into the Column Headings 
property. I'm not sure how you got 2 numbers with a "/" from "D" and an 
integer.


-- 
Duane Hookom
MS Access MVP


"Irshad Alam" <IrshadAlam@discussions.microsoft.com> wrote in message 
news:7F850CEE-CE72-4CE8-8086-3F1058153E0D@microsoft.com...
> Dear sir,
>
> Thanks for your advise.
>
> I tried the method shown on the website. But not fully sucessful.
>
> My Sql View is below :
>
> TRANSFORM Max(Query1.EMoney) AS MaxOfEMoney
> SELECT Query1.ENo, Query1.Ename, Query1.Ecode, Sum(Query1.EMoney) AS 
> TotalAmt
> FROM Query1
> GROUP BY Query1.ENo, Query1.Ename, Query1.Ecode
> ORDER BY "D" & DateDiff("d",[EDate],[FromDate])
> PIVOT "D" & DateDiff("d",[EDate],[FromDate]);
>
> As I posted test data from  1-Feb2010 to 10-Feb-2010, It viewed ok as 
> shown
> on web.
>
> But the problem arises, as said on the web I change the Query properties
> colum heading of that field as below :
>
> "D1/2","D2/2","D3/2","D4/2","D5/2","D6/2","D7/2","D8/2","D9/2",........."D28/2"
>
> All the data of amount disappears, only the rows remain ok. Below is the 
> sql
> view after puting the colum heading:
>
> TRANSFORM Max(Query1.EMoney) AS MaxOfEMoney
> SELECT Query1.ENo, Query1.Ename, Query1.Ecode, Sum(Query1.EMoney) AS 
> TotalAmt
> FROM Query1
> GROUP BY Query1.ENo, Query1.Ename, Query1.Ecode
> ORDER BY "D" & DateDiff("d",[EDate],[FromDate])
> PIVOT "D" & DateDiff("d",[EDate],[FromDate]) In
> ("D1/2","D2/2","D3/2","D4/2","D5/2","D6/2","D7/2","D8/2","D9/2","D10/2");
>
>
> Please advise
>
> Regards
>
> Irshad
>
>
> "Duane Hookom" wrote:
>
>> If this is for a report (or maybe even a form) I would use the solution
>> found at http://www.tek-tips.com/faqs.cfm?fid=5466. You would need to 
>> change
>> all the date intervals from quarter to day. Use a text box on a form to
>> enter either the beginning or ending date.
>>
>> -- 
>> Duane Hookom
>> MS Access MVP
>>
>>
>> "Irshad Alam" <IrshadAlam@discussions.microsoft.com> wrote in message
>> news:50FE4299-D677-4722-BE0D-862FFB577A21@microsoft.com...
>> > I have table Named OutStationTab having the data as below field
>> >
>> > EName - Text Field
>> > OsDate - Date Field
>> > OsAmt - Numeric field
>> >
>> > I want to make a cross tab query in which
>> > RowHeading- EName
>> > Column Heading-  Expr1: Format([OsDate],"dd-mm-yyyy")
>> > Value- OsAmt
>> >
>> >
>> > Note the data is entered from 15th Date of a month to 14th date of next
>> > month. One Employee take 1 outstation charges only in a day.
>> >
>> > I am able to make the crosstab query and its working perfect. Like I
>> > filter
>> > the query (From 15-02-2010 to 14-03-2010), then made the CrossTab
>> >
>> > query. It works perfect.
>> > It does not display the date on colum in which no one is on outstation.
>> > BUT my requirement is that to display like all the day like - 15/02 
>> > 16/02
>> > 17/02 till.....14/03 . It should NOT miss any day inbetween the
>> >
>> > period.
>> >
>> > Please advise method to do above, so that all the date inbetween the 
>> > start
>> > and end date should display.
>> >
>> > And also how to handle report. As the month changes and date 
>> > count/column
>> > also changes (like decrease/increase)
>> >
>> > Regards
>> >
>> > Irshad
>> > 
0
Duane
3/28/2010 4:08:20 PM
Reply:

Similar Artilces:

query destination field
How do you change a destination field of a query in Excel? I have the data from the query being sent to cell A4 but need it to go to cell A1. I tried the "edit query" option but with no luck albert I think you can just delete Rows 1:3 and it will adjust accordingly. -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "albert" <anonymous@discussions.microsoft.com> wrote in message news:14E1E67B-F7A0-4FD3-A78E-C4E1CCC22453@microsoft.com... > How do you change a destination field of a query in Excel? I have the data from the query b...

Cashflow query to return previous valid field if date has null val
Hello guys! Hope someone can help as usual! I have designed a simple query to control my cashflow, pulling data from a topay_query and toreceive_query. I have managed to make all the calculations, no problem. The thing is that I have an "Accumulated payed" and "Accumulated received". When there's no moviment in a day these fields come out blank. How can I do that when there's no movement, the query would consider always the value of the previous valid field? THanks mate! How are your queries set up? Are you linking by day? Is one of your criteria to only ...

Simple query??
Hello All I have been wrestling for ages now with a query that I thought would be simple .. but can't get the output I want! I have a table [practice suppliers] with fields SUPPLIER and AUTO CATEGORY (and others). The field SUPPLIER is not a unique (key) field, but in fact it almost is: ~95% of the records in the table contain unique values in this field.Of the remaining ~5%, about half of the records for a given SUPPLIER value contain the same value of AUTO CATEGORY, and the other half contain different values for AUTO CATEGORY. I want a query that will return the number of diffe...

Print multiple tabs on one page
How to print multiple tabs in excel onto one page in one print job? Tab1 and tab2 have different format and cannot be combined into one tab. Need to print the two worksheets one on top of each other is the same direction. Tried Report Manager but printed on two pieces of paper. Any suggestions? One way to accomplish something like this is to play around with "pictures". I'm on an XL97 machine now so, When you hold down <Shift> and hit <Edit>, you get a different dialog box .... one that contains "picture " options. So, select an entire sheet, or p...

A more elegant solution?
I often want to display the value of a global variable in a form or report. I need to do this via a function (can't just display the variable itself, it seems). Instead of a bunch of specific functions, I'd like to have a single "fncDisplayGlobal()" function. I could create one with an ever-growing SELECT statement in it, but is there a more elegant way? Can I somehow pass in the name of the function as a string and then magically turn it into the variable of the same name? And then pass back the value? "Laurel" <FakeMail@Hotmail.com> wrote in ne...

'Restore to Mailbox' is greyed out in the redirection tab.HELP
Even I selected only one single mailbox. I am using Veritas BackupExec 7.3. The account I am logon has the local administrator rights. Any suggestions? Thanks. Bernard Jen ...

Queries and Now()
Hi Using Access 2007. I have a database in mdb format that records the information of customer sales on a web site. In the OrderDate field I am using Now() to record the date and time the order is placed. I also have a query which displays only currently open orders. When I view either the table or query data locally within Access the date/time information of each order displays correctly. Likewise, when I view the table data online via an ASP VB web page, the date/time information of each order displays correctly However, when I view query data online via an ASP VB web page, the da...

Make Table Query Question 07-21-07
Hello All: Access 2003 I have a make table query and one of the field is a $$$ dollar amount. I set the properties to "currency". However, in the table, the field type is "Number". Any ideas why this is happening? -Joe You could try typecasting the field in your query. For eample, if the design view shows the field like this: Amount: [Quantity] * [UnitPrice] try changing it to: Amount: CCur(Nz([Quantity] * [UnitPrice],0)) In general, the better solution is to design the table the way you want it, and then use an Append query to populate it, rather than a Ma...

CString tabbing....
Hello again, I have yet another question that I need answered. I got a CString variable that I used the format property to set the text. Here is what the code looks like.... FinalResult.Format("%s\t%s\t%s",TmpBfr,TmpBfr2,TmpBfr3); When I print out this string I get an odd character, instead of a tab between the strings. What can I do to get the output from the printer to have these tabs instead of the weird character? Thanks in advance, James Simpson Straightway Technologies >I got a CString variable that I used the format property >to set the text. Here is what the co...

multi record query
I am taking 4 fields from a linked AS 400 database, the 4 fields are IDAREA = T IDAISL = RA IDBAY = IL1 IDLEVL = 9 I need the word "TRAIL19" to be my parameter for the information I need for a query to fill in the form and report Is there any way to combine these 4 feilds? On Mon, 11 Jan 2010 20:09:01 -0800, Norm <Norm@discussions.microsoft.com> wrote: select IDAREA & IDAISL & IDBAY & IDLEVL as myCombinedField from myTable (of course you change myObjectNames to yours) -Tom. Microsoft Access MVP >I am taking 4 fields from a linked AS 4...

overlapping elements in tabs
hello, how can i manage to avoid overlapping elements in tabs. for example: if i have a group box and want to use a check box in the group box header sometimes it works, but most of the times the check box is hidden under the group box. is there an option to 'order' the elements like we know it from powerpoint? thanks for the help - happy easter manuel "baeman" <baeman@gmx.net> wrote in message news:1635d65f-03ec-480a-a8db-ff3d12543281@s37g2000prg.googlegroups.com... > hello, > > how can i manage to avoid overlapping elements in tabs. > > for examp...

Tables/Queries
Hello, I've seen some posts regarding a similar problem to what I'm having but cant find the resolutution! I have a database with tableA which contains basic inventory info. I have a field in tableA named location. There is a second table (tableB) which has a list of all the locations our company delivers to. I've been warned to stay away from lookup fields, so I'm using combo boxes on the form to look up the location and then populate the location field in tableA when the value is selected. Here is the issue (and again, I've found some posts similar): Running a...

query form 12-29-09
I have a form that opens when you run a query for an employee name. It does exactly what it is suppose to do. It displays all information that I have requested. The problem I am having is when I enter a name of an employee that is not in the database the form comes back as a grayed out box. Is there any way to set the form or query to prompt the user that the name is not in the database and allow the user to run the query again. Eric Check to see if record exist before you open the form. If you are using a command button to open your form containing code like the follow...

Queries independent of accents?
Hello! We have a DB of people from many countries. Some names contain chars with accents like � or � and other types of accents. Is there a way of searching all names in the DB containing any kind of accent without specifiying the acctents used? Is there a function like f("�") = "e" where f is the function I look for. Thx in advance! Fritz Fritz Do you store the data as NAVARCHAR(n) datatype? Have you specify N just before a value in WHERE condition? Hmm, I think may want to take a look into REPLACE function to remove accents "Fr...

My solution
Using a popup (mode) form is beneficial when you don't want it to become lost behind another form while it is open. Since my procedure will work the way I like only when the form is open in Normal mode, my solution is to close the form when it loses the focus. Then click the button to display it again. It turns out that it makes for a better working app and I don't lose site of my open form. Note the close method won't work when a form is in popup mode, becuase a popup form can't receive the focus. I meant this to be posted as a reply to the thread, "Moves...

Help on muliple table queries
Hi all, I wonder if you can help. I have 5 tables of data with the same column names. I want to run a query which includes all records from all tables within a certain time period using a from/to date. Is this possible/simple, and how do I do it!! Thanks Natalie Hello Natalie. "Natalie" wrote: > Hi all, > I wonder if you can help. I have 5 tables of data with the same > column names. I want to run a query which includes all records from > all tables within a certain time period using a from/to date. > > Is this possible/simple, and how do I do it...

Voting Buttons -- Missing drop-down box in Tracking tab
I have a user on Office 2003 who uses voting buttons in Outlook. She claims that on the Tracking tab of her sent message, a drop-down box should appear at the right side of each user's "response" column showing the voting options -- for her to manually populate if the respondent verbally replies to her. Our outlook 2003 does not have this. I can find nothing on it. What is she talking about, and how can I set up this feature for her? -- Lisa M I think she's wrong. That function exists in a Meeting's Tracking table, not a vote related tracking table from an em...

VCard and Activities Tab
I'd like to send co workers my vcard, but it shows all of my activities on the activity tab. I'd prefer that this couldn't be seen. It's Outlook 2003 btw. Thank you. Sam No, it doesn't. You can stop being paranoid about this. No one is = monitoring the activities in your Outlook folders. A vCard is a text = file that contains information about a contact. You can see this for = yourself by opening it in Notepad. It contains no information about = activities. REPEATING: It contains no information about activities.=20 The Activities page builds a list *on the fly* of item...

Error Exporting Access Query Results to Excel
I'm having 2 different problems exporting query results from Access to Excel (Office 2007). Problem 1: I do what I've always done, in Access VBA get ADO recordset from query results, instantiate Excel, get a range object, use the range object's CopyFromRecordset method. This has always worked in the (pre-Office 2007) past for me. It is much easier than iterating records and columns and assigning values to cells. Now, I get an error -2147467259 Automation Error Unspecified Error. I'm guessing this is because 2 of my recordset fields are pictures in Attach...

MS Query instead of "Edit OLE DB Query"
Hello all, I am having a problem with editing some existing queries within Excel 2003 workbooks. When clicking on the "Edit Query" button from the External Data toolbar, I now get the "Edit OLE DB Query" dialog box, instead of MS Query, which was the interface that I previously would get. Is there a way to switch this back? I have been using Office 2003 on Windows XP Pro SP2. I recently had Microsoft Update upgrade my version of Office 2003 from SP2 to SP3. The problems appear to have started after that. I can still create new queries in Excel using MS Query, which is...

Moving Parameter Queries
I have created a Parameter Query that imports transactions from an Access Database on my 'C' drive to "work out the bugs". It works just fine, however when I move it (as well as the related dqy file and the database) to a shared network drive so that others can access it, it sends back an ODBC driver error message. I am at a loss as to how to remedy this situation without recreating the query (very time consuming) at the new location Has anyone run into this before Any efforts will be much appreciated Tom Sounds like a confused link - am assuming your query pulls from a...

xPath query #2
I'm trying the select a element based on values of two attributes. In the Xml doc i need to look at an attribute minVal and maxVal. if the Value I'm passing in in the xPath is greate than min "and" less than max, I'll want to pull all the attributes of that element. Is this possible with xPath? I'm able to easily query 1 attribute, but not two. Thanks TR TR wrote: > I'm trying the select a element based on values of two attributes. In > the Xml doc i need to look at an attribute minVal and maxVal. if the > Value I'm passing in in th...

Append Query 08-21-07
I have two tables. Table A has 349,353 records. Table B has 377,787 records. I need to add the 28, 434 records that are not in table A but are in table B. Table A and B have a unique 6 digit number as its primary Key. When I attempt to run an append query from B to A, I get an error message stating it did not add 28, 434 records due to Key Violations. Sorting the unique 6 digit numbers tells me right off the bat that there are some in B that are not in A, yet when I attempt an append query, it tells me there are Key violations. I even selected on specific record whose 6 digit number I ...

Excel DNS query
Hello, Is there a way for an Excel function to query a DNS server? Thanks. Soundy Not that I know of, but you can turn on the macro recorder, use 'get external data' and tailor the resulting code into a user function of your own. E.g. I've used this to create a button to get MS-Access data from a query that has the same name as the sheet (tab) name. It saves me a lot of copy-paste actions. Bas Hartkamp <soundy@gmail.com> schreef in bericht news:1151940450.029823.127570@j8g2000cwa.googlegroups.com... > Hello, > > Is there a way for an Excel function ...

Learning XPath Query Language Resource Material/Books/White Papers
I am a total beginner to XPath and using Native .NET XML Classes (although I have used XML with Datasets before). I have "XML for ASP.NET Developers" which seems like a good .Net XML overview book but would like to learn more about writing XPath Queries. What do I need to learn the XPath Language? Thanks Earl http://www.zvon.org/xxl/XPathTutorial/General/examples.html -- This posting is provided "AS IS" with no warranties, and confers no rights. "Earl Teigrob" <earlt777@hotmail.com> wrote in message news:u9bfw%23fUDHA.1012@TK2MSFTNGP11.phx.gbl... &g...