unrelated table in a query
in [General]
|
Prev: testmail
Next: Calculating the Time In A Time Zone
From: elbyc on 12 Dec 2009 23:14 On Dec 12, 9:36 am, techrat <jm...(a)hotmail.com> wrote: > 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.
From: KenSheridan via AccessMonster.com on 13 Dec 2009 07:21 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 MainTable.DateColumn = DatesTable.DateFrom, then switch to SQL view and amend 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 elbyc wrote: >> 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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access/200912/1
From: elbyc on 14 Dec 2009 19:24 On Dec 13, 4:21 am, "KenSheridan via AccessMonster.com" <u51882(a)uwe> wrote: > 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 > MainTable.DateColumn = DatesTable.DateFrom, then switch to SQL view and amend > 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 > > > > > > elbyc wrote: > >> 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. > > -- > Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access/200912/1- Hide quoted text - > > - 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]));
From: KenSheridan via AccessMonster.com on 15 Dec 2009 05:09 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 elbyc wrote: >On Dec 13, 4:21 am, "KenSheridan via AccessMonster.com" <u51882(a)uwe> >wrote: >> 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])); -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access/200912/1
From: elbyc on 23 Dec 2009 12:48
On Dec 15, 2:09 am, "KenSheridan via AccessMonster.com" <u51882(a)uwe> wrote: > 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: > >On Dec 13, 4:21 am, "KenSheridan via AccessMonster.com" <u51882(a)uwe> > >wrote: > >> 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])); > > -- > Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access/200912/1- Hide quoted text - > > - Show quoted text - Thank you |