Loading Single Record Table into Query

[kennedymr2]

I have a query with many tables, all linked.

I need to add a table, FROMTO, which only has one record, and does not really link to any table. IE. I need to reference it ie [lastname] >= [fromtto]![alphafrom] etc.
********************
When i include in the query, error message, ??ambigious outer joins.

Just need to have it there to reference!!!

Appreciate any help

 

[tlbroadbent]

I'm sure I don't understand the necessity of an unrelated table in a query. Perhaps you could explain a little more about what you are doing to help me understand. Posting at least a skeleton of your query will help.

In the meantime, you may be able to overcome the "ambiguous outer joins" error by restructuring the query.[ul][li]Make sure the relationships are consistently defined. Say I have a customer table, a sales table and a payments table. The CustID column is the primary key in the Customers table and a foreign key in the other tables.

The following JOIN may produce ambiguity because the foreign key relationship isn't properly defined by the JOIN attributes.

Customer c Inner Join Sales s On c.CustId=s.CustID
Left Join Payments On s.CustID=p.CustID

In the rewritten query, the ambiguity would be eliminated.

Customer c Inner Join Sales s On c.CustId=s.CustID
Left Join Payments On c.CustID=p.CustID

[li]Use of parentheses around JOINS can also help eliminate ambiguity. As you can see, the use of parentheses may also require additional restructuring of the query.

Customer c Inner Join
(Sales s Left Join Payments On s.CustID=p.CustID)
c.CustId=s.CustID Terry

;-) I never worry about the future. It comes soon enough. -Albert Einstein

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[mymou]

Don’t think you want to start using joins.

Try creating a function that can then be used via the expression builder etc.

Not sure what the standard way to store references in Access is (INI file, Global Variables, database properties etc) - but this works.

 

[kennedymr2]

I have created a large query, which is working well.

I have created a seperate table called fromto1.
This table contains all the from/to info i need to select
which data i need showing up when the query is run.

I need to be able to reference this table (fromto1) , in my query.

under field say... alphakey in critea, i would have
>=[fromto1]![alphafrom] etc...


The fromto1 table only has one record.
How do i get it into the query, without an error.

I note, if i do this , and the query has only one table, (no joins), i can do this.

Hope my question is a little clearer.

If i am unable to get the above to work,will look furthur into stewartsent's idea.

 

[mymou]

Set up ADO or DAO recordset and put the value into a function. Something like:

Function GetResults()

Dim oConn As New ADODB.Connection
Dim oRS As ADODB.Recordset
sFileName =
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _ sFileName & ";Persist Security Info=False"
Set oRS = oConn.Execute("tblTableName&quot
oRS.MoveFirst
GetResults = oRS("FieldName&quot

End Function

This Function will pull back a single value that can then be used as criteria in a query. ie criteria would be >GetResults()

 

[kennedymr2]

Thanks stewartsent,

I will implement the calling function idea.,
it will overcome the problem i have .
Appreciate your help