Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 3rd, 2008, 09:37 AM
Authorized User
 
Join Date: May 2007
Location: , , .
Posts: 85
Thanks: 0
Thanked 0 Times in 0 Posts
Default Query issue

I've messed around with this for a while and still no joys. I am trying to get access 2000 to bring out the right results

For each employee select the 67th percentile value of ascending ordered field of orders placed between certain dates.

the date field criteria is normally "BETWEEEN Date() and Date()-365" or "BETWEEN Date() and Date()-180"

the fields are

intcaseid this field only appeared as a check on what access was doing
intemployeeid
dtsubdate
txtapr

I only want 1 result for each employee so the resultant would look like

J Bloggs 8.9%
A Smith 10.1%
A Other 7.5%
S Butts 6.9%

If I run the query with a specified employeeid e.g. "WHERE employeeid=5", I get a very different result if I group on employeeid.

E.g. I get grouped "J Bloggs 7.5%" and specific "J. Bloggs 8.9%"



Reply With Quote
  #2 (permalink)  
Old June 6th, 2008, 10:40 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

What does the whole SQL string look like in both cases? What code are you using to launch event query?

mmcdonal

Look it up at: http://wrox.books24x7.com
Reply With Quote
  #3 (permalink)  
Old June 13th, 2008, 10:58 AM
Authorized User
 
Join Date: May 2007
Location: , , .
Posts: 85
Thanks: 0
Thanked 0 Times in 0 Posts
Default

SELECT TOP 67 PERCENT lkpqryarname.arname, Max(tblfileinspect.caseapr) AS MaxOfcaseapr
FROM tblfileinspect INNER JOIN lkpqryarname ON tblfileinspect.arfirm = lkpqryarname.arid
WHERE (((tblfileinspect.caseapr) Is Not Null And (tblfileinspect.caseapr)>1) AND ((tblfileinspect.ffdate) Between Date() And Date()-90))
GROUP BY lkpqryarname.arname;

That's the latest attempt at it.

Reply With Quote
  #4 (permalink)  
Old June 16th, 2008, 09:54 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

It's hard to tell without seeing your table structure. A couple of comments.

You have

WHERE (((tblfileinspect.caseapr) Is Not Null And (tblfileinspect.caseapr)>1) AND ((tblfileinspect.ffdate) Between Date() And Date()-90))

Well, if you have

tblfileinspect.caseapr > 1

you don't need

tblfileinspect.caseapr Is Not Null

because the first requirement will ensure that the field is not null. Also, you used a BETWEEN in your criterion but your dates are backwards. I wonder what Access will do if your end date is AFTER your start date. Swap those.

tblfileinspect.ffdate Between DateAdd("d", -90, Date()) And Date()

(aside)
Say, there are balloons and confetti coming off the ceiling. Oh, this is my 1000th post.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
Reply With Quote
  #5 (permalink)  
Old June 18th, 2008, 04:22 AM
Authorized User
 
Join Date: May 2007
Location: , , .
Posts: 85
Thanks: 0
Thanked 0 Times in 0 Posts
Default

yeah i tried it without the null a few months ago. Unfortunately found I was getting numerous blank entries without the "is not null" in there. This then throws out the result

Reply With Quote
  #6 (permalink)  
Old June 18th, 2008, 04:46 AM
Authorized User
 
Join Date: May 2007
Location: , , .
Posts: 85
Thanks: 0
Thanked 0 Times in 0 Posts
Default

the table structure consists mainly of yes no fields (around 100 with a total of 160 fields in all at the moment and ever expanding as the law changes) as its for checking the contents of a case and the quality & nature of information recorded.

The other fields of the table aren't involved in the query. I realise that normalisation would regularly dictate making this into seperate tables however it just doesn't really seem to work.

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Issue with displaying result of the query -JSTL shimmeringtrinkets BOOK: Beginning JavaServer Pages 0 June 24th, 2008 08:31 AM
Access and SQL query Issue ayazhoda Access VBA 0 May 16th, 2008 07:54 AM
Query issue Grafixx01 Access 4 April 12th, 2007 07:52 AM
Append Query issue gherkin Access 3 August 7th, 2006 10:18 AM
Access VBA Delete Query Issue... snoopy92211 Access VBA 4 June 24th, 2004 03:57 PM



All times are GMT -4. The time now is 08:56 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.