query help

[rkferri]

Hi all,
I'm trying to do something very simple but I'm having a major brain freeze. I have two access tables and I'm trying to read data from both and display the information. My primary key in both tables is personid. My first table(obits) I want to get firstname, lastname, personid, dateofdeath. The second table(Guestbook) I want to get personid, condolences.

My query:
<cfquery datasource="#DSN#" name="getobit">
SELECT Obits.Personid,
Obits.FirstName,
Obits.MiddleName,
Obits.LastName,
Obits.DateOfDeath,
Guestbook.personid,
Guestbook.condolences
FROM Obits, Guestbook
</cfquery>

Now everytime I run it I get the data coming back like this:
Frederking, Howard W. 02/25/2007 This is a test.
Smith, Amanda 02/02/2007 This is a test.
Frederking, Howard W. 02/25/2007 sflalsdjf;ajsfdjlasjdflajsf dlasdjflajsdfkjlasjflkjsafljd sfsa
Smith, Amanda 02/02/2007 jdsflalsdjf;ajsfdjlasjdflajsf dlasdjflajsdfkjlasjflkjsafljd sfsa
Frederking, Howard W. 02/25/2007 gfhdfghdfhfdgh
Smith, Amanda 02/02/2007 gfhdfghdfhfdgh
Frederking, Howard W. 02/25/2007 sadfasf
Smith, Amanda 02/02/2007 sadfasf

It's putting my data in a mess. I don't think I'm joining the tables correctly in my query. When I display it I only want the person's name and date of death to show once and all the condolences for that person to show. The name is repeating for each condolences and for some reason some of the condolences are not with the appropiate name.

Any help would be appreciated.

Thanks,
Kelly

 

[r937]

you just need to join the tables properly

<cfquery datasource="#DSN#" name="getobit">
SELECT Obits.Personid
     , Obits.FirstName
     , Obits.MiddleName
     , Obits.LastName
     , Obits.DateOfDeath
     , Guestbook.personid
     , Guestbook.condolences
  FROM Obits
LEFT OUTER
  JOIN Guestbook
    ON Guestbook.personid = Obits.Personid
ORDER
    BY Obits.Personid
</cfquery>

<cfoutput query="getobit" group="Personid">
<!--- print Obit columns (once only) --->
<cfoutput>
<!--- print condolences (multiples) --->
</cfoutput>
</cfoutput>

note: it's a LEFT OUTER JOIN in case there might be an obit without condolences

you know how a nested CFOUTPUT works? the ORDER BY is important to make it work correctly

r937.com | rudy.ca

 

[rkferri]

Thanks so much for responding!!

I put in the following query:
<cfquery datasource="#DSN#" name="getobit">
SELECT Obits.Personid,
Obits.FirstName,
Obits.MiddleName,
Obits.LastName,
Obits.DateOfDeath,
Guestbook.personid,
Guestbook.condolences
FROM Obits
LEFT OUTER
JOIN Guestbook
ON Guestbook.personid = Obits.Personid
ORDER
BY Obits.Personid
</cfquery>


and the following as my output:
<cfoutput query="getobit" group="Personid">
<table WIDTH="100%" cellpadding="3" cellspacing="1" BORDER="1">
<tr>
<Td WIDTH="25%" valign="top"><font face="arial" color="black" size="2">#LastName#, &nbsp;#FirstName#&nbsp;&nbsp;#MiddleName#</font></td>
<Td WIDTH="25%" valign="top"><font face="arial" color="black" size="2">#DateFormat(DateOfDeath, "mm/dd/yyyy")#</font></td>
<cfoutput>
<Td WIDTH="45%"><font face="arial" color="black" size="2">#Condolences#</font></td>
</cfoutput>
</cfoutput>

and I get the following error:
ODBC Error Code = S1000 (General error)


[Microsoft][ODBC Microsoft Access Driver] Type mismatch in expression.


SQL = "SELECT Obits.Personid, Obits.FirstName, Obits.MiddleName, Obits.LastName, Obits.DateOfDeath, Guestbook.personid, Guestbook.condolences FROM Obits LEFT OUTER JOIN Guestbook ON Guestbook.personid = Obits.Personid ORDER BY Obits.Personid"

Any ideas why??

 

[r937]

it appears you have a type mismatch

what are the datatypes of the two personid columns here?

r937.com | rudy.ca

 

[rkferri]

Personid in my Obits table is an autonumber.
Personid in my Guestbook table is a text.

How can I get passed that error without changing the database types. I need to keep the guestbook personid as a text field since I'm just updating that field with the obits personid when I do updates.

 

[r937]

I need to keep the guestbook personid as a text field since I'm just updating that field with the obits personid when I do updates.

actually, it would be better if you do change it, and your updates will still work


meanwhile you can try one of the following

   ON Guestbook.personid = '' & Obits.Personid

   ON ' ' & Guestbook.personid = Str(Obits.Personid)

r937.com | rudy.ca

 

[rkferri]

Thank you so much works great! Just for future reference how could I have two tables one field with my personid as autonumber and the second table's personid as an autonumber field. When I update the second table I insert my personid value from the first table into my personid in the second table. I guess just because the datatype is autonumber I can still do this??

Thanks again,
Kelly

 

[r937]

no, make the second one just a long integer, but not autonumber

r937.com | rudy.ca

 

[rkferri]

got it Thanks again!