*RESOLVED* Rec set f/ Report from multiple tables

[mdsoren]

I am still searching for the answer to this question (have tried SQL forums elsewhere, but no one is responding 'yay' or 'nay')

What I need is - given a company ID, find all the records tied to that company across 4 tables. That much I can do.

The problem is I need to take a piece of that result (a contact name) and look up their address in same table the company's address came out of.

I have tried referencing the table twice, once by name and once by alias and use the alias for the contact info.

And that NEARLY works! Except it returns multiple records (it should return a single record with two distinct addresses within that record). What I actually get is the company info, complete with company address, repeated 6 times with 6 different contact names and their subsequent addresses!!!

It is returning the correct company based on the ID passed to the query, but then returns EVERY contact in the sample database, rather than the single contact which is tied to that company.

It looks messy, but here is the query string:

SELECT tblCorp.*, tblOrganization.*, tblStateReports.*, 
    tblAddressBook.Address1Mailing, 
    tblAddressBook.Address1Main, 
    tblAddressBook.Address2Mailing, 
    tblAddressBook.Address2Main, tblAddressBook.Attorney, 
    tblAddressBook.CityMailing, tblAddressBook.CityMain, 
    tblAddressBook.EmailAddress, tblAddressBook.FAXNumber, 
    tblAddressBook.FullName, tblAddressBook.ID, 
    tblAddressBook.PrimaryPhone, 
    tblAddressBook.RegisteringAgent, 
    tblAddressBook.SecondaryPhone, 
    tblAddressBook.StateMailing, tblAddressBook.StateMain, 
    tblAddressBook.ZIPMailing, tblAddressBook.ZIPMain, 
    RegAddress.FullName AS RegAgent, 
    RegAddRess.Address1Reg, RegAddress.Address2Reg, 
    RegAddress.CityReg, RegAddress.StateReg, 
    RegAddress.ZIPReg
FROM tblAddressBook, tblCorp, tblOrganization, tblStateReports, 
    tblAddressBook AS RegAddress
WHERE (tblAddressBook.ID = tblCorp.ID AND 
    tblCorp.ID = tblOrganization.ForeignKey AND 
    tblOrganization.ID = tblStateReports.ForeignKey AND 
    tblCorp.ID = ? AND tblOrganization.OrganizedAuthorized = 'O') 
    AND (tblCorp.ID = ? AND RegAgent = tblCorp.RegisteredAgent)

HELP! I need to deliver this report TOMORROW and I can't make the stinking thing work

[IROY55]

All I can tell you that your best bet is to creat a Query (if you're using MS Access) or View (if using SQL Server, Sybase, Oracle or other) and create your recordset beased on it:
strSQL = "Select * From MyView"

[mdsoren]

Okay (?)...

I am creating the rec set in code and I am admittedly weak in SQL; can you reference a recordset in a SQL statement?

Given what I have above, if I could take the rec set with the multiple records in it and turn around and use THAT to create a NEW rec set, I could filter out the bad records. Can you write a SQL statement where the "FROM" clause pulls records from a another rec set vs a table???

Does this spark any other ideas?

I am down to the wire!

[Sheppe]

Originally posted by mdsoren
I am still searching for the answer to this question (have tried SQL forums elsewhere, but no one is responding 'yay' or 'nay')

What I need is - given a company ID, find all the records tied to that company across 4 tables. That much I can do.

The problem is I need to take a piece of that result (a contact name) and look up their address in same table the company's address came out of.

I have tried referencing the table twice, once by name and once by alias and use the alias for the contact info.

And that NEARLY works! Except it returns multiple records (it should return a single record with two distinct addresses within that record). What I actually get is the company info, complete with company address, repeated 6 times with 6 different contact names and their subsequent addresses!!!

It is returning the correct company based on the ID passed to the query, but then returns EVERY contact in the sample database, rather than the single contact which is tied to that company.

It looks messy, but here is the query string:

SELECT tblCorp.*, tblOrganization.*, tblStateReports.*, 
    tblAddressBook.Address1Mailing, 
    tblAddressBook.Address1Main, 
    tblAddressBook.Address2Mailing, 
    tblAddressBook.Address2Main, tblAddressBook.Attorney, 
    tblAddressBook.CityMailing, tblAddressBook.CityMain, 
    tblAddressBook.EmailAddress, tblAddressBook.FAXNumber, 
    tblAddressBook.FullName, tblAddressBook.ID, 
    tblAddressBook.PrimaryPhone, 
    tblAddressBook.RegisteringAgent, 
    tblAddressBook.SecondaryPhone, 
    tblAddressBook.StateMailing, tblAddressBook.StateMain, 
    tblAddressBook.ZIPMailing, tblAddressBook.ZIPMain, 
    RegAddress.FullName AS RegAgent, 
    RegAddRess.Address1Reg, RegAddress.Address2Reg, 
    RegAddress.CityReg, RegAddress.StateReg, 
    RegAddress.ZIPReg
FROM tblAddressBook, tblCorp, tblOrganization, tblStateReports, 
    tblAddressBook AS RegAddress
WHERE (tblAddressBook.ID = tblCorp.ID AND 
    tblCorp.ID = tblOrganization.ForeignKey AND 
    tblOrganization.ID = tblStateReports.ForeignKey AND 
    tblCorp.ID = ? AND tblOrganization.OrganizedAuthorized = 'O') 
    AND (tblCorp.ID = ? AND RegAgent = tblCorp.RegisteredAgent)

HELP! I need to deliver this report TOMORROW and I can't make the stinking thing work

Perhaps a subquery? Something like this:

Declare SelectName varchar(500)
Set SelectName = (Select tblCorp.RegisteredAgent From tblCorp.RegisteredAgent Where tblCorp.ID = ?)

SELECT tblCorp.*, tblOrganization.*, tblStateReports.*,
...
FullName AS (Select Fullname From tblAddressBook Where tblAddressBook.ID = ? AND tblAddressBook.Fullname = SelectName)

Address1Reg AS (Select Address1Main From tblAddressBook Where tblAddressBook.ID = ? AND tblAddressBook.Fullname = SelectName)
...
FROM tblAddressBook, tblCorp, tblOrganization, tblStateReports,
...

Try that out. It's not optimal for speed, but it should get you what you need.

[Sheppe]

Originally posted by mdsoren
Okay (?)...

I am creating the rec set in code and I am admittedly weak in SQL; can you reference a recordset in a SQL statement?

Given what I have above, if I could take the rec set with the multiple records in it and turn around and use THAT to create a NEW rec set, I could filter out the bad records. Can you write a SQL statement where the "FROM" clause pulls records from a another rec set vs a table???

Does this spark any other ideas?

I am down to the wire!

You won't be able to do it in code alone - you have to use a Query (in Access), or a Stored Procedure (in SQL). What DB are you using?

Also, are you using a reporting tool (like Crystal Reports), or are you just displaying a recordset?

[mdsoren]

I am using Access2000 as the database, but the app will be run on machines without Access, so it has to stand alone (using the access runtime only).

Currently, I am using the DataReport tool in VB (although in the next release I plan on switching to Crystal, but for now I have to go with this due to time - I have to learn Crystal first! and I am over a month beyond the orig deadline for delivery, so I can't afford any major changes.)

I did get another idea, but again I am not sure how to go about the execution of it... what if I create the first rec set and put those results in a NEW TABLE? then query the new table to pull out the correct record? How easy is it to create AND destroy tables as I don't need it after the report has been printed and closed?

I have to go off line for a couple hrs, but will be back later (3+ hrs from now).

APPRECIATE any and ALL help on this!!!

[Sheppe]

I can't remember if the VB reporting tool offers grouping, but if it does then put in a group, on the report, based on the Fullname.

[mdsoren]

I got it fixed!

Now, re-reading what Sheppe said yesterday I believe I got the same advice from another forum.

Here's what I changed:

SELECT tblCorp.*, tblOrganization.*, (SELECT Address1Reg FROM tblAddressbook AS RegAddrBk WHERE RegAddrBk.FullName = tblOrganization.RegisteredAgent) AS RegAddr1, (SELECT Address2Reg FROM tblAddressbook AS RegAddrBk WHERE RegAddrBk.FullName = tblOrganization.RegisteredAgent) AS RegAddr2, (SELECT CityReg FROM tblAddressbook AS RegAddrBk WHERE RegAddrBk.FullName = tblOrganization.RegisteredAgent) AS RegCity, (SELECT StateReg FROM tblAddressbook AS RegAddrBk WHERE RegAddrBk.FullName = tblOrganization.RegisteredAgent) AS RegState, (SELECT ZIPReg FROM tblAddressbook AS RegAddrBk WHERE RegAddrBk.FullName = tblOrganization.RegisteredAgent) AS RegZIP, tblStateReports.*, tblAddressBook.Address1Mailing, tblAddressBook.Address1Main, tblAddressBook.Address2Mailing, tblAddressBook.Address2Main, tblAddressBook.Attorney, tblAddressBook.CityMailing, tblAddressBook.CityMain, tblAddressBook.EmailAddress, tblAddressBook.FAXNumber, tblAddressBook.FullName, tblAddressBook.ID, tblAddressBook.PrimaryPhone, tblAddressBook.RegisteringAgent, tblAddressBook.SecondaryPhone, tblAddressBook.StateMailing, tblAddressBook.StateMain, tblAddressBook.ZIPMailing, tblAddressBook.ZIPMain FROM tblAddressBook, tblCorp, tblOrganization, tblStateReports WHERE (tblAddressBook.ID = tblCorp.ID AND tblCorp.ID = tblOrganization.ForeignKey AND tblOrganization.ID = tblStateReports.ForeignKey AND tblCorp.ID = ? AND tblOrganization.OrganizedAuthorized = 'O')

Hopefully, this may help out others who may search the forums for ideas!

Thanx ALL!
MDS