Excel, VBA, SQL, DAO 3.6 help needed


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Excel, VBA, SQL, DAO 3.6 help needed

  1. #1
    Jared Evans Guest

    Excel, VBA, SQL, DAO 3.6 help needed

    Hey there.

    I'm new to this Newsgroup but i could do with some help with the above
    tools.

    Basically i'm controlling an Access database from Excel. The database has 3
    tables.

    Table 1: "Staff" contains the fields "ID", "S_Name" and "F_Name"
    Table 2: "Books" contains the fields "ISBN", "Title", "Author", and
    "Subject"
    Table 3: "BookLoans" contains the fields "ISBN", "Issue Date", "Return
    Date", and "Staff ID Number"


    The relationship's are:

    "ISBN" in table "Books" is linked to "ISBN" in table "BookLoans"
    "ID" in table "Staff" is linked to "Staff ID Number" in table "BookLoans"

    Now using Excel i can easily loan a book to a staff member, add a book,
    delete or edit a book, add/delete/edit a staff member.
    Where i have problems isreturning a book.

    how i have the form in Excel set out is: 2 Listboxes. 1 displays all the
    fields from the table "Staff". now when i click on a member of staff from
    that list box i want the other list box to display which books that member
    has. For now, getting it to display the "ISBN" is all i wish.

    I know i need inner joins, but i'm not entirely sure how to write them. If
    anyone could help please do. I'd be in your debt.

    Jaz



  2. #2
    Arthur Wood Guest

    Re: Excel, VBA, SQL, DAO 3.6 help needed


    what you need is SQL like this:

    Select * from BookLoans where [Staff Id Number] = <SelectedStaffID>

    where <SelectedStaffID> it eh ID if the Staff memberselecte from the first
    combo box.

    YOu need to show sme of the code that you have wriiten, becuase otherwise
    the entire converstion is CONCEPTUAL, and the above SQL shows the Concept,
    but NIT the SPECIFICS of the actual code that would be need to carry out
    what you need.

    Arthur Wood



    Jared Evans" <Jaz@s-evans.freeserve.co.uk> wrote:
    >Hey there.
    >
    >I'm new to this Newsgroup but i could do with some help with the above
    >tools.
    >
    >Basically i'm controlling an Access database from Excel. The database has

    3
    >tables.
    >
    >Table 1: "Staff" contains the fields "ID", "S_Name" and "F_Name"
    >Table 2: "Books" contains the fields "ISBN", "Title", "Author", and
    >"Subject"
    >Table 3: "BookLoans" contains the fields "ISBN", "Issue Date", "Return
    >Date", and "Staff ID Number"
    >
    >
    >The relationship's are:
    >
    >"ISBN" in table "Books" is linked to "ISBN" in table "BookLoans"
    >"ID" in table "Staff" is linked to "Staff ID Number" in table "BookLoans"
    >
    >Now using Excel i can easily loan a book to a staff member, add a book,
    >delete or edit a book, add/delete/edit a staff member.
    >Where i have problems isreturning a book.
    >
    >how i have the form in Excel set out is: 2 Listboxes. 1 displays all the
    >fields from the table "Staff". now when i click on a member of staff from
    >that list box i want the other list box to display which books that member
    >has. For now, getting it to display the "ISBN" is all i wish.
    >
    >I know i need inner joins, but i'm not entirely sure how to write them.

    If
    >anyone could help please do. I'd be in your debt.
    >
    >Jaz
    >
    >



  3. #3
    Don Guest

    Re: Excel, VBA, SQL, DAO 3.6 help needed


    I'd suggest when you check in a book in you use a sql UPDATE statment and
    set "Return Date" to equal the system date where [Staff Id Number] = <SelectedStaffID>.
    All your queries to select a book you would add a where clause to be sure
    "Return Date" is not null if "Issue Date" is not null. Hope that helps.

    Cheers,
    Don


    "Arthur Wood" <Wooda@nospam.com> wrote:
    >
    >what you need is SQL like this:
    >
    >Select * from BookLoans where [Staff Id Number] = <SelectedStaffID>
    >
    >where <SelectedStaffID> it eh ID if the Staff memberselecte from the first
    >combo box.
    >
    >YOu need to show sme of the code that you have wriiten, becuase otherwise
    >the entire converstion is CONCEPTUAL, and the above SQL shows the Concept,
    >but NIT the SPECIFICS of the actual code that would be need to carry out
    >what you need.
    >
    >Arthur Wood
    >
    >
    >
    >Jared Evans" <Jaz@s-evans.freeserve.co.uk> wrote:
    >>Hey there.
    >>
    >>I'm new to this Newsgroup but i could do with some help with the above
    >>tools.
    >>
    >>Basically i'm controlling an Access database from Excel. The database

    has
    >3
    >>tables.
    >>
    >>Table 1: "Staff" contains the fields "ID", "S_Name" and "F_Name"
    >>Table 2: "Books" contains the fields "ISBN", "Title", "Author", and
    >>"Subject"
    >>Table 3: "BookLoans" contains the fields "ISBN", "Issue Date", "Return
    >>Date", and "Staff ID Number"
    >>
    >>
    >>The relationship's are:
    >>
    >>"ISBN" in table "Books" is linked to "ISBN" in table "BookLoans"
    >>"ID" in table "Staff" is linked to "Staff ID Number" in table "BookLoans"
    >>
    >>Now using Excel i can easily loan a book to a staff member, add a book,
    >>delete or edit a book, add/delete/edit a staff member.
    >>Where i have problems isreturning a book.
    >>
    >>how i have the form in Excel set out is: 2 Listboxes. 1 displays all the
    >>fields from the table "Staff". now when i click on a member of staff from
    >>that list box i want the other list box to display which books that member
    >>has. For now, getting it to display the "ISBN" is all i wish.
    >>
    >>I know i need inner joins, but i'm not entirely sure how to write them.

    > If
    >>anyone could help please do. I'd be in your debt.
    >>
    >>Jaz
    >>
    >>

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center