unrelated table in a query

[elbyc]

I am using a query with several tables, linked with one to many
relationships. There is one table in there that is not related to any
other tables. I contains a single record and I use a form on opening
the database to set parameters on it (start date and end date).

I use this table to set conditions on the query, then I use the query
for about 10 reports. I haven't had any problems with the query
(although the reports run slow). Is there any reason I should not set
it up this way?

I have to start a new project and would like to repeat the logic if it
is not flawed.

 

[techrat]

I use a form on opening

the database to set parameters on it (start date and end date).
......
I have to start a new project and would like to repeat the logic if it
is not flawed.

Can you post a copy of the SQL for the query here? If I understand
what you have done correctly, I am sure that there is a better way to
accomplish your objective.

Without seeing the SQL, I can tell you that if the values on the form
are strictly being used as criteria in the query that serves the 10
reports, you can simply reference the form field directly in the
criteria row of your query like so Forms![yourformname]!
[startdate_field_name] etc. This requires that your form stay open
however.

HTH

 

[techrat]

Ken's first post demonstrates a good point of caution when using this
method and that is that you need to be absolutely sure that you limit
the number of records in your criteria table to 1 or you may start
getting unpredictable results.

If you are using a bound form to set the data in the table, you need
to be sure to disallow the user from working with anything but the 1
record in the table.

 

[elbyc]

Ken's first post demonstrates a good point of caution when using this
method and that is that you need to be absolutely sure that you limit
the number of records in your criteria table to 1 or you may start
getting unpredictable results.

If you are using a bound form to set the data in the table, you need
to be sure to disallow the user from working with anything but the 1
record in the table.

Thank you for this information. Your responses are all quite helpful.
I was noodling over it through over the weekend so I could be ready to
start the new DB on Monday. Techrat, I'll paste the sql when I get
into work.

The form is indeed bound to one record - I've set it to allow no
additions.

I'm intrigued by the join using a between clause. I assume that will
mean the query cannot be viewed in design view, with which I am more
comfortable.

 

[elbyc]

A JOIN clause using a BETWEEN…..AND operation cannot be built in designview,
but the simplest way is to join the tables in the usual way in design view on
MainTable.DateColumn = DatesTable.DateFrom, then switch to SQL view andamend
it to:

ON (MainTable.DateColumn BETWEEN
DatesTable.DateFrom AND DatesTable.DateTo)

If the join is done in the WHERE clause then the join expression can be
entered in design view, which is presumably what you are currently doing.

Ken Sheridan
Stafford, England

Ken's first post demonstrates a good point of caution when using this
method and that is that you need to be absolutely sure that you limit

[quoted text clipped - 4 lines]

to be sure to disallow the user from working with anything but the 1
record in the table.

Thank you for this information. Your responses are all quite helpful.
I was noodling over it through over the weekend so I could be ready to
start the new DB on Monday. Techrat, I'll paste the sql when I get
into work.

The form is indeed bound to one record - I've set it to allow no
additions.

I'm intrigued by the join using a between clause. I assume that will
mean the query cannot be viewed in design view, with which I am more
comfortable.

Thanks - that's clear.
As I promised, here is the SQL. Like I said, it seems to work ok, but
I am curious how linking directly to the form would improve on it.

SELECT [monthly dept headcount reconciliation].*
FROM [monthly dept headcount reconciliation], [Date snapshot]
WHERE ((([monthly dept headcount reconciliation].Hired)<[date
snapshot].[hire date]));

 

[elbyc]

Apart from Techrat's  point about the DateSnapshot table not being permitted
more than one row, the only other possible argument I can envisage against
your current approach would be that as the value in the table is persistent,
if your start-up routines were bypassed and the hire date column not updated
via the form, the query would be referencing whatever value is in the
DateSnapshot table from the last time it was updated, which might or might
not be appropriate in the context of the application.

You can set the DateSnapshot form's AllowAdditions property to False to
prevent more than one row being inserted.  Its not bullet-proof of course as
a perverse user could insert a row other than via the form.

Ken Sheridan
Stafford, England





elbycwrote:

A JOIN clause using a BETWEEN…..AND operation cannot be built in design view,
but the simplest way is to join the tables in the usual way in design view on

[quoted text clipped - 32 lines]

- Show quoted text -

Thanks - that's clear.
As I promised, here is the SQL. Like I said, it seems to work ok, but
I am curious how linking directly to the form would improve on it.

SELECT [monthly dept headcount reconciliation].*
FROM [monthly dept headcount reconciliation], [Date snapshot]
WHERE ((([monthly dept headcount reconciliation].Hired)<[date
snapshot].[hire date]));

Thank you