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!! :-(
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
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
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
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?
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
