|
| |
|
|
whitec00
Posts: 32
From: Dallas Texas USA
Status: offline
 |
Displaying last record - 2/4/2002 13:50:04
This post goes in line with a previous one from ScribeVision, but I cannot grap the concept of all the answers that were posted to the original post so I am posting anew in my terms:
I have a database that my users are posting to via the Intranet. Each day the user must contact a client and record the event in a 'comments' database. The 'comments' database records the [Account Number],[Account Name],[Date of Contract],[Sales Rep],[Date],[Comment].
Realizing that there can be anywhere from 1 to 100 entries or more.
I want to write a query that will display all the fields listed above, but only the most recent record.
The additional problem I see here is that if a user was not calling his/her clients, then the record that displayed would be an old one, or if the user had not called his/her client at all, then no record would display. I also have an Account Demographic database that I could use to report on, but how would I combine the two databases together so that the report would list the account demographics from the Accounts database and the comments from the comments database, but only display the most recent comment and date?
Does this make sense ???
All this, I want done in FrontPage and to answer ahead of time, I pretty much have the spooky diet memorized now so if I need to incorporate that, let me know....
Thanks in advance if you can help.
|
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: Displaying last record - 2/4/2002 14:01:14
Don't know if this will fit your scenario, but I would build a standard DRW sorted on the date decending, then just display 1 record...
Hope it helps...
| |
|
|
|
whitec00
Posts: 32
From: Dallas Texas USA
Status: offline
|
RE: Displaying last record - 2/4/2002 14:16:02
rdouglass,
This certainly worked in terms of showing the most recent record by sorting by date descending. But it only showed one record. I know that I might have 13 different accounts that I call on a daily basis. Your solution does not show the thirteen accounts that I might have called. It only shows the one record because I limited the results to "1".
I need to see all of my accounts that are assigned to me, plus I need to see the last comment made on each account. I think I am going to somehow have to combine my account demographics table with my comments table so I can list all accounts but only the last comment made on the account. Any ideas on combining queries?
whitec00
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: Displaying last record - 2/4/2002 14:56:56
I'm sorry - I must have missed the problem...
I have had good luck building the query INSIDE the DB (especially if both your tables are inside the same DB), then just call the Query instead of the Table. In FP, you don't necessarily have to call the table, you can call a query instead. Does that help???
| |
|
|
|
whitec00
Posts: 32
From: Dallas Texas USA
Status: offline
|
RE: Displaying last record - 2/4/2002 14:59:29
How do you call a query from an Access database?
Keep in mind that the database is being served by IIS and not treated
as if it were on a network drive.
Tell me more !!!!
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: Displaying last record - 2/4/2002 16:19:45
You won't be calling a query from Access - you will be calling an Access query from ASP. And yes, it will still be done thru IIS and not a network share.
If it IS an Access DB, you should be able to open the site live, open the fpdb folder (if that is where your DB is - that is the default location), and double-click to open the db file in Access. Then (in Access not FP), build your query INSIDE the Access file, close and save the DB, and call the query (instead of the table(s)) from FP using step 2 of the DRW. You can set up all your relations and criteria from within Access. IMO, that is the easiest way to query multiple tables with the DRW.
You'll see queries listed with the tables (in Step 2 of DRW) and have the word '(VIEW)' tacked onto the name. Try it with 2 simple tables and you'll see what I mean....
| |
|
|
|
whitec00
Posts: 32
From: Dallas Texas USA
Status: offline
|
RE: Displaying last record - 2/4/2002 17:55:29
Okay, cool !!! So now I can run queries without having to know all the SQL commands.
This is good !!!
But......
SELECT TOP 1 tblclient.acct_number, tblclient.acct_name, tblComments.ID, tblComments.Comment
FROM tblclient INNER JOIN tblComments ON tblclient.acct_number = tblComments.acct_number
ORDER BY tblComments.ID DESC;
I still have a problem when I create my query to display the last record ID from my comments database. It is only listing one account.
I want to list all of my accounts in the client table and in addition post the last comment made in the comment table. The two tables are related by acct_number.
Is this possible???
| |
|
|
|
Spooky
Posts: 26606
Joined: 11/11/1998
From: Middle Earth
Status: offline
|
RE: Displaying last record - 2/4/2002 19:05:56
So loose the TOP 1, and when the first display loop has ended, dont show any more comments.
In your code, when the comment gets displayed, do this .
<%If CommentWritten = True then
'Dont show the comments field
Else
' Show the comments field
CommentWritten = True
end if
%>
§þððk¥
Database / DRW Q & A
VP-ASP Shopping cart
Spooky Login
| |
|
|
|
todd2k
Posts: 1
Joined: 2/5/2002
From: Sugar Land TX USA
Status: offline
|
RE: Displaying last record - 2/5/2002 21:26:02
What if you do a left outer join and group on account?
Todd
| |
|
|
|
whitec00
Posts: 32
From: Dallas Texas USA
Status: offline
|
RE: Displaying last record - 2/7/2002 17:11:58
Spooky / Todd,
I have to admit that I am not an expert so I do not know how to implement the code Spooky suggested, nor do I know what a Left Outer Join is.
If you can guide me through this, I would be obliged, if not, then send me back to the porch with the other porch puppies so I can watch the big dogs play...{:-O
| |
|
|
|
Spooky
Posts: 26606
Joined: 11/11/1998
From: Middle Earth
Status: offline
|
RE: Displaying last record - 2/7/2002 21:59:29
First thing - can you make a DRW and display it in the format you need? (leave the comment thing for now)
§þððk¥
Database / DRW Q & A
VP-ASP Shopping cart
Spooky Login
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
