Forum home » Delegate support and help forum » Microsoft Access Training and help » Query in Access
|
|
|
The UK's most regular Training information: See also: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Query in Access
Resolved · Urgent Priority · Version 2003
Reply Mon 21 Jul 2008, 11:31Delegate Lisa said...
Lisa has attended:
Excel VBA Intro Intermediate course
Query in Access
You have data that is related many to one. So to do a report based on a query - you need just to show the up-dated records.
How do you do this!! :-(
For upcoming training course dates see: Pricing & availability
RE: Query in Access
Hi Lisa
Thank you for your question.
If I understand you correctly you want to run a query that simply shows records that have been recently updated.
Can you clarify for me the time period involved. Do you want to see records that have been updated on that day? or during the previous week, or do you want to specify the time period when the query runs
Thanks
Stephen
Reply Mon 21 Jul 2008, 13:10Delegate Lisa said...
RE: Query in Access
Hi,
What it is, is there is a comments table attached to a "item table". So every "item" may have several comments attached but all i would like is when i run a report is the last comment for that particular "item" to show up, at the minute all of the comments are showing up and i cant think how to reduce it down just to show the last comment on each item.
Thanks
RE: Query in Access
Hi Lisa
You need to add a date field to the comments table, which will hold the date at which the comment was made.(You could give the field a default value of Date(), so that the current date is always entered automatically)
You can then build a query containing all the fields you require for your report, with the date field added in last of all. (It should be the rightmost column in the query)
Then click on the totals button on the toolbar (This has the summation symbol on it) This adds in the group and totals row into the query design view. On this row in the date column change "Group By" to "Max". All the other columns should have group by entered in this row.
When you run the query it will now show the comment with the latest date
Hope this helps
Regards
Stephen
Reply Mon 21 Jul 2008, 14:22Delegate Lisa said...
RE: Query in Access
Hi,
I have tried this but its still bringing up 2 projects with 2 comments at different dates. - one date being 16.06.08 and one being 18.07.08.
Help.....please
Reply Mon 21 Jul 2008, 14:30Delegate Lisa said...
RE: Query in Access
I tried putting the 3 columns which i have the comments in to max and it works in the query the only thing is that when you close it and open it again it comes up with enter parameter value for the date............but if i put todays date in for example it just puts that as the date for all of the comments leaving me with the same problem plus no original date.....
Cna you help?
Reply Mon 21 Jul 2008, 14:53Delegate Lisa said...
RE: Query in Access
Hi,
Somehow this one has worked now.....been playing with it...thank you sooooo much for your help.
Thanks again
Lisa
Reply Thu 25 Sep 2008, 10:38Delegate Lisa said...
RE: Query in Access
I have tried this but its still not working - it picks up all of the records not just the latest one.
If there something else which i could try?
or is it because i have used the date() in the date column as it worked before this...
Thanks
Lisa
|