UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Creating a Crosstab query that can return 'as of' data    
 
   
srp7777
post Apr 7 2009, 10:57 PM
Post#1



Posts: 5
Joined: 7-April 09



I have a Property Data table that has Orgination Date, Property Type & Selling Status. Basically I need the count of properties by property type for Jan - Dec excluding sold properties. I can create 12 queries and in each one change the criteria to get the count for that month but I was hoping that a crosstab query could be easier. Does anyone have any sugguestions?

Example:
Property Type Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total
Commercial 10 12 15
Residential 20 25 33
Land 5 4 7
Multifamily 6 8 10
Go to the top of the page
 
GroverParkGeorge
post Apr 8 2009, 12:22 AM
Post#2


UA Admin
Posts: 35,681
Joined: 20-June 02
From: Newcastle, WA


Welcome to Utter Access.
Oes that example represent the actual columns, or fields, in your table? Or is this an example of the output required?
If that is, in fact, the way the table is currently designed, it needs to be changed to a normal design.
Assuming that this is the desired output, and not a description of the table, your crosstab query can be created by selecting the Months from the OriginationDate field, Format([OriginationDate]) to return the months formatted as "Jan", etc. Unfortunately this has the disadvantage of converting the months to strings, so the sorting doesn't reflect the actual sequence.
Here's a sample that might get you started:
TRANSFORM Count(tblProperty.[PropertyType]) AS CountofPT
SELECT tblProperty.[PropertyType])
FROM tblProperty
GROUP BY tblProperty.[PropertyType]
PIVOT Format([OriginationDate],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
Untested air code. Change the table and field names to those in your actual database.
Go to the top of the page
 
srp7777
post Apr 8 2009, 11:15 AM
Post#3



Posts: 5
Joined: 7-April 09



The example represents the output required. I tried your code and it gave me a count but not the count that I need, unfortunately I don't think that I was very clear. I'm very new to this, Sorry. I need to know how the count changed from month to month. So I did one query to with criteria on the Date to say <#2/01/09#> to get Jan's number and then another query with criteria on the Date to saying <#3/01/09#> to get Feb's numbers and so on. Can I do this in a crosstab or is there something easier than 13 separate queries? Thanks a lot for all of your time and help.
Go to the top of the page
 
GroverParkGeorge
post Apr 8 2009, 10:54 PM
Post#4


UA Admin
Posts: 35,681
Joined: 20-June 02
From: Newcastle, WA


You need to display the difference between months, or how much the count changed from month to month. A much more complex query than just the count in a month.

This will be easier if you can provide a sample of your data to work with. Remove any sensitive data and compress the sample so that it is under 500KB and upload it here. Van has created a similar source query here which might be useful as an example.


Edited by: GroverParkGeorge on Thu Apr 9 2:09:40 EDT 2009.
Go to the top of the page
 
srp7777
post Apr 9 2009, 02:31 PM
Post#5



Posts: 5
Joined: 7-April 09



I need the actual number for each month. I've attached a sample of the database and the query "YTD Count of Properties" is the result I need to out into a report. Thanks again for you help.
Attached File(s)
Attached File  DB1 04-09-09.zip ( 50.75K )Number of downloads: 8
 
Go to the top of the page
 
srp7777
post Apr 20 2009, 12:49 PM
Post#6



Posts: 5
Joined: 7-April 09



Does anyone have any thoughts?
Go to the top of the page
 
Sandi V
post Apr 20 2009, 01:45 PM
Post#7



Posts: 334
Joined: 21-July 05
From: Baltimore, MD


If I understand what you want, I think you can use the Crosstab Query Wizard to walk you through this. I did - see the query Property Data Crosstab. I went through the wizard and then added new criteria for Selling Status = NOT Sold, and removed the default to Group by.
Hope that helps - Sandi
Attached File(s)
Attached File  db1_2009-04-09.mdb.zip ( 57.18K )Number of downloads: 3
 
Go to the top of the page
 
RAZMaddaz
post Apr 20 2009, 02:11 PM
Post#8


UtterAccess VIP
Posts: 9,591
Joined: 23-May 05
From: Bethesda, MD USA


Look at the query YTD Count of Properties New. If you have any questions, let me know.
AZ
Attached File(s)
Attached File  db1_2009-04-09New.zip ( 21.19K )Number of downloads: 2
 
Go to the top of the page
 
srp7777
post Apr 24 2009, 01:02 PM
Post#9



Posts: 5
Joined: 7-April 09



This works great THANK!!!!
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th September 2019 - 02:03 PM