RE: Appointments!

	Home Register Search Help Login

Recent Posts
Todays Posts
Most Active posts
Posts since last visit
My Recent Posts
Mark posts read

Sponsors
Shopping Cart Software
Ecommerce software integrated into Frontpage, Dreamweaver and Golive templates. No monthly fees and available in ASP and PHP versions.
Website Templates
We also have a wide selection of Dreamweaver, Expression Web and Frontpage templates as well as webmaster tools and CSS layouts.
Frontpage website templates
Creative Website Templates for FrontPage, Dreamweaver, Flash, SwishMax

RE: Appointments!

View related threads: (in this forum | in all forums)

Logged in as: Guest

Users viewing this topic: none

Printable Version

All Forums >> Web Development >> ASP, PHP, and Database >> RE: Appointments!

Page: << < prev 1 [2]


TexasWebDevelopers Posts: 722 Joined: 2/22/2002 From: Dallas, TX Status: offline	RE: Appointments! - 9/5/2009 23:56:55 Here is what you said you wanted: "I want to find a way using DRW, who does not have an appointment! Meaning not today or in the future!" and "I want to sort who does NOT have a new appointment." So let's make an english sentence that we can convert to code: "I want to select the first and last names of everybody from the appt table who is enrolled in AC who does not have an appointment today or 60 days in the future and display their name only once in alphabetical order." So here is the code: SELECT DISTINCT firstname, lastname, ptnumber, enrolled FROM appt WHERE enrolled = 'AC' AND WHERE nextapt NOT BETWEEN Date() And Date()+60 ORDER BY lastname ASC; Now it WILL show dates before todays date....that's what you are asking it to do! How else are you going to find a name that DOESN'T have an appointment today or within the next 60 days?? The only data left is folks who had an appointment BEFORE today! You don't distinguish the data in any other way. As a matter of fact, the original solution of Swoosh with the DISTINCT added will work equally as well: SELECT DISTINCT firstname,lastname FROM TableName WHERE NextApt<Date() ORDER BY lastname; _____________________________ Follow us on TWITTER (in reply to box)


box Posts: 167 Joined: 12/10/2002 Status: offline	RE: Appointments! - 9/6/2009 0:54:36 fp_sQry="SELECT DISTINCT firstname,lastname, ptnumber, enrolled FROM appt WHERE nextapt < #::todaydate::# AND enrolled = 'AC' ORDER BY lastname ASC" fp_sQry="SELECT DISTINCT firstname,lastname, ptnumber, enrolled FROM appt WHERE nextapt < date() AND enrolled = 'AC' ORDER BY lastname ASC" Both returned exact same results! (in reply to TexasWebDevelopers)


swoosh Posts: 1535 Joined: 5/18/2002 From: Beaver Falls, PA Status: offline	RE: Appointments! - 9/6/2009 14:40:58 Can you attach the results as a screenshot? _____________________________ Swoooosh Just Do It! (in reply to TexasWebDevelopers)


box Posts: 167 Joined: 12/10/2002 Status: offline	RE: Appointments! - 9/7/2009 1:44:32 The result displays First and last names and their number after you click on their number which is hyperlink to their appointment page you see that they have a future appointments also! That defeat the purpose since I what to display ONLY people without an appointment! (in reply to swoosh)


swoosh Posts: 1535 Joined: 5/18/2002 From: Beaver Falls, PA Status: offline	RE: Appointments! - 9/8/2009 8:42:45 Not exactly the screenshot I had in mind. Can you post a screenshot image of both pages you are seeing. Or if the page is published can you post a link. _____________________________ Swoooosh Just Do It! (in reply to box)


box Posts: 167 Joined: 12/10/2002 Status: offline	RE: Appointments! - 9/8/2009 12:07:12 Here is the problem! In this database a person might have many old appointments and many new appointments also you can find a person with many old appointments and not new one! We wanted to display the list of person who doesn’t have a new appointment? If we use this fp_sQry="SELECT DISTINCT firstname, lastname, ptnumber, enrolled FROM appt WHERE nextapt NOT BETWEEN #::todaydate::# AND #::todaydate::# +365 AND enrolled = 'AC' ORDER BY lastname ASC": Still will display people who have new appointment as well since the same person also might have an old appointments! Or if we use this: fp_sQry="SELECT DISTINCT firstname, lastname, ptnumber, enrolled FROM appt WHERE nextapt < #::todaydate:: AND enrolled = 'AC' ORDER BY lastname ASC": Still will display people who have new appointment as well since the same person also might have an old appointments! I need to find way like a function to check the person for all the appointments in the database and if the last appointment is less than today then display the name of that person! Any idea please? (in reply to box)


swoosh Posts: 1535 Joined: 5/18/2002 From: Beaver Falls, PA Status: offline	RE: Appointments! - 9/8/2009 12:32:39 Okay I get the hint that a screenshot image isn't going to happen. What the problem is I believe is your database structure. For example, from your last screenshot image earlier in this thread you had something like the following: Kelly K 10/10/09 Kelly K 8/15/08 Therefore when you give it the query using the NOT BETWEEN it will work, only it will pull out Kelly K for the nextapt of 8/15/08. However, you say that then you go to an appointment page and then you see that Kelly K does have an appointment upcoming this year on 10/10/09. The query is working box! it is only displaying the initial records that DOES NOT have an appointment between those dates. However, because you have multiple entries in your system it cannot do what you want it to do because of the multiple entries of the same person. Why not have two fields. One for Last Appt and another for Next Appt. for the same record. Thus, eliminating duplicate customer entries. Or even replacing the Next Appt value overtop the old one instead of inserting a new record for it. Once again, we can't see what your looking at which is why I thought maybe a screenshot would give us a better idea to at least view what your viewing. NOTE: if however you need to keep track of older appts for customers then you need an entirely different structure < Message edited by swoosh -- 9/8/2009 12:43:10 > _____________________________ Swoooosh Just Do It! (in reply to box)


box Posts: 167 Joined: 12/10/2002 Status: offline	RE: Appointments! - 9/8/2009 13:37:20 Thanks for reply, having lastappt and nextappt wont work because one person may have several appointments in the future. I will work to get the screen shot but I don’t see how is going to help? What do you think about a function to check the latest appointment then see if the date is less than current date then display the list? So we get the name of person without any new appointment! (in reply to swoosh)


swoosh Posts: 1535 Joined: 5/18/2002 From: Beaver Falls, PA Status: offline	RE: Appointments! - 9/8/2009 13:49:48 If you do not need the old appts why not do an update query of all appts less than todays date to appear blank. Then you can query for those who have a null value for next apt. < Message edited by swoosh -- 9/8/2009 13:55:32 > _____________________________ Swoooosh Just Do It! (in reply to box)


TexasWebDevelopers Posts: 722 Joined: 2/22/2002 From: Dallas, TX Status: offline	RE: Appointments! - 9/8/2009 15:01:58 I see what you want to do: compare the ptnumbers of those who have had appointments in the past but do not have them in the future. However, your database is not built to easily allow that query. Essentially you are limited to comparing dates...and that is not really what you want. There are a number of solutions but both follow along the same path. This sql gives you a recordset with the future appointment. (name it RS1): sql = "SELECT DISTINCT ptnumber, firstname, lastname FROM [table] WHERE nextapt BETWEEN Date() AND Date()+365" This sql gives you the recordset of unique past appointments (name it RS2): sql = "SELECT DISTINCT ptnumber, firstname, lastname FROM [table] WHERE nextapt <= Date()" Then create a recordset that does an inner join on both of those recordsets to filter out the future appt from the past appts: SELECT Rs1.ptnumber FROM Rs1 LEFT JOIN Rs2 ON Rs1.ptnumber=Rs2.ptnumber WHERE Rs1.ptnumber<>Rs2.ptnumber" I haven't tested it so the last SQL may be incorrect but you get the drift. Joining recordsets is a pain...it might be easier to use "make table" instead...this creates temporary tables with the data you want to join. Other than that, a database redesign is the way to go. I would create a table with customer information and a table with appointment information and relate them using the ptnumber as the key. _____________________________ Follow us on TWITTER (in reply to swoosh)


box Posts: 167 Joined: 12/10/2002 Status: offline	RE: Appointments! - 9/10/2009 15:48:30 <!--#include file="../_fpclass/fpdblib.inc"--> <% fp_sQry="SELECT * FROM appt0 WHERE (ptnumber = '::ptnumber::')" fp_sDefault="" fp_sNoRecords="" fp_iMaxRecords=256 fp_sDataConn="pt" fp_iCommandType=1 fp_iPageSize=0 fp_fTableFormat=False fp_fMenuFormat=False fp_sMenuChoice="" fp_sMenuValue="" fp_iDisplayCols=1 fp_fCustomQuery=False BOTID=0 fp_iRegion=BOTID %> <!--#include file="../_fpclass/fpdbrgn1.inc"--> <%Response("UPDATE DISTINCTROW appt SET firstname='::firstname::',lastname='::lastname::', enrolled='::enrolled::' WHERE (ptnumber = '::ptnumber::')%> <!--#include file="../_fpclass/fpdbrgn2.inc"--> I want to create an temporary table, one person with one appointment only this way I wont have any issue to sort people without appointment. How can I use the update sql in here? <!--#include file="../_fpclass/fpdbrgn1.inc"--> <%Response("UPDATE DISTINCTROW appt SET firstname='::firstname::',lastname='::lastname::', enrolled='::enrolled::' WHERE (ptnumber = '::ptnumber::')%> <!--#include file="../_fpclass/fpdbrgn2.inc"--> (in reply to box)

Page: << < prev 1 [2]

All Forums >> Web Development >> ASP, PHP, and Database >> RE: Appointments!

Page: << < prev 1 [2]

Jump to: 1

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