More ASP and Access problems. Please help ASAP

[artsapimp]

I am building a ticketing system and am stumped. I am using access as the database and have 9 tables connected through relationships. I have 1 table called "tickets" where 2 of the fields are "Opened_By" and "Closed_By" both pointing to a "Rep" table. If I use 1 of the relationships it works fine. Is there a way to use both in the same SQL statement? If so, how would I designate which ones I'm referring to when I write "Rep_First_Name"? Thanks for any help.

... And Happy Holidays.

[UncaDanno]

Wild guess here, but I assume that when you say tickets.Opened_By and tickets.Closed_by point to Rep, you mean you have a code in those colums that correspond to, say, something like rep.Rep_ID. Am I close?

If so, you could try something like
SELECT open_rep.Rep_First_Name as Open_Rep_Name, close_rep.Rep_First_Name as Close_Rep_Name, ticket.Ticket_Number FROM tickets INNER JOIN rep AS open_rep ON open_rep.Rep_ID = ticket.Opened_By INNER JOIN rep AS close_rep ON close_rep.Rep_ID = ticket.Closed_By WHERE ticket.Ticket_Number = the_ticket_i_want

"rep AS open_rep" and "rep AS close_rep" give the two references to the rep table the uniqueness they need to prevent the "ambiguous reference" error you're likely to get if you don't use "AS".

Hear it got cold down your way last night.....

[artsapimp]

Thank you. That is probably exactly what I need. It kind of makes sense to me so I will work with it. I will most likely have a question or two in the future, but I'll give it a shot on my own from here.

Last night wasn't really bad. I think it was somewhere in the high 30's with the wind-chill factor. 3 mornings ago it was single digits with the wind-chill. That was the coldest I remember here in Florida.

[artsapimp]

Where you have "open_rep.Rep_First_Name as Open_Rep_Name" I am assuming "open_rep" is the table? If so this isn't the problem I'm having because Open_Rep and Close_Rep are both coming from the same table called NDCRep (National Data Center Rep). I have the fields "NDCRepID", "NDCRep_Last_Name", and "NDCRep_First_Name" in the NDCRep table. It is associated to the "tickets" table twice.

Tickets.Closed_By is a "number" which is associated with NDCRepID. Tickets.Opened_By is also a "number" which is associated with NDCRepID.

I know how to use one of them at a time, it's when I put ...rst(" NDCRep_Last_Name ") it doesn't know if I mean Opened_By or Closed_By.

Thanks for any help.

[mosquito]

Ok, here we go, I assume that you have the following tables:
tickets(Ticketinfo, Opened_by, Closed_by)
NDCRep(NDCRepID, NDCRep_Last_Name, NDCRep_First_Name)

To Give the ticketinfo, you use the following query:
SELECT t.ticketinfo, r.NDCRep_Last_Name as OB_Name, r.NDCRep_First_Name as OB_FrstName,
r2.NDCRep_Last_Name as CB_Name, r2.NDCRep_First_Name as CB_FrstName
From tickets t
inner join NDCRep r
on r.NDCRepID = t.Opened_by
inner join NDCRep r2
on r2.NDCRepID = t.Closed_by

You can get the "Opened by" name by doing
response.write(rst("OB_Name") & " " & rst("OB_FrstName"))

To get the "Closed by" use this code
response.write(rst("CB_Name") & " " & rst("CB_FrstName"))

I hope this is what you mean. If not, post more info on what you want.

[Edited by mosquito on 12-22-2000 at 12:03 PM]

[artsapimp]

That is it. Thank you very much.

I do have another problem though. This is an error I'm getting when trying to just open Updates from a different table. Here is what I've got so far. thanks for any help.


strUpdateSQL = "SELECT Follow_up.TicketNumber, Follow_up.Update, Follow_up.Update_Date, Follow_up.Update_Time, NDCRep.NDCRep_First_Name, NDCRep.NDCRep_Last_Name"
strUpdateSQL = strUpdateSQL & " FROM NDCRep RIGHT JOIN Follow_up ON NDCRep.NDCRepID = Follow_up.NDCRep"
strUpdateSQL = strUpdateSQL & " WHERE Follow_up.TicketNumber = " & Request.QueryString("TicketNumber")


The error I'm getting is this...


Microsoft JET Database Engine error '80040e07'

Data type mismatch in criteria expression.

/tickets1/ticket.asp, line 141


I thought it was because the table was called update which is some "special" word so I changed it to "follow_up" and that didn't fix it either. Any help would be great.

thanks again for the previous help.

[UncaDanno]

Good follow-up, mosquito. A bit easier to read than what I was going to reply with.

Art, the problem is that Request.QueryString("TicketNumber")is passed as a variant to the database. And the ticket number is stored as a number.

Try:
" WHERE Follow_up.TicketNumber = " & cint(Request.QueryString("TicketNumber"))

[artsapimp]

That was exactly it. I changed the field from text to number and added the Cint and it worked. Thank both of you for all of your help.

I hope I don't have to ask any more questions.

Have a great holiday.

[mosquito]

Be carefull, with using Cint, the number is converted to integer. If the value exceeds 32000 (its a bit more than that) the system will raise an error. Use the conversion function to the datatype of your table field to be sure.

Cint() for integers,
Clng() for Longs,
CDbl() for Double,
...

It never hurts to be carefull.

[artsapimp]

That's true. Thanks.

This ticketing system is only SUPPOSED to be temporary. In March there should be a better one taking over. I would be surprised if we had 15000 by that time. Thanks though.

[UncaDanno]

"This ... system is only SUPPOSED to be temporary."

Oh, how many times over the years have I heard THAT litany!

Welcome (again) to the real world of development, Art! I know of apps I helped build back in the mid '80s that were supposed to be stand-ins until "the real thing" was built. The stand-ins (patchwork Frankensteins all) are still in operation.

[artsapimp]

Wow, in the 80's I was nervous about what to wear on my first day of Pre-School.

But I do understand. The reason I have to build this system is because our previous ticketing system is really flat and takes up too many resources (IDC, HTX Technology). We already have a contract with a company that was supposed to have one here by December, Then January, Now March... We're just not able to wait that long with the terrible one we're using now. This new system has to come eventually because of the contract that's already agreed upon, so hopefully mine will be temporary.

I hope everyone had a good Holiday!