Excel, VBA, SQL, DAO 3.6 help needed

[Jared Evans]

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

[Arthur Wood]

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
>
>

[Don]

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
>>
>>
>