unrelated table in a query
in [General]
|
Prev: testmail
Next: Calculating the Time In A Time Zone
From: elbyc on 12 Dec 2009 10:47 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.
From: techrat on 12 Dec 2009 11:28 > 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
From: KenSheridan via AccessMonster.com on 12 Dec 2009 11:50 No reason whatsoever as far as I can see. When you include a table in a query without explicitly joining it to another table the query returns what is known as the Cartesian product of the tables (mathematically a Cartesian coordinate is each of a set of coordinates describing the position of a point in relation to a set of intersecting straight axes). In terms of tables this means that each row in one table is joined to each row in another. As you have only one row in the table in question this one row will be joined to each row returned by the join of whatever other tables you have in a query, or to each row in another table if there is only one other table involved. Consequently the values in the columns in your singe row table are available to each row returned by the other table(s) and can consequently be used to restrict the results of the query in the way you are doing. There are other ways the Cartesian product of tables can be usefully employed. A common one is to return multiple instances of each row returned by a query. You might want to return multiple copies of each address fir instance in a labels report. This is done by including a table with a single column, Counter say, which has numbers form 1 to 100 for instance (the only significance of the top number is that it determines the maximum number of instances of a row which can be returned). This table is included in the query along with the addresses table and the query is restricted on the Counter column, e.g. SELECT Addresses.* FROM Addresses, CounterTable WHERE Counter <=20; to return 20 of each address. Ken Sheridan Stafford, England elbyc wrote: >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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access/200912/1
From: KenSheridan via AccessMonster.com on 12 Dec 2009 12:12 PS: One other thing I meant to mention is that you are in fact joining the tables, but in your case the join criterion is included in the WHERE clause rather than in the usual JOIN clause. Prior to the SQL 92 standard (if memory serves me) this was how tables were joined. The introduction of the JOIN clause was really to allow for outer joins, which cannot be done in the WHERE clause. But you could equally well join the tables in the JOIN clause in the usual way: SELECT * FROM MainTable INNER JOIN DatesTable ON (MainTable.DateColumn BETWEEN DatesTable.DateFrom AND DatesTable.DateTo); On thing to note here is that if a BETWEEN….AND operation is included in a JOIN clause like this the join expression must be enclosed in parentheses. The parentheses are not essential if the join is done in the WHERE clause, however. Ken Sheridan Stafford, England -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access/200912/1
From: techrat on 12 Dec 2009 12:36
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. |