New Database Help

[Jo]

Hi there,

I'm Jo and it's the first time I've posted here. I'm in process of creating
a database at work and have come a little unstuck.....I'm a bit of a novice
and wondered if anyone could help. I work in a library and send out dual
language books to babies of dual or other nationality.
The db is to be used for logging a range of book titles and numbers ordered
and books sent out to individuals. I am trying to work out a way of keeping
track of how many of each title I have in stock so I can see when to order
more. Am I making sense so far?

One table has the following fields:
Book Title (e.g. Jungle Animals (Albanian) or Jungle Animals (Gujarati))
Date Ordered
No. Ordered

Another table (and related form) has details of books sent out to
individuals:
Name
Address
etc
Book Title
Date Sent Out

I am struggling to find a way - if there is one - of linking the info so to
keep track of stock numbers. I have tried various queries and exporting
data to Excel but got in a real muddle. There's probably a really simple
way, but I'm not that experienced that I can work it out!
I would appreciate any advice.
Thanks,
Jo

[Keith Wilby]

"Jo" <[e-mail] lan.plus.comwro te in message
news:46******** **************@ ptn-nntp-reader02.plus.n et...

Hi Jo,

Could you adapt or get some pointers from this?

http://office.microsoft.com/en-gb/te...1033&av=ZAC000

Regards,
Keith.
www.keithwilby.com

[Allen Browne]

Hi Jo, and welome. Hope you are able to derive good help here.

If this is your first database, the first step is to set up a data structure
(tables) that has all the right connections (one to many relations) between
them.

You might have more than one copy of a particular title. Presumably you want
to be able to track each one individually, e.g. this particular copy of
"Jungle Animals" was borrowed by Jo Smith on 2/28, and so she should have it
back to us by 3/28. If so, for each book title, you can have many instances
of the book: a one-to-many relation between Titles and Books. And you lend
out a book (not merely a title.) That means these tables:
Title table (one record for each title/book published)
TitleID AutoNumber primary key
Title Text name of the book
ISBN Text
YearPublished Number
...

Book table (one record for each copy you buy)
BookID AutoNumber primary key
TitleID Number relates to Title.TitleID
DateAcquired Date/Time when you got this book
...

Now, one person can borrow from you multiple times. Therefore you have a
one-to-many relation between borrower and loans. These tables:

Borrower table (one record for each person)
BorrowerID AutoNumber primary key
Surname Text
FirstName Text
Address Text
City Text
Zip Text
...

Loan table (one record each time someone borrows books):
LoanID AutoNumber primary key
BorrowerID Number relates to Borrower.Borrow erID
LoanDate Date/Time when taken out.

When someone borrows books, they often borrow more than one at at time. Any
loan therfore has multiple line-items: a one-to-many relation between Loan
and loan details. So the books borrowed in a loan go into a related table:

LoanDetail table (line items for a loan):
LoanDetailID AutoNumber primary key
LoanID Number which loan this is a line item of)
BookID Number the book that was borrowed
DueDate Date/Time when this book is due back (blank until
returned.)

That's the basic start. Creating the right tables and relationships is
absolutely crucial to getting a useful database.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jo" <[e-mail] lan.plus.comwro te in message
news:46******** **************@ ptn-nntp-reader02.plus.n et...

Hi there,

I'm Jo and it's the first time I've posted here. I'm in process of
creating a database at work and have come a little unstuck.....I'm a bit
of a novice and wondered if anyone could help. I work in a library and
send out dual language books to babies of dual or other nationality.
The db is to be used for logging a range of book titles and numbers
ordered and books sent out to individuals. I am trying to work out a way
of keeping track of how many of each title I have in stock so I can see
when to order more. Am I making sense so far?

One table has the following fields:
Book Title (e.g. Jungle Animals (Albanian) or Jungle Animals
(Gujarati))
Date Ordered
No. Ordered

Another table (and related form) has details of books sent out to
individuals:
Name
Address
etc
Book Title
Date Sent Out

I am struggling to find a way - if there is one - of linking the info so
to keep track of stock numbers. I have tried various queries and
exporting data to Excel but got in a real muddle. There's probably a
really simple way, but I'm not that experienced that I can work it out!
I would appreciate any advice.
Thanks,
Jo

[Larry Linson]

Jo,

Just an "endorsemen t" here. Allen is the Access MVPs' MVP on inventory-type
applications -- he's the one to whom we turn for samples and advice on this
subject.

Larry Linson
Microsoft Access MVP

[Jo]

"Allen Browne" <Al*********@Se eSig.Invalidwro te in message
news:46******** *************** @per-qv1-newsreader-01.iinet.net.au ...

Hi Jo, and welome. Hope you are able to derive good help here.

If this is your first database, the first step is to set up a data
structure (tables) that has all the right connections (one to many
relations) between them.

You might have more than one copy of a particular title. Presumably you
want to be able to track each one individually, e.g. this particular copy
of "Jungle Animals" was borrowed by Jo Smith on 2/28, and so she should
have it back to us by 3/28. If so, for each book title, you can have many
instances of the book: a one-to-many relation between Titles and Books.
And you lend out a book (not merely a title.) That means these tables:
Title table (one record for each title/book published)
TitleID AutoNumber primary key
Title Text name of the book
ISBN Text
YearPublished Number
...

Book table (one record for each copy you buy)
BookID AutoNumber primary key
TitleID Number relates to Title.TitleID
DateAcquired Date/Time when you got this book
...

Now, one person can borrow from you multiple times. Therefore you have a
one-to-many relation between borrower and loans. These tables:

Borrower table (one record for each person)
BorrowerID AutoNumber primary key
Surname Text
FirstName Text
Address Text
City Text
Zip Text
...

Loan table (one record each time someone borrows books):
LoanID AutoNumber primary key
BorrowerID Number relates to Borrower.Borrow erID
LoanDate Date/Time when taken out.

When someone borrows books, they often borrow more than one at at time.
Any loan therfore has multiple line-items: a one-to-many relation between
Loan and loan details. So the books borrowed in a loan go into a related
table:

LoanDetail table (line items for a loan):
LoanDetailID AutoNumber primary key
LoanID Number which loan this is a line item of)
BookID Number the book that was borrowed
DueDate Date/Time when this book is due back (blank
until returned.)

That's the basic start. Creating the right tables and relationships is
absolutely crucial to getting a useful database.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jo" <[e-mail] lan.plus.comwro te in message
news:46******** **************@ ptn-nntp-reader02.plus.n et...

>Hi there,

I'm Jo and it's the first time I've posted here. I'm in process of
creating a database at work and have come a little unstuck.....I'm a bit
of a novice and wondered if anyone could help. I work in a library and
send out dual language books to babies of dual or other nationality.
The db is to be used for logging a range of book titles and numbers
ordered and books sent out to individuals. I am trying to work out a way
of keeping track of how many of each title I have in stock so I can see
when to order more. Am I making sense so far?

One table has the following fields:
Book Title (e.g. Jungle Animals (Albanian) or Jungle Animals
(Gujarati))
Date Ordered
No. Ordered

Another table (and related form) has details of books sent out to
individuals:
Name
Address
etc
Book Title
Date Sent Out

I am struggling to find a way - if there is one - of linking the info so
to keep track of stock numbers. I have tried various queries and
exporting data to Excel but got in a real muddle. There's probably a
really simple way, but I'm not that experienced that I can work it out!
I would appreciate any advice.
Thanks,
Jo

Thanks Allen, but I think I may have misled you here and made it sound more
complicated... by mentioning that I work in a library. The books I am
sending out are for keeps - one book will go to one baby and that's it.

The only relationship I have set up at the moment is Book Title.

Kind Regards,
Jo

[Allen Browne]

If you are quite certain that there will *never* be more than one book per
person, then the tables you suggested make good sense.

Just don't use Name for a field. Nearly everything in Access has a Name
property, and so Access will get it confused with the Name of your form etc.
Use separate fields for Surname and FirstName.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jo" <[e-mail] lan.plus.comwro te in message
news:46******** **************@ ptn-nntp-reader02.plus.n et...

Thanks Allen, but I think I may have misled you here and made it sound
more complicated...b y mentioning that I work in a library. The books I am
sending out are for keeps - one book will go to one baby and that's it.
The only relationship I have set up at the moment is Book Title.

"Allen Browne" <Al*********@Se eSig.Invalidwro te in message
news:46******** *************** @per-qv1-newsreader-01.iinet.net.au ...

>Hi Jo, and welome. Hope you are able to derive good help here.

If this is your first database, the first step is to set up a data
structure (tables) that has all the right connections (one to many
relations) between them.

You might have more than one copy of a particular title. Presumably you
want to be able to track each one individually, e.g. this particular copy
of "Jungle Animals" was borrowed by Jo Smith on 2/28, and so she should
have it back to us by 3/28. If so, for each book title, you can have many
instances of the book: a one-to-many relation between Titles and Books.
And you lend out a book (not merely a title.) That means these tables:
Title table (one record for each title/book published)
TitleID AutoNumber primary key
Title Text name of the book
ISBN Text
YearPublished Number
...

Book table (one record for each copy you buy)
BookID AutoNumber primary key
TitleID Number relates to Title.TitleID
DateAcquired Date/Time when you got this book
...

Now, one person can borrow from you multiple times. Therefore you have a
one-to-many relation between borrower and loans. These tables:

Borrower table (one record for each person)
BorrowerID AutoNumber primary key
Surname Text
FirstName Text
Address Text
City Text
Zip Text
...

Loan table (one record each time someone borrows books):
LoanID AutoNumber primary key
BorrowerID Number relates to Borrower.Borrow erID
LoanDate Date/Time when taken out.

When someone borrows books, they often borrow more than one at at time.
Any loan therfore has multiple line-items: a one-to-many relation between
Loan and loan details. So the books borrowed in a loan go into a related
table:

LoanDetail table (line items for a loan):
LoanDetailID AutoNumber primary key
LoanID Number which loan this is a line item of)
BookID Number the book that was borrowed
DueDate Date/Time when this book is due back (blank
until returned.)

That's the basic start. Creating the right tables and relationships is
absolutely crucial to getting a useful database.

"Jo" <[e-mail] lan.plus.comwro te in message
news:46******* *************** @ptn-nntp-reader02.plus.n et...

>>Hi there,

I'm Jo and it's the first time I've posted here. I'm in process of
creating a database at work and have come a little unstuck.....I'm a bit
of a novice and wondered if anyone could help. I work in a library and
send out dual language books to babies of dual or other nationality.
The db is to be used for logging a range of book titles and numbers
ordered and books sent out to individuals. I am trying to work out a
way of keeping track of how many of each title I have in stock so I can
see when to order more. Am I making sense so far?

One table has the following fields:
Book Title (e.g. Jungle Animals (Albanian) or Jungle Animals
(Gujarati))
Date Ordered
No. Ordered

Another table (and related form) has details of books sent out to
individuals :
Name
Address
etc
Book Title
Date Sent Out

I am struggling to find a way - if there is one - of linking the info so
to keep track of stock numbers. I have tried various queries and
exporting data to Excel but got in a real muddle. There's probably a
really simple way, but I'm not that experienced that I can work it out!
I would appreciate any advice.

[onedaywhen]

On Mar 22, 8:05 pm, "Larry Linson" <boun...@localh ost.notwrote:

Just an "endorsemen t" here. Allen is the Access MVPs'MVPon inventory-type
applications -- he's the one to whom we turn for samples and advice on this
subject.

Larry Linson
Microsoft AccessMVP

Just to redress the balance slightly, I find Allen's design wanting. I
worked for about a year in a library, including stock taking and
invigilation, and a constant user of public and private libraries and
I genuinely think such a simplistic design to be unworkable.

Considerations of surrogates aside, to key a table of books on
autonumber and not ISBN would IMO quickly lead to data integrity
corruption. Autonumber (random? incrementing?) for BorrowerID is not
practical either because you would need to expose it (hint: my local
public library *cards* -- yes, as a borrower I have more than one card
-- have 14 character alphanumeric key and incorporate a bar code).

It doesn't matter how small the library is, this a data model IMO too
complex for a newsgroup post. Then there's populating the database...

My best advice would be to take an honest, open-minded look at this
article:

http://en.wikipedia.org/wiki/Not_Invented_Here

and for the OP to seriously consider purchasing some ready-made
software.

No disrepect to Allen for having a go, though; we all like a
challenge :)

Jamie.

--

[Jo]

"onedaywhen " <ja**********@x smail.comwrote in message
news:11******** **************@ b75g2000hsg.goo glegroups.com.. .

On Mar 22, 8:05 pm, "Larry Linson" <boun...@localh ost.notwrote:

>Just an "endorsemen t" here. Allen is the Access MVPs'MVPon
inventory-type
applications -- he's the one to whom we turn for samples and advice on
this
subject.

Larry Linson
Microsoft AccessMVP

Just to redress the balance slightly, I find Allen's design wanting. I
worked for about a year in a library, including stock taking and
invigilation, and a constant user of public and private libraries and
I genuinely think such a simplistic design to be unworkable.

Considerations of surrogates aside, to key a table of books on
autonumber and not ISBN would IMO quickly lead to data integrity
corruption. Autonumber (random? incrementing?) for BorrowerID is not
practical either because you would need to expose it (hint: my local
public library *cards* -- yes, as a borrower I have more than one card
-- have 14 character alphanumeric key and incorporate a bar code).

It doesn't matter how small the library is, this a data model IMO too
complex for a newsgroup post. Then there's populating the database...

My best advice would be to take an honest, open-minded look at this
article:

http://en.wikipedia.org/wiki/Not_Invented_Here

and for the OP to seriously consider purchasing some ready-made
software.

No disrepect to Allen for having a go, though; we all like a
challenge :)

Jamie.

--

It's a lot simpler than that. Forget the library bit. I'll explain a bit
further.....I work for a national project called Bookstart which encourages
parent and carers to share books with their babies from a very young age.
Each baby, when attending their 7-9 month health check receives a gift of a
bag containing books, other goodies and a questionnaire (which the parent
fills out). Questionnaires are sent back to me and I use the information
for evaluation purposes. One of the optional questions relates to
ethnicity. If the baby is of dual or other nationality then I have the
pleasure of sending them out another free book, this time in their own
language.
I need to keep track of the numbers of books of each language that I hold in
stock and how many I have given out so I know when to re-order. That is
basically it.
Is there away I can send out a dummy of my database so far for someone to
have a look at and advise me. I think I'm on the right track with the field
names etc. I have set up a query which lists the titles I have sent out,
and on what dates, but I need a way of counting up how many of each type
etc.

Jo

>

[Larry Linson]

"onedaywhen " <ja**********@x smail.comwrote

Just to redress the balance slightly, I find Allen's design wanting.

Thanks so much for sharing your knowledge with us.

[Jo]

I feel that everyone has given up on me without really understanding what I
was trying to get at (see my posting of 24/3 - that explains it better).
Everyone seems to be trying to make this more compplicated than it really
is! Oh, and is it the norm to top-post in this group, only I was reprimanded
numerous times in other groups for doing so?

Jo
"Jo" <[e-mail] lan.plus.comwro te in message
news:46******** **************@ ptn-nntp-reader02.plus.n et...

Hi there,

I'm Jo and it's the first time I've posted here. I'm in process of
creating a database at work and have come a little unstuck.....I'm a bit
of a novice and wondered if anyone could help. I work in a library and
send out dual language books to babies of dual or other nationality.
The db is to be used for logging a range of book titles and numbers
ordered and books sent out to individuals. I am trying to work out a way
of keeping track of how many of each title I have in stock so I can see
when to order more. Am I making sense so far?

One table has the following fields:
Book Title (e.g. Jungle Animals (Albanian) or Jungle Animals
(Gujarati))
Date Ordered
No. Ordered

Another table (and related form) has details of books sent out to
individuals:
Name
Address
etc
Book Title
Date Sent Out

I am struggling to find a way - if there is one - of linking the info so
to keep track of stock numbers. I have tried various queries and
exporting data to Excel but got in a real muddle. There's probably a
really simple way, but I'm not that experienced that I can work it out!
I would appreciate any advice.
Thanks,
Jo