Wrox Programmer Forums
|
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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 7th, 2007, 02:34 PM
Authorized User
 
Join Date: Jul 2006
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Default query problem

hey all,

i am having a problem with a certain query which pulls records from two tables. one table contains info entered into a main form, and the other has information entered into the subform.

what i want the query to pull is all the records from the main table, and only the most recent record (IE latest date inputed into "date" field) from the sub table.

however what ends up happening is the query will pull all the records from the subtable. so instead of having say 5 records from the main form and 5 from the subform, i get 5 records from the main form but 20-30 from the subform!

does anyone know how to fix this

 
Old June 7th, 2007, 02:53 PM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Jonas,
How are the 2 tables indexed?

Kevin

dartcoach
 
Old June 8th, 2007, 07:51 AM
Authorized User
 
Join Date: Jul 2006
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Default

hey kevin,

here is the code that is on the query, as of right now it is pulling the correct record from the subform, however it is only taking it from the first main form record. my report has one line basically, but should have more :S

SELECT [Project Management].[Project Name], [Project Management].[Assigned to], [Project Management].[Implementation Start Date], [Status Updates Sub].Status, [Status Updates Sub].Date
FROM [Project Management] INNER JOIN [Status Updates Sub] ON [Project Management].ID = [Status Updates Sub].ID
WHERE ((([Status Updates Sub].Date)=(SELECT Max(Date) AS Expr1 FROM [Status Updates Sub];)))
GROUP BY [Project Management].[Project Name], [Project Management].[Assigned to], [Project Management].[Implementation Start Date], [Status Updates Sub].Status, [Status Updates Sub].Date, [Status Updates Sub].ID;


 
Old June 8th, 2007, 08:10 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Jonas,

Is there always a record in the sub table?

Kevin

dartcoach
 
Old June 8th, 2007, 08:18 AM
Authorized User
 
Join Date: Jul 2006
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Default

yes, i set the date to =Date() as the default value

 
Old June 8th, 2007, 08:23 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Try reversing your logic. Get the last updated record for the sub table, then match to the main table.

Kevin

dartcoach
 
Old June 8th, 2007, 12:28 PM
Authorized User
 
Join Date: Jul 2006
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I'm still having a little trouble. I want to pull the most recent date from the "status subform" pulls the project "name" from its main form. i will illustrate below what i would like to see:

Before Query:

Date Name
1/1/07 A
1/2/07 A
1/2/07 B
1/2/07 C
1/3/07 A
1/4/07 B

After Query:

Date Field
1/3/07 A
1/4/07 B
1/2/07 C

I tried using Max(Date) to get the most recent date, however it only pulls "1/4/07 B" which is the latest of ALL the dates, not just he "B" dates.

i tried matching it to the main table as well but that isn't working either :S

 
Old June 8th, 2007, 01:08 PM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Jonas,
Date comes from the sub table and Name comes from the main table?

Kevin

dartcoach
 
Old June 8th, 2007, 01:25 PM
Authorized User
 
Join Date: Jul 2006
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Default

yes, thats correct

 
Old June 8th, 2007, 01:29 PM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Which 1 is on both?

dartcoach





Similar Threads
Thread Thread Starter Forum Replies Last Post
Query Problem. rupen Access 3 April 27th, 2007 07:43 AM
Query Problem bundersuk VB Databases Basics 0 December 30th, 2006 07:50 AM
problem with query harpua Classic ASP Databases 1 January 24th, 2005 12:36 PM
Problem in query leo_vinay Classic ASP Databases 5 January 21st, 2005 06:32 AM
query problem mateenmohd SQL Server 2000 7 September 9th, 2003 11:58 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.