Post reply

Access Front end and SQL Backend table relationships

  • December 15, 2008 at 2:10 pm

    #77160

    Ok....I have a table that list Employees and then I have another table that holds Files that each Employee creates. table 1 = tbl_Employees and table 2 = tbl_Files.

    The primary key in Employees table relates to AcctID and SectID in the Files table. in the Employees table there are Secretarys and Attorneys.

    here are my tables:

    EmpID (Primary Key)

    LName

    FName

    Role (Secretary/Attorney)

    FileID (Primary Key)

    FileNo

    SectID (Foreign Key)

    AttyID (Foreign Key)

    FileName

    DateOpened

    Dept

    Status

    Desc

    So Emp ID is related to SectID and AcctID. I'm trying to create a query to run a report.

    In the query I want to pull the Emp first Name, last name, role, fileNo, FileName, etc. But when I put these fields in the query it only pulls either all the Secretarys or All the Attorneys instead of pulling both. I'm not sure how to create the query so that it will show the Secretarys and Attorneys under FName and LName and just destinguish the two under the Role column.

    Any help on this would be greatly appreciated.

    Thanks

  • December 16, 2008 at 11:13 am

    #913353

    You can union both results together

    select EmpID

    ,LName

    ,FName

    ,Role

    ,FileID

    ,FileNo

    ,SectID

    ,AttyID

    ,FileName

    ,DateOpened

    ,Dept

    ,Status

    ,Desc

    from employees

    inner join files

    on empid=sectid

    union all

    select EmpID

    ,LName

    ,FName

    ,Role

    ,FileID

    ,FileNo

    ,SectID

    ,AttyID

    ,FileName

    ,DateOpened

    ,Dept

    ,Status

    ,Desc

    from employees

    inner join files

    on empid=attyid

    order by empid

  • December 16, 2008 at 11:19 am

    #913357

    Thanks that worked but for some reason it's bringing in each record twice.

  • December 16, 2008 at 11:24 am

    #913360

    SELECT dbo_tbl_Files.FileID, dbo_tbl_Files.SectID, dbo_tbl_Files.AttyID, (RTrim(Attorney.LName) & ', ' & RTrim(Attorney.FName)) as Attorney, (RTrim(Secretary.LName) & ', ' & RTrim(Secretary.FName)) As Secretary, dbo_tbl_Files.FileNo, dbo_tbl_Files.FileName, dbo_tbl_Files.Dept, dbo_tbl_Files.DateOpened, dbo_tbl_Files.Description, dbo_tbl_Files.Status

    FROM dbo_tbl_Files

    INNER JOIN dbo_tbl_Employees As Attorney ON Attorney.EmpID = dbo_tbl_Files.AttyID

    INNER JOIN dbo_tbl_Employees As Secretary ON Secretary.EmpID = dbo_tbl_Files.SectID

    The above query actually does what I need it to do when I do it in Sql but when I try to do it in access I get the error: Syntax error (missing operator) in query expression Attorney.EmpID = dbo_tbl_Files.AttyID

    INNER JOIN dbo_tbl_Employees As Secretary ON Secretary.EmpID = dbo_tbl_Files.SectID

  • December 17, 2008 at 4:56 am

    #913707

    Almost the same as I would write the query, only I would make an alias for the dbo_tbl_Files table. But the query gave me the same error as you described. Why don't you use the Design view for constructing the query though? When I did that, I got code I wouldn't write myself, but I guess Access can't handle more than one INNER JOIN... This is the code I gotSELECT Files.FileID, Files.SectID, Files.AttyID, (RTrim(Attorney.LName) & ', ' & RTrim(Attorney.FName)) AS Attorney, (RTrim(Secretary.LName) & ', ' & RTrim(Secretary.FName)) AS Secretary, Files.FileNo, Files.FileName, Files.Dept, Files.DateOpened, Files.Description, Files.Status

    FROM (dbo_tbl_Files AS Files INNER JOIN dbo_tbl_Employees AS Secretary ON Files.SectID = Secretary.EmpID) INNER JOIN dbo_tbl_Employees AS Attorney ON Files.AttyID = Attorney.EmpID;

    Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2

  • December 17, 2008 at 6:40 am

    #913782

    yeah, I guess Access limits you what you can do. Actually I did start out using the query design but nothing would work that way. But the code you gave me worked like a charm. Thank you so very much for your help. Hope I can call on you again sometime.

    Gwen:)

  • December 17, 2008 at 6:51 am

    #913789

    You could try writing and saving the query in SQL Server as a stored procedure and linking to that in Access, that's worked for me before.

    Facts are stubborn things, but statistics are more pliable - Mark Twain
    Carolyn
    SQLServerSpecialists[/url]

  • December 18, 2008 at 6:48 am

    #914489

    Gwen,

    For next time:

    The Access Query Designer will handle your senario. You have to "Add" the tbl_Employees twice and relate one to Attorneys and one to Secretaries. When you add tbl_Employees the second time, Access gives the second one a different alias, automatically.

  • December 18, 2008 at 6:55 am

    #914496

    Thanks to everyone who helped me with this issue. I have it working and now I have another problem.....

    I am trying to write a query for a report that pulls only the files that has been "Closed". I have a field called "Status" and it writes "active" or "closed" to the table.

    First I have 2 forms that the user enters files. One form is used to enter "active" files and the other form is used to enter "closed" files. Now the user need to get reports for active files and closed files. I have created queries to get all the active files but for some reason when I try to use the same query to get closed files it doesn't work.

    here is the query I have:

    SELECT Files.FileNo, Files.DateClosed, (RTrim(Attorney.FName) & ' ' & RTrim(Attorney.LName)) AS Attorney, Files.FileName, Files.Dept, Files.Room, Files.BoxNo, Files.Description, Files.Status

    FROM (dbo_tbl_Files AS Files INNER JOIN dbo_tbl_Employees AS Secretary ON Files.SectID = Secretary.EmpID) INNER JOIN dbo_tbl_Employees AS Attorney ON Files.AttyID = Attorney.EmpID

    WHERE (((Files.Status)="Closed"));

    please help!

  • December 18, 2008 at 7:09 am

    #914511

    Standard debugging - have you checked your DATA to see if any records have a Status values of "Closed"? Typos or missing data are common. Tell us what you have tried in order to determine the problem; it will help us to help you.

    Here there be dragons...,

    Steph Brown

  • December 18, 2008 at 7:23 am

    #914522

    Have you tried to change the query from Files.Status = "Closed" to Files.Status <> "Active"? It's not the best for performance, but at least you might get the correct output.

    Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2

  • December 18, 2008 at 7:23 am

    #914523

    thank you so much for your replies. As soon as I sent this post I figured it out. Once again, thanks for all your help on this.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply