[AccessD] sql help please

Joe,

» Thank you for all the time you put into this. «

It was fun, actually.  I did it so that I could SEE the relationships.
 But you're welcome!

» Are you saying that I spelled them differently in each table? Big
Dumb for me. I am not getting any prompts for the query. «

I didn't explain myself properly.  Some of the fields in your query
have the word Accident spelled correctly:  AccidentDATE, AccidentTime,
and AccidentDayofWeek.  It was just the key fields (I presume) in
those four tables that ALL had the name MyAccidnetRecordNumber.  The
key field names were not different in each of the tables; they were
all consistently misspelled.  That's what I meant.

One more thing about the joins:  when I'd finished creating the mock
tables to enable me to look at the query in design mode, I noticed
that there was no direct JOIN statement linking
10_tbl_PersonelMasterData (M) to 20_tblAIMaster (A), just those two
clauses at the end of your query.  So I thought that I'd remove all
the links and then re-link them "manually" in the Design window.  I
highlighted the connecting line between M and A and right-clicked on
it to get the context menu, I then clicked on Delete...but the
connecting line was still there.  I checked the SQL code and found
that the final AND (M.DRIV_NUM = A.DRIV_NUM) had been removed leaving
just the ON (M.DRIV_NUM = A.DRIV_NUM) clause.  I then deleted the
second connecting line and then all the rest, and re-linked them to
match the original design.

Here's the New and Improved FROM clause:

FROM (((((10_tbl_PersonelMasterData AS M
          INNER JOIN 10_stblEmpCompInfo AS C
          ON M.MyEmpNumb = C.MyEmpNumb)
        LEFT JOIN 50_ltblTerminal AS T
        ON C.TERMINAL = T.TRMNL_CODE)
      INNER JOIN 20_tblAIMaster AS A
      ON M.DRIV_NUM = A.DRIV_NUM)
    INNER JOIN 20_stblAccDayInfo AS D
    ON A.MyAccidnetRecordNumber = D.MyAccidnetRecordNumber)
  INNER JOIN 20_stblAIPaidExpences AS X
  ON A.MyAccidnetRecordNumber = X.MyAccidnetRecordNumber)
LEFT JOIN 20_stb_Loc_Wather_Road AS W
ON A.MyAccidnetRecordNumber = W.MyAccidnetRecordNumber;

I wonder if that reference to M.DRIV_NUM = A.DRIV_NUM might have been
the remains of a WHERE clause that just happened NOT to cause a syntax
error in the query.

Regards,

Steve Erbach
Scientific Marketing
Neenah, WI
www.swerbach.com
Security Page: www.swerbach.com/security



On 2/5/06, Joe Hecht <jmhecht at earthlink.net> wrote:
> Steve,
>
> Thank you for all the time you put into this. You know a sql
> statement is too big if the creator does not fully
> understand it all.
>
> For brevity I removed the sql statement but please see my
> comments in line.
>
> Joe Hecht
> jmhecht at earthlink.net
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> Steve Erbach
> Sent: Sunday, February 05, 2006 9:47 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] sql help please
>
>
>
> I noticed that the MyAccidnetRecordNumber field contained in
> 4 different tables is misspelled.  Do you get Access query
> prompts when you run this query asking you to enter a value
> for MyAccidnetRecordNumber?  If that's the case, then it
> looks to me like the misspelling is the problem.
>
> [Joe Hecht] Are you saying that I spelled them differently
> in each table? Big Dumb for me. I am not getting any prompts
> for the query.
>
>
> I noticed that the clause (M.DRIV_NUM = A.DRIV_NUM) is shown
> twice at the end.
>
> [Joe Hecht] How did I do that? I work almost exclusively in
> design mode. Are you able to see what is causing that? I
> sure did not type that.
>
> The table joins look straightforward enough.
>
> [Joe Hecht] Some of the join issues you wrote look wrong to
> me. (The joins, not your comments.)
>
>
> I see that there are no fields SELECTed from the
> 20_stblAIPaidExpences
> Table
>
>
>  [Joe Hecht] I had not gotten around to dropping them in.
>
> Hope this helps.
> [Joe Hecht]
> It does. Thank you. I am going to try your query or rebuild
> this beast up from multiple queries.
>
> Thanks again,
>
> Joe
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>


--