Query Results into Other Queries

[orreymalcolm]

Hey Everyone,

I have 3 Tables.
<Complete Contract List> - lists all the contracts available (2011 records)

<Member list> - List all member information (657 records)

<Complete List of Members and contracts> - Has a listing of every member and every contract that those members are committed to. (486,000 records)

Ok, here is my code so far, I'm trying to do the entire operation in JetSQL so as to save time and effort.

I have 3 Queries right now.

The first one asks me for a member number, when i put it in, it gives me all the contracts associated with that member number. Query name: SelectContractsFromMember

PARAMETERS [Enter Member #] Text ( 255 );
SELECT * FROM <Complete List of Members and Contracts>
WHERE (<Contract Number> IN (SELECT <Contract Number> FROM <Complete Contract List>)) AND (<Member Number> = [Enter Member #])
ORDER BY <Contract Number>;

My second one now checks this list against the full contract list and determines which are not already inside.
Query name: NotListedContracts

Note: This query seems to take a long time to run and eventually freezes the computer. Possibly an error in my code? Or maybe access limitations? I have no idea, I tried running it on a Core Duo 1.86 gHz and a Pentium 1.6gHz...both froze. Contract list has only 2011 contracts in it.

SELECT * FROM <Complete Contract List>
WHERE <Contract Number> NOT IN (SELECT <Contract Number> FROM SelectContractsFromMember);

And finally, the one that isn't working, but not the end of the problem lol...this one should, in theory, Append the not listed contracts with those of the listed ones for this member. Query name: AppendNewContracts

Note: Same freezing problem as second query when using the UNION ALL instead of INSERT INTO.

(Incomplete)
INSERT INTO SelectContractsFromMember (<Contract Number>)
SELECT * FROM NotListedContracts;

Any Help is appreciated....

Thanks!

 

[PHV]

One way is to create a CrossJoin query (cartesian product) combining all members with all contracts and then create a query based on the above one with a LeftJoin to the committed table.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[orreymalcolm]

Hey PHV,

I would do a cartesian, but my experience with it has gave me unwanted results, such as duplicating everything without member numbers, then adding some without, then putting them all together in a weird format. Everytime i use this type of join, i never get the desired results.

Is there any other way? or perhaps you could point me in the direction of some sample code that would work.

 

[PHV]

Please, post the structure of your 3 tables.

A cartesian product is like no join at all, ie you'll get 1321227 records.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[orreymalcolm]

Structure is as follows.

Members Table:
Member ID, Name, Address, etc

Contract Table:
Contract ID, Description, Created, Ending, etc

All Members/All Contracts Table:
Member ID, Contract ID, Expiry, Committed (A for Yes, B for No)

 

[traingamer]

Are the Member ID and Contract ID indexed in the All Members/All Contracts Table?

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[orreymalcolm]

No there is no index on them....Should I add an index?

 

[PHV]

As for the index:
Members Table:
Member ID (PK)
Contract Table:
Contract ID (PK)
All Members/All Contracts Table:
(Member ID, Contract ID) PK
Member ID (FK)
Contract ID (PK)

The crossjoin query (say, qryAll):
SELECT [Member ID], [Contract ID]
FROM [Members Table], [Contract Table]

The full listing you want:
SELECT L.[Member ID], L.[Contract ID], R.Expiry, R.Committed
FROM qryAll AS L LEFT JOIN [All Members/All Contracts Table] AS R
ON L.[Member ID] = R.[Member ID] AND L.[Contract ID] = R.[Contract ID]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[orreymalcolm]

The Cross join gives me errors during runtime. It's asking for parameters. What do I do

 

[PHV]

It's asking for parameters
You have to put the REAL field and table names ...

 

[orreymalcolm]

lol, Thanks, I'm not that oblivious

I have the REAL ones in, but it still asks.

 

[PHV]

Please, post your actual SQL code and the EXACT phrasing of the parameter(s) asking.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[orreymalcolm]

Member table is called gsd_hosf
MemberID = ff_memb_no

Contract table is called gsd_comr
ContractID = fr_agre_no

AllMember and Contracts table is called gsd_hocm
MemberID = fm_memb_no
ContractID = fm_agre_no
Committed = fm_option

 

[PHV]

You didn't reply to my question stamped 19 Jun 07 15:27

 

[orreymalcolm]

It was asking for parameters for everything in gsd_hocm

all the "fm_" ones.

 

[PHV]

I asked for the actual SQL code too ...