sql action question

[gcapp]

I am having a problem with an SQL statement and I wondered if someone may have a suggestion.

I have a drop-down list on "pageA.asp" with the action set to pageB.asp" that displays all the departments in my county,
and on pageB.asp I have my sql that displays the addresses of the departments.

Now in each record that displays, there is a link that takes you to "pageC.asp".

On pageC I need to list all the employeesk at each address (from pageB records).

In the sql on my pageC.asp is there a way that I can use a WHERE clause that will grab the employees that are at each address AND from each department?

Example: I have this WHERE clause on pageB to grab the records from the drop-down on pageA where it displays the departments and the addresses of each.

WHERE DepartmentID.DeptName LIKE '" & Request.Form("departments")

Now on pageC is there anyway to use a WHERE clause to grab the records from pageA, since the action on pageA is set to go to pageB??

What I need to do is to be able to list records on pageC that are employees of each department from each address.

Hope this isn't confusing.

If anyone can help, I would appreciate it.

Gary

[raf]

This is very confusing.

First of all, why do you use a "like" as operator instead of "=" ?
Second: the sql statement you will need, depends or your db-design. So wa can't actually tell you what stetement you need.

I dont quite understand wy you should need the departement. (the screen A info). Each address wil be linked to only one departement, and each employee to 1 adress. No ?
So the adress alone should be anough to select all the employees. No?

[gcapp]

Raf,
I knew this would be confusing. Where can I get help on this? It's kind of a long explanation. I would even be glad to pay somebody $50 to help me. I'm sure it wouldn't take an expert long at all to figure this out. I think it's just too long to describe here. Unless I attach a text file that explains everything, but I don't want to get anyone mad in this forum, so I'll wait to hear from you.

Thanks

[raf]

Unless I attach a text file that explains everything, but I don't want to get anyone mad in this forum, so I'll wait to hear from you.

Why would anyone mind if you attach a textfile of ten pages ?
Those who want to look into it, will download it.
So go ahead.

To set you completely at easy:

Please, don't get mad at him. I'm asking you nicely. raf :)

[gcapp]

Ok, if anyone out there can take a look at the attached file and help me with what I am trying to do, it would be greatly appreciated. If you look at it and need more detail, please reply to the forum and I will give more detail.

I have been working on this for two weeks and am stuck.

Thanks,
Gary

[raf]

I'll look in to it at lunchtime or this evening. (9.30 int he morning now). I need to draw it out to get my head around it.

Looks to me you've overcompliceted your db. A star scheme design will probably be better. I'll let you know if i've found anything.

[raf]

OK. I took a look at it.

2 option:
- change the db design (you’ll run into problems later on, if departments grow or a new superdepartment is created. This design isn’t flexible enough). What’s more, i can’t imagen there our thousands of departments or subdepartmens, and a table of a few hundred of records is peanuts for a db)
-select on subdeptID

My advice is to change the db design :(

Else,

On page 1 you select a department. Page two displays the addresses of the subdepartments (adress is a link to the amployees)

Page 1 should post the DeptID.
Page 2 selects all subdepartmens with that DeptID

Sql=select * from SubDepartments where DeptID = thedept”
Sql=replace(sql,”thedept”,request.querystring(“dept”,”’”,”’’”)))

(note: code is VBscript)

You display the adresses and the link,that holds the subdeptID

<a href=""ind_listing.asp?subdep="&rsPhoneResult.Fields("SubdeptID")&""">

On the last page, you select all emlployees with that subdeptID

Sql=select * from PhoneNumbers where SubDeptID = thesubdept”
Sql=replace(sql,”thedept”,request.querystring(“SubdeptID”,”’”,”’’”)))



Nothing more to it then that.

Or whasn't this the thing you wanted to do?

[gcapp]

Raf,
You have what I am talking about almost correct. The SubDept is something that I haven't thought about doing anything with yet, because I'm so confused right at this point. You see the SubDept is actually a sub-department of the main department. So for example, the Aging Department has several locations (this has nothing to do with the SubDept)., then there are also sub-departments within Aging, like Nutrition Program.

So for now, I'm ignoring the SubDept issue.

So everything you told me to do, I would replace the SubDept for just DeptID (I think).

But I'm confused on a thing or two.

You say post the DeptID on page one. Now the first page (index.asp) has the drop-down menu of all the departments. How do you mean post the DeptID, in a hidden value?? And if that is what you mean, how do you do that?

So on the second page I have just the addresses of each department and the link to get to the employees from that address and that department. How would I do that? You see in just the Aging Department there are hundreds of people who work for that department, but maybe 80 of them work at one location. So when you click on the link, on page three it will display those 80 employees from that particular address.

So I don't know if this changes what you told me to do. I'll still mess with it here, but any other help you can give I would appreciate.

I'll give you a small example of what I need the pages to do, maybe that will be easier.

On page 1, the drop down-menu will have "Aging" in it. You select it and on page two you get:

Aging

303 Court St.
Olean, NY 14760
Click here to get individual listings

OR

1700 Lincoln Ave.
Little Valley, NY 14755
Click here to get individual listings

So then if you click on the first link - on page three you get a listing of employees from that address or if you click on the second address link, you get the employees from that address.

Hopefully you understand what I'm trying to do.

If you have any further help, I would appreciate it.

Gary

[raf]

I think i’m starting to understand it.

So the pageflow is:
Page 1, dropdown hit department --> addresses of department, link to employees --> select and display employees.

Hmm.
This LocationID in the PhoneNumbers table, that can be joined with the ID in the Towns table. OK
The Town1 etc in the Departments-table, those are text? So i presume those are the text from the towns table?
Well, it would be the easiest (and smartest) to save the Towns ID’s in the departmentstable (for numerous reasons like updatebillity and speed !)


Now what would then happen. You select the department. The departmentID is posted to the second page. Posted means you use the “post” method instead of the “get” method.
You then select the addresses and towns from the departmentstable
Sql = “Select adresses and towns from Departments where ID=theid”
Sql = replace(“sql”, “theid”, request.form(“dropdownname”)

Display the adresses and add a link to it with the town value in the querystring + you add the departmentsID to the querystring

<a href="ind_listing.asp?dept=<%=rsadresses.Fields("deptID")%>&town=<%=rsadresses.Fields("town1")%>">

Now how do you get thevalue for town1 under address1? Well 2 possibilitys:
- build an array and then proces that row by row;
- use a nested loop that checks for the last caracter.


When they hit the link, you select all employees with that departmentID and that town value as a Location.

Sql = “Select * from PhoneNumbers where DeptID=theid and Location=thelocation”
Sql = replace(“sql”, “theid”, request.querystrind(“dept”)
Sql = replace(“sql”, “thelocation”, request.querystring(“town”)


If you use the text (description) in the departmentstable, you need to change the label to the value first.

Select * from PhoneNumbers where DeptID=theid and Location IN (Select ID from towns where Name=’thelocation’)”
Sql = replace(“sql”, “theid”, request.querystrind(“dept”)
Sql = replace(“sql”, “thelocation”, request.querystring(“town”)


Let me know if you still have questions/problems

[gcapp]

Raf,
If this is something that is too confusing and you know someone that is willing to do this for me for some cash, let me know. I'd liek to try and do this and whether it takes my database to be changed around or whatever, I'd like to complete this.

Let me know.
Gary

[raf]

Did you try what i said in my last post?

It's pretty straightforward. If you want, i can write it for you.
Wat db? MySQL or Access?With PHP or ASP?

[gcapp]

Raf,
I think this is over my head. I keep trying things and it's not working right, I'm getting errors and I want to scream!
Let's try another approach. I have attached a text file that shows my code for each page and maybe that will smooth things out. In there is also one record from each table so you can see how they are in there. I still think I may have confused you on some of the tables.

Now in the attached file you will see how my sql statements are on phoneresult.asp and ind_listing.asp. I have them the way they are because I used Access and these queries gave me the results I wanted.

I implemented the suggestions you made on the last post.

You will also notice that in the Departments table that I have fields for Town1, Town2, etc. I put just text in those fields instead of a number identifier because I couldn't seem to get the right Town to display with the code on the phoneresult.asp page. You'll notice in that code that I have "Town1" to display for the first listing, "Town2" for the second listing.

I'm sure this is part of the problem, but i'm not sure how to get it to work right.

So if you can help, I would really appreciate it. If not and you know someone that can help, again it would be appreciated.

Thanks,
Gary

[raf]

try replacing the link you wrote with this one

response.write("<br><font size='2'>Click <a href='ind_listing.asp?thedept="& rsPhoneResult.Fields("DeptID") & "&town=" & rsPhoneResult.Fields("Town1") & "'><b>here</b></a> for Individual Listings</font>")


You've missed some single quotes, added a few double quotes and forgot the & between the two variables.

Do you have this stuff running somewhere? I don't have time to set up a db and code the pages to try this out.
So uyou'll need to tell me what exactly doesn't work. Which errors when etc

[gcapp]

Raf,
I fixed that but now I get a problem with my sql in the phone result.asp page. I just think that someone (an it doesn't have to be you) needs to look at this because something is definitely wrong.

I think the table fields and what code you are telling me to use are not in sync and maybe even a misunderstanding of what I need to be done.

The error I get now is:
No value given for one or more required parameters and I tried to chaneg the sql and nothing - still the same.

Like i said, I'd be glad to pay someone to look at this.

You tell me what to do cause I'm stuck.

[raf]

send me the complete code of that page + what fields from which tables need to be returned, and i'll have a look at it.

[gcapp]

Raf,
I appreciate you looking this. Ok, because I figure more questions may be coming, I've attached a zip file with the three pages I am using and the database structure again.

Now the index page works fine (unless you see a need to change it)

The phoneresult page: between the dashed lines is what is displayed on that page. The fields in there are what needs to be there and they are all taken from the Departments table. Now in the Departments table there is one record that has 4 addresses, 4 towns, 4 phone numbers and 4 faxes - - that is why I have the code the way it is - to accommodate that record. A few departments have 3 addresses, some 2 and some just one. So I made the code that way to display as many addresses as each department has. This may be changed if you want. Like I said, I need that page to display all the addresses for that particular department.


The ind_listing page: here is used variables to display the records. The way that is, is fine. But the sql is probably wrong. I have the sql ORDER so it will list the employees by rank, then listing. Some employees are department heads and need to be on top.

I can tell you that the PhoneNumbers table has over 1000 records because it has all the employees in the County. Don't know if that matters, just thought I would mention it.

Again, I don't what to do to thank you for looking at this. Like I said, I am not opposed to paying for this if i have to. If you feel you need the actual Access database, I can send it with reduced records in it. These pages are obviously done in ASP.

Thanks
Gary

[raf]

gcapp,

this code can't be right.:confused: :confused:

sqlPhone = "SELECT DISTINCT DeptName FROM DepartmentID ORDER BY DeptName"

sqlphone = "SELECT * FROM PhoneNumbers, TownID " &_
"WHERE DepartmentID.DeptID = theid"


sqllisting = "SELECT PhoneNumbers.DeptID AS PhoneNumbers_DeptID, DepartmentID.DeptName, TownID.Town, PhoneNumbers.SubDeptID, PhoneNumbers.Rank, PhoneNumbers.Listing, PhoneNumbers.Title, PhoneNumbers.Email1, PhoneNumbers.Email2, PhoneNumbers.LocationID, PhoneNumbers.Phone, PhoneNumbers.Extension1, PhoneNumbers.Extension2, PhoneNumbers.Extension3 " & _
"FROM DepartmentID INNER JOIN (TownID INNER JOIN PhoneNumbers ON TownID.ID = PhoneNumbers.LocationID) ON DepartmentID.DeptID = PhoneNumbers.DeptID " & _
"WHERE DeptID=theid and Location IN (SELECT ID FROM Towns WHERE Town='thelocation') ORDER BY PhoneNumbers.Rank, PhoneNumbers.Listing"

The first two aren't tablenames and why an alias on the third one. The joins don't look right neither.

I think the fastest way to solve this is that you zip me the three asp pages with a reduced access-db and then i'll set it up here and write the code. (won't take long but i'll only be able to do this tomorrow evening, unless you sent i right away)

[gcapp]

Raf,
Oooops, actually I changed the names of the tables to make it easier for you to understand. I then would have changed it to accommodate how i named them.

Anyway I changed the names of the tables to the names that I originally told you. Originally, my pages were also named different to mak eit easier for you to understand. So I also changed all the sql's to match the page names and tables that I originally told you.

The pages that I sent you are complete as far as the ASP code goes. The rest of each page is just html, so if you use the pages i sent here, that will work.

I can't however attach the database. Even as small as it is, and zipped, it still exceeds th elimit in this forum. Maybe if you have an email or somewhere else I can send it, it would work.

Gary

[raf]

I can't imagen it being to big (just 3 alomst empty tables and a few scripts?)

Well, i've sent you a Personal Message so sent it to that adress. Try to keep it as small as you can since it's a free mailservice with a limit.

[gcapp]

Raf,
If you sent a personal message it may have been to an old email address. If you could send another to gacapp@adelphia.net then I will send you my database and pages.

Thanks again,
Gary

[gcapp]

Raf,
I emailed you my stuff. Again, thank you so much.

Gary

[raf]

I've fixed the asp pages and it works now.

I've put the connectionstrings in an include. Db adress is same as in original pages.

I've trown out some code i didn't need or that didn't make any sense to me. I've tested it and it works. Haven't add any layouting or stuff.

Let me know if it doesn't work.


Fixed pages and include in attach