2 unrelated tables

[Mike Langensiepen]

I have a database which has a MyCompanyInformation table storing details
like Company Name, Tax Percentage etc.

All of my client data is stored in unrelated linked tables but I want to
retrieve some fields from the MyCompanyInformation table for use in reports
and forms (e.g. the Tax Percentage in calculations or the Company Name and
Address in Reports.

What is the best way of doing this? A dlookup seems to be the obvious one as
I can't seem to generate a query unless the tables are linked (which they
aren't).

Cheers

Mike

 

[John Vinson]

I have a database which has a MyCompanyInformation table storing details
like Company Name, Tax Percentage etc.

All of my client data is stored in unrelated linked tables but I want to
retrieve some fields from the MyCompanyInformation table for use in reports
and forms (e.g. the Tax Percentage in calculations or the Company Name and
Address in Reports.

What is the best way of doing this? A dlookup seems to be the obvious one as
I can't seem to generate a query unless the tables are linked (which they
aren't).

Cheers

Mike

Is MyCompanyInformation just a single-record table? If so, just use
DLookUp("[fieldname]", "[MyCompanyInformation]"). You don't need a
link. For that matter, you can use a "Cartesian Join" - just include
the table with NO join line. This will only work for reports, though;
the cartesian join won't be updateable.

If it has many records... how do you determine WHICH record you want
to see?

John W. Vinson[MVP]

 

[Mike Langensiepen]

Yes, it is a single record table and will never have more than the one
record.

Whatever technique I use has to work for forms and reports (or 2 seperate
techniques). I have seen criticisms of the dlookup method but I can't see
why as it seems to be the appropriate method.

Thanks

Mike

I have a database which has a MyCompanyInformation table storing details
like Company Name, Tax Percentage etc.

All of my client data is stored in unrelated linked tables but I want to
retrieve some fields from the MyCompanyInformation table for use in
reports
and forms (e.g. the Tax Percentage in calculations or the Company Name and
Address in Reports.

What is the best way of doing this? A dlookup seems to be the obvious one
as
I can't seem to generate a query unless the tables are linked (which they
aren't).

Cheers

Mike

Is MyCompanyInformation just a single-record table? If so, just use
DLookUp("[fieldname]", "[MyCompanyInformation]"). You don't need a
link. For that matter, you can use a "Cartesian Join" - just include
the table with NO join line. This will only work for reports, though;
the cartesian join won't be updateable.

If it has many records... how do you determine WHICH record you want
to see?

John W. Vinson[MVP]

 

[John Vinson]

Whatever technique I use has to work for forms and reports (or 2 seperate
techniques). I have seen criticisms of the dlookup method but I can't see
why as it seems to be the appropriate method.

Well, it can be quite slow and inefficient; but with a single-record
table it should be OK. Have you tried it and it didn't work?

John W. Vinson[MVP]

 

[Mike Lang]

Nope but I use dlookup to identify state and postcode from a suburb and
notice that it takes a second or so to do that.

Just wondering if there was any other reason not to use dlookup and if there
was a better option