Trying to match Data

[Terminus]

Hi there, im having problems matching data in my database it its driving me crazy. The problem goes a litlle something like this:

I have 2 table with data in them. On one table (tblx), holds data on items i wish to find the other (tbly) holds the selected items for each individual contact. What i want to do is get the database to tell mesay when i select 5 different properties from tblx to find the corresponding contacts in tbly. My problem is that it finds all the cotacts individually to each of the 5 items meaning i get duplicates and contacts who arent supposed to be there because they dont have say any of the other 4 selected properties highlighted in their contact properties.

Can anyone help, am really desperate!!!!!!

Many thanks,

Terminus

 

[dan1967]

I think the problem might be in the way the relationship between the tables is defined. You will want to set up your relationship like this:

Include ALL records from 'tblx' and only those records from 'tbly' where the joined fields are equal.

Dan.

 

[Terminus]

Thanks for the response, but i know thats the query i want to create but i cant find a way of getting the query only to pick up where only the joined fields are equal. That where im having the problems.

Can u help?

Terminus

 

[dan1967]

When you are in the design window, you see the 2 tables you are creating your query against. Right-click on the line that connects them and select Join Properties. You can set the join in there. Also, you can click on the Relationship button in your toolbar to set the joins for all of your tables. Once you set it in there, it is set for all Queries, Forms, Reports.

Dan.

 

[Terminus]

Cheers Dan 4 all the advice, but if i try to change the relationship joins and bring up thos people i get the error that there are ambiguous outer joins. Is there anything else u can recommend?

Terminus

 

[dan1967]

I would check your Join again and make sure you have it set like metioned above:

Include ALL records from 'tblx' and only those records from 'tbly' where the joined fields are equal.

You should have an arrowhead pointing into table 'tbly'.

Dan.

 

[Terminus]

Dan many thanks again but i do this and still get the same error:

The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, creae a seperate query that performs the first join and then include that query in your SQL statements.

The tables that have the info in them are link boxes with foreign keys in them.

Tblx has contactID and FormSelectID
Tbly has MailshotID and FormselectID

Basically the info in FormselectID from tblx has the info of the select items to each contact and the info in FromselectID in tbly holds the info of which i select to find contacts.

Ive taken ur advice in linking the tables but all it does is return the error above. Is it the way im setting out my tbls that is causing the problem?

Many thanks Ross

 

[dan1967]

I think I am at a loss. Without actually seeing the problem, I cannot come up with a solution, and I cannot recreate this problem in my environment.

Sorry and good luck to you.

Dan.

 

[Terminus]

I dont suppose i can send u a mini version of the db so u could look at it?

Terminus

 

[MichaelRed]

' ... sounds like ... " you have multiple joins between the sources and not all of them are the same. e.g. you have joined some as equjoins and others as others as outter joins. While it is possible to combine the two, you cannot do so between the same two sources in a (single) query. This is a common cause of the error you receieved.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Terminus]

Cheers Michael,

but how do i get the contacts (tblx) to match with the selected criteria in tbly? Can u help?

 

[MichaelRed]

GENERICALLY, the info is in the ubiquitous {F1}. A littel bit is in the error message, re create one query with the "Type A" personality (or joins) and another to join that with one with "type B" personality. Which 'personality' is a/b depends on some details not available in this thread. The ubiquitions topic(s) could include [inner | outter | left | right | join] and various combinations. The other 'issue' is whjat I refer to as layered queries, but that is not a 'topic'. It simpl refers to theprocess of having several queries which pull different subsets of data in a series of steps, some steps have 'parallel' queries, while others just take info from their immediate predessors. Diagrammed, it is 'tree like' with the start being the base and the ent point the 'tip'.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Terminus]

Hi, i think i get what ur saying, but what i dont get is how to make set of queries to make this. I fairly new to access, is there any possibility u could give me a real life example of how u would build such a set of queries to get what i want?

Many thanks,

Terminus

 

[MichaelRed]

As I noted previously, without knoweing (a lit) more than I need (or want to) anyu example I gave would be quite removed from your situation, and (probably) not very helpful.

Each query would probably have all the same type of joins. I would see at least two queries, since you appear to have both outter joins and inner joins between the two tables. so one query would (I assume) have the outter joins, while the other query would use the first query and the table with the inner joins.

Tek-Tips, with the limitation on 'hosting' ONLY text does not provide a medium of exchange for either the information (basic data) or the gui interface components, so sending and receiving sufficient 'stuff' via posts is not convenient.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Terminus]

Is it possible to email you a sample of the of my db sou could see what im trying achieve?

Terminus

 

[MichaelRed]

SMALL sample?

... but I STILL may not do it ... I would at least reply





MichaelRed
m.red@att.net

Searching for employment in all the wrong places