By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,663 Members | 2,157 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,663 IT Pros & Developers. It's quick & easy.

New Database Help

P: n/a
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
Mar 22 '07 #1
Share this Question
Share on Google+
47 Replies


P: n/a
"Jo" <jo@NOSPAM.stallan.plus.comwrote in message
news:46**********************@ptn-nntp-reader02.plus.net...

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
Mar 22 '07 #2

P: n/a
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.BorrowerID
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" <jo@NOSPAM.stallan.plus.comwrote in message
news:46**********************@ptn-nntp-reader02.plus.net...
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
Mar 22 '07 #3

P: n/a
Jo,

Just an "endorsement" 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
Mar 22 '07 #4

P: n/a
Jo

"Allen Browne" <Al*********@SeeSig.Invalidwrote 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.BorrowerID
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" <jo@NOSPAM.stallan.plus.comwrote in message
news:46**********************@ptn-nntp-reader02.plus.net...
>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
Mar 23 '07 #5

P: n/a
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" <jo@NOSPAM.stallan.plus.comwrote in message
news:46**********************@ptn-nntp-reader02.plus.net...
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.

"Allen Browne" <Al*********@SeeSig.Invalidwrote 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.BorrowerID
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" <jo@NOSPAM.stallan.plus.comwrote in message
news:46**********************@ptn-nntp-reader02.plus.net...
>>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.
Mar 23 '07 #6

P: n/a
On Mar 22, 8:05 pm, "Larry Linson" <boun...@localhost.notwrote:
Just an "endorsement" 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.

--
Mar 23 '07 #7

P: n/a
Jo

"onedaywhen" <ja**********@xsmail.comwrote in message
news:11**********************@b75g2000hsg.googlegr oups.com...
On Mar 22, 8:05 pm, "Larry Linson" <boun...@localhost.notwrote:
>Just an "endorsement" 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
>

Mar 24 '07 #8

P: n/a
"onedaywhen" <ja**********@xsmail.comwrote
Just to redress the balance slightly, I find Allen's design wanting.
Thanks so much for sharing your knowledge with us.

Mar 25 '07 #9

P: n/a
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" <jo@NOSPAM.stallan.plus.comwrote in message
news:46**********************@ptn-nntp-reader02.plus.net...
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

Mar 26 '07 #10

P: n/a
In article <46083d1d$0$8748$ed2619ec@ptn-nntp-
reader02.plus.net>, jo@NOSPAM.stallan.plus.com says...
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" <jo@NOSPAM.stallan.plus.comwrote in message
news:46**********************@ptn-nntp-reader02.plus.net...
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


You work in a library and need a database model. So, why not
look here http://www.databaseanswers.org/data_models/index.htm
and pick 1 of the 6 library models.

Mar 26 '07 #11

P: n/a
"Mike Gramelspacher" <gr******@psci.netwrote
You work in a library and need a database model.
So, why not look here
http://www.databaseanswers.org/data_models/index.htm
and pick 1 of the 6 library models.
Actually, someone simply _assumed_ that she worked in a library.

She's corrected that error: she works in an organization that _gives_ (not
lends) books, and may send the book in a different language as a followup.
She simply needs to keep track of whether a different-language book should
be sent and of how many books she has, so she can order more when the supply
runs low.

Larry Linson
Microsoft Access MVP
Mar 27 '07 #12

P: n/a
assuming that this is your data
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
and assuming
a) no. ordered is number of books you've received from the supplier
b) you send out one copy of a book title to one individual

you can create a query (qryGivenOut)
that counts the number of books sent out to individuals
SELECT bookTitle, COUNT(bookTitle) as numberGivenOut
FROM tblIndividual GROUP BY bookTitle

and create a 2nd query (qryInStock) to give you total books in stock
SELECT bookTitle, SUM(noOrdered) as numberInStock
FROM tblBook GROUP BY bookTitle

and create a 3rd query to give you the number of remaining books
SELECT qryInStock.bookTitle,
numberInStock,
numberGivenOut,
numberInStock - numberGivenOut as numberRemaining
FROM qryInStock left join
qryGivenOut on qryInStock.bookTitle =
qryGivenOut.bookTitle
On Mar 26, 3:37 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
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" <j...@NOSPAM.stallan.plus.comwrote in message

news:46**********************@ptn-nntp-reader02.plus.net...
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- Hide quoted text -

- Show quoted text -

Mar 27 '07 #13

P: n/a
Without details of requirements (and that, really, would not be appropriate
for newsgroup), from what you've said, I think these tables would be a
start:

Books Description -- BookIdentifier, Book Name, Author, Language, other
information of interest relating to the book itself. (Note: it appears you
intend to include language along with title... I recommend against that
because sooner or later, you will need to Query against that, independently,
and you will find it much easier if the Language is in a separate Field.

Books Transactions -- TransactionIdentifier, DateOfTransaction,
NumberOfBooks (positive number = addition to stock, negative number =
withdrawal from stock), BookIdentifier as foreign key relating this to
BooksDescription Table, Donee, other information of interest relating to the
transaction.

Donees -- DoneeID, Donee Name, Language/Ethnicity, other information of
interest relating to the Donee.

I don't doubt that you'll think of other requirements / functions you need
to track, and those will likely require additional information.

NOTE: Current inventory can be calculated by summing the NumberOfBooks Field
in the Books Transaction Table. In addition to removals and withdrawals,
you will likely need to have an "adjustment" or "physical inventory count"
transaction. That's the kind of thing that Allen will know better than I to
advise.

Larry Linson
Microsoft Access MVP

"Jo" <jo@NOSPAM.stallan.plus.comwrote in message
news:46**********************@ptn-nntp-reader02.plus.net...
>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" <jo@NOSPAM.stallan.plus.comwrote in message
news:46**********************@ptn-nntp-reader02.plus.net...
>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


Mar 27 '07 #14

P: n/a
"Jo" <jo@NOSPAM.stallan.plus.comwrote
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?
There is no "standard" requiring a particular format of posting /
responding. We have had some "heated discussions" about it here in
comp.databases.ms-access, in the past. There was no consensus on the
subject, and I guess most who had strong feelings one way or the other just
gave up on convincing those of us who did not.

Larry Linson
Microsoft Access MVP

Mar 27 '07 #15

P: n/a
Jo

"Larry Linson" <bo*****@localhost.notwrote in message
news:fB_Nh.4531$8l2.265@trnddc01...
"Mike Gramelspacher" <gr******@psci.netwrote
You work in a library and need a database model.
So, why not look here
http://www.databaseanswers.org/data_models/index.htm
and pick 1 of the 6 library models.

Actually, someone simply _assumed_ that she worked in a library.

She's corrected that error: she works in an organization that _gives_
(not lends) books, and may send the book in a different language as a
followup. She simply needs to keep track of whether a different-language
book should be sent and of how many books she has, so she can order more
when the supply runs low.

Larry Linson
Microsoft Access MVP
Got it in one Larry...thanks!!
Jo
Mar 27 '07 #16

P: n/a
Jo
Hi there,

I have only just had the chance to give your advice a go as it looks exactly
like what I ought to be doing to achieve the results I am
after.....however...
I am having trouble with the syntax for the queries you have listed below.
I've tried all sorts, but each time I'm getting errors returned regarding
operators and operands etc which I really don't understand. Could you
possibly give me an example of, perhaps, the correct syntax for the first
query you mention?
Your help is much appreciated,
best wishes,
Jo
<le*********@natpro.comwrote in message
news:11*********************@p77g2000hsh.googlegro ups.com...
assuming that this is your data
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
and assuming
a) no. ordered is number of books you've received from the supplier
b) you send out one copy of a book title to one individual

you can create a query (qryGivenOut)
that counts the number of books sent out to individuals
SELECT bookTitle, COUNT(bookTitle) as numberGivenOut
FROM tblIndividual GROUP BY bookTitle

and create a 2nd query (qryInStock) to give you total books in stock
SELECT bookTitle, SUM(noOrdered) as numberInStock
FROM tblBook GROUP BY bookTitle

and create a 3rd query to give you the number of remaining books
SELECT qryInStock.bookTitle,
numberInStock,
numberGivenOut,
numberInStock - numberGivenOut as numberRemaining
FROM qryInStock left join
qryGivenOut on qryInStock.bookTitle =
qryGivenOut.bookTitle
On Mar 26, 3:37 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
>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" <j...@NOSPAM.stallan.plus.comwrote in message

news:46**********************@ptn-nntp-reader02.plus.net...
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- Hide quoted text -

- Show quoted text -


Apr 16 '07 #17

P: n/a
On Mar 26, 7:52 pm, lesperan...@natpro.com wrote:
assuming that this is your data
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

and assuming
a) no. ordered is number of books you've received from the supplier
b) you send out one copy of a book title to one individual

you can create a query (qryGivenOut)
that counts the number of books sent out to individuals
SELECT bookTitle, COUNT(bookTitle) as numberGivenOut
FROM tblIndividual GROUP BY bookTitle

and create a 2nd query (qryInStock) to give you total books in stock
SELECT bookTitle, SUM(noOrdered) as numberInStock
FROM tblBook GROUP BY bookTitle

and create a 3rd query to give you the number of remaining books
SELECT qryInStock.bookTitle,
numberInStock,
numberGivenOut,
numberInStock - numberGivenOut as numberRemaining
FROM qryInStock left join
qryGivenOut on qryInStock.bookTitle =
qryGivenOut.bookTitle

On Mar 26, 3:37 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
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" <j...@NOSPAM.stallan.plus.comwrote in message
news:46**********************@ptn-nntp-reader02.plus.net...
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- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -
can you post the query you are using and the error you are getting

Apr 16 '07 #18

P: n/a
Jo

<le*********@natpro.comwrote in message
news:11**********************@y80g2000hsf.googlegr oups.com...
On Mar 26, 7:52 pm, lesperan...@natpro.com wrote:
>assuming that this is your data
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

and assuming
a) no. ordered is number of books you've received from the supplier
b) you send out one copy of a book title to one individual

you can create a query (qryGivenOut)
that counts the number of books sent out to individuals
SELECT bookTitle, COUNT(bookTitle) as numberGivenOut
FROM tblIndividual GROUP BY bookTitle

and create a 2nd query (qryInStock) to give you total books in stock
SELECT bookTitle, SUM(noOrdered) as numberInStock
FROM tblBook GROUP BY bookTitle

and create a 3rd query to give you the number of remaining books
SELECT qryInStock.bookTitle,
numberInStock,
numberGivenOut,
numberInStock - numberGivenOut as numberRemaining
FROM qryInStock left join
qryGivenOut on qryInStock.bookTitle =
qryGivenOut.bookTitle

On Mar 26, 3:37 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
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" <j...@NOSPAM.stallan.plus.comwrote in message
>news:46**********************@ptn-nntp-reader02.plus.net...
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- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -

can you post the query you are using and the error you are getting
Hi there,
I'm having a real problem replicating the error! I'm getting different
errors each time. I think I am going about this the totally wrong way. I
have set up a query using the 'Books Sent Out' Table. This works fine and
produces a list of all the books that have been sent out so far.
Then from that query I am trying to set up a COUNT function as follows:
COUNT(Sleepyhead (Albanian)) but am now getting the error:

'Can't have aggregate function in WHERE clause ([Books Sent Out Query].[Book
Title & Language]=Count(Sleepyhead ("Albanian"))).'
>
I am totally baffled as I can't reporoduce the operator/operand error. I
have a feeling that I may be writing the function in the wrong place, but to
be honest...I really haven't a clue!
Regards,
Jo
Apr 19 '07 #19

P: n/a
On Apr 19, 2:49 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message

news:11**********************@y80g2000hsf.googlegr oups.com...


On Mar 26, 7:52 pm, lesperan...@natpro.com wrote:
assuming that this is your data
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
and assuming
a) no. ordered is number of books you've received from the supplier
b) you send out one copy of a book title to one individual
you can create a query (qryGivenOut)
that counts the number of books sent out to individuals
SELECT bookTitle, COUNT(bookTitle) as numberGivenOut
FROM tblIndividual GROUP BY bookTitle
and create a 2nd query (qryInStock) to give you total books in stock
SELECT bookTitle, SUM(noOrdered) as numberInStock
FROM tblBook GROUP BY bookTitle
and create a 3rd query to give you the number of remaining books
SELECT qryInStock.bookTitle,
numberInStock,
numberGivenOut,
numberInStock - numberGivenOut as numberRemaining
FROM qryInStock left join
qryGivenOut on qryInStock.bookTitle =
qryGivenOut.bookTitle
On Mar 26, 3:37 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
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" <j...@NOSPAM.stallan.plus.comwrote in message
news:46**********************@ptn-nntp-reader02.plus.net...
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- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -
can you post the query you are using and the error you are getting

Hi there,
I'm having a real problem replicating the error! I'm getting different
errors each time. I think I am going about this the totally wrong way. I
have set up a query using the 'Books Sent Out' Table. This works fine and
produces a list of all the books that have been sent out so far.
Then from that query I am trying to set up a COUNT function as follows:
COUNT(Sleepyhead (Albanian)) but am now getting the error:

'Can't have aggregate function in WHERE clause ([Books Sent Out Query].[Book
Title & Language]=Count(Sleepyhead ("Albanian"))).'

I am totally baffled as I can't reporoduce the operator/operand error. I
have a feeling that I may be writing the function in the wrong place, but to
be honest...I really haven't a clue!
Regards,
Jo- Hide quoted text -

- Show quoted text -
is Sleepyhead a function ? what does it do ? can you post it ?

what does your first query look like ? ie. SELECT ... FROM ...
WHERE ...

and your second query ?
SELECT ..
FROM [Books Sent Out Query]
WHERE
([Books Sent Out Query].[Book Title & Language]=Count(Sleepyhead
("Albanian")))

what do you get with the following
SELECT COUNT(*)
FROM [Books Sent Out Query]
WHERE [Book Title & Language] = Sleepyhead ("Albanian")

Apr 19 '07 #20

P: n/a
Jo

<le*********@natpro.comwrote in message
news:11**********************@y5g2000hsa.googlegro ups.com...
On Apr 19, 2:49 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
><lesperan...@natpro.comwrote in message

news:11**********************@y80g2000hsf.googleg roups.com...


On Mar 26, 7:52 pm, lesperan...@natpro.com wrote:
assuming that this is your data
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
>and assuming
a) no. ordered is number of books you've received from the supplier
b) you send out one copy of a book title to one individual
>you can create a query (qryGivenOut)
that counts the number of books sent out to individuals
SELECT bookTitle, COUNT(bookTitle) as numberGivenOut
FROM tblIndividual GROUP BY bookTitle
>and create a 2nd query (qryInStock) to give you total books in stock
SELECT bookTitle, SUM(noOrdered) as numberInStock
FROM tblBook GROUP BY bookTitle
>and create a 3rd query to give you the number of remaining books
SELECT qryInStock.bookTitle,
numberInStock,
numberGivenOut,
numberInStock - numberGivenOut as numberRemaining
FROM qryInStock left join
qryGivenOut on qryInStock.bookTitle =
qryGivenOut.bookTitle
>On Mar 26, 3:37 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
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" <j...@NOSPAM.stallan.plus.comwrote in message
>news:46**********************@ptn-nntp-reader02.plus.net...
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- Hide quoted text -
- Show quoted text -- Hide quoted text -
>- Show quoted text -
can you post the query you are using and the error you are getting

Hi there,
I'm having a real problem replicating the error! I'm getting different
errors each time. I think I am going about this the totally wrong way.
I
have set up a query using the 'Books Sent Out' Table. This works fine
and
produces a list of all the books that have been sent out so far.
Then from that query I am trying to set up a COUNT function as follows:
COUNT(Sleepyhead (Albanian)) but am now getting the error:

'Can't have aggregate function in WHERE clause ([Books Sent Out
Query].[Book
Title & Language]=Count(Sleepyhead ("Albanian"))).'

I am totally baffled as I can't reporoduce the operator/operand error. I
have a feeling that I may be writing the function in the wrong place, but
to
be honest...I really haven't a clue!
Regards,
Jo- Hide quoted text -

- Show quoted text -

is Sleepyhead a function ? what does it do ? can you post it ?

what does your first query look like ? ie. SELECT ... FROM ...
WHERE ...

and your second query ?
SELECT ..
FROM [Books Sent Out Query]
WHERE
([Books Sent Out Query].[Book Title & Language]=Count(Sleepyhead
("Albanian")))

what do you get with the following
SELECT COUNT(*)
FROM [Books Sent Out Query]
WHERE [Book Title & Language] = Sleepyhead ("Albanian")
Sleepyhead is a book title....my table of book title & language is set up
with titles such as:
Sleepyhead (Albanian)
Sleepyhead (Gujurati)
Heads, Shoulders, Knees & Toes (Albanian)
etc
I'm so sorry, but I do not understand the SELECT bit - is that a function in
itself. My knowledge of query functions and syntax is so limited that I am
really struggling to understand this. I'm not even managing to set up the
following in the right way:

SELECT COUNT(*)
FROM [Books Sent Out Query]
WHERE [Book Title & Language] = Sleepyhead ("Albanian")
I feel I may be wasting you time and perhaps I should go back to doing this
all manually!
Is there a way I could post (or email you) with a copy of the database (it's
no more than 300kb) - not with real data of course, but with a couple of
made-up entries?

Apr 20 '07 #21

P: n/a
On Apr 20, 12:38 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message

news:11**********************@y5g2000hsa.googlegro ups.com...


On Apr 19, 2:49 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message
>news:11**********************@y80g2000hsf.googleg roups.com...
On Mar 26, 7:52 pm, lesperan...@natpro.com wrote:
assuming that this is your data
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
and assuming
a) no. ordered is number of books you've received from the supplier
b) you send out one copy of a book title to one individual
you can create a query (qryGivenOut)
that counts the number of books sent out to individuals
SELECT bookTitle, COUNT(bookTitle) as numberGivenOut
FROM tblIndividual GROUP BY bookTitle
and create a 2nd query (qryInStock) to give you total books in stock
SELECT bookTitle, SUM(noOrdered) as numberInStock
FROM tblBook GROUP BY bookTitle
and create a 3rd query to give you the number of remaining books
SELECT qryInStock.bookTitle,
numberInStock,
numberGivenOut,
numberInStock - numberGivenOut as numberRemaining
FROM qryInStock left join
qryGivenOut on qryInStock.bookTitle =
qryGivenOut.bookTitle
On Mar 26, 3:37 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
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" <j...@NOSPAM.stallan.plus.comwrote in message
news:46**********************@ptn-nntp-reader02.plus.net...
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- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -
can you post the query you are using and the error you are getting
Hi there,
I'm having a real problem replicating the error! I'm getting different
errors each time. I think I am going about this the totally wrong way.
I
have set up a query using the 'Books Sent Out' Table. This works fine
and
produces a list of all the books that have been sent out so far.
Then from that query I am trying to set up a COUNT function as follows:
COUNT(Sleepyhead (Albanian)) but am now getting the error:
'Can't have aggregate function in WHERE clause ([Books Sent Out
Query].[Book
Title & Language]=Count(Sleepyhead ("Albanian"))).'
I am totally baffled as I can't reporoduce the operator/operand error. I
have a feeling that I may be writing the function in the wrong place, but
to
be honest...I really haven't a clue!
Regards,
Jo- Hide quoted text -
- Show quoted text -
is Sleepyhead a function ? what does it do ? can you post it ?
what does your first query look like ? ie. SELECT ... FROM ...
WHERE ...
and your second query ?
SELECT ..
FROM [Books Sent Out Query]
WHERE
([Books Sent Out Query].[Book Title & Language]=Count(Sleepyhead
("Albanian")))
what do you get with the following
SELECT COUNT(*)
FROM [Books Sent Out Query]
WHERE [Book Title & Language] = Sleepyhead ("Albanian")

Sleepyhead is a book title....my table of book title & language is set up
with titles such as:
Sleepyhead (Albanian)
Sleepyhead (Gujurati)
Heads, Shoulders, Knees & Toes (Albanian)
etc
I'm so sorry, but I do not understand the SELECT bit - is that a function in
itself. My knowledge of query functions and syntax is so limited that I am
really struggling to understand this. I'm not even managing to set up the
following in the right way:

SELECT COUNT(*)
FROM [Books Sent Out Query]
WHERE [Book Title & Language] = Sleepyhead ("Albanian")

I feel I may be wasting you time and perhaps I should go back to doing this
all manually!
Is there a way I could post (or email you) with a copy of the database (it's
no more than 300kb) - not with real data of course, but with a couple of
made-up entries?- Hide quoted text -

- Show quoted text -
create a new query
select the query [books sent out query] from the list of possible
queries
click the 'totals' icon on the toolbar (looks like the letter E or
Greek epsilon)
drag the [book title & language] field to the list of 'fields' you
want to see
add Sleepyhead("Albanian") to the 'criteria' of this field
add count to the 'total' field

I'm using access97, so if you using a different version, things might
be slightly different

Apr 20 '07 #22

P: n/a
Jo

<le*********@natpro.comwrote in message
news:11**********************@n76g2000hsh.googlegr oups.com...
On Apr 20, 12:38 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
><lesperan...@natpro.comwrote in message

news:11**********************@y5g2000hsa.googlegr oups.com...


On Apr 19, 2:49 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message
>>news:11**********************@y80g2000hsf.google groups.com...
On Mar 26, 7:52 pm, lesperan...@natpro.com wrote:
assuming that this is your data
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
>and assuming
a) no. ordered is number of books you've received from the
supplier
b) you send out one copy of a book title to one individual
>you can create a query (qryGivenOut)
that counts the number of books sent out to individuals
SELECT bookTitle, COUNT(bookTitle) as numberGivenOut
FROM tblIndividual GROUP BY bookTitle
>and create a 2nd query (qryInStock) to give you total books in
stock
SELECT bookTitle, SUM(noOrdered) as numberInStock
FROM tblBook GROUP BY bookTitle
>and create a 3rd query to give you the number of remaining books
SELECT qryInStock.bookTitle,
numberInStock,
numberGivenOut,
numberInStock - numberGivenOut as numberRemaining
FROM qryInStock left join
qryGivenOut on qryInStock.bookTitle =
qryGivenOut.bookTitle
>On Mar 26, 3:37 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
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" <j...@NOSPAM.stallan.plus.comwrote in message
>news:46**********************@ptn-nntp-reader02.plus.net...
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- Hide quoted text -
- Show quoted text -- Hide quoted text -
>- Show quoted text -
can you post the query you are using and the error you are getting
>Hi there,
I'm having a real problem replicating the error! I'm getting
different
errors each time. I think I am going about this the totally wrong
way.
I
have set up a query using the 'Books Sent Out' Table. This works fine
and
produces a list of all the books that have been sent out so far.
Then from that query I am trying to set up a COUNT function as
follows:
COUNT(Sleepyhead (Albanian)) but am now getting the error:
>'Can't have aggregate function in WHERE clause ([Books Sent Out
Query].[Book
Title & Language]=Count(Sleepyhead ("Albanian"))).'
>I am totally baffled as I can't reporoduce the operator/operand error.
I
have a feeling that I may be writing the function in the wrong place,
but
to
be honest...I really haven't a clue!
Regards,
Jo- Hide quoted text -
>- Show quoted text -
is Sleepyhead a function ? what does it do ? can you post it ?
what does your first query look like ? ie. SELECT ... FROM ...
WHERE ...
and your second query ?
SELECT ..
FROM [Books Sent Out Query]
WHERE
([Books Sent Out Query].[Book Title & Language]=Count(Sleepyhead
("Albanian")))
what do you get with the following
SELECT COUNT(*)
FROM [Books Sent Out Query]
WHERE [Book Title & Language] = Sleepyhead ("Albanian")

Sleepyhead is a book title....my table of book title & language is set up
with titles such as:
Sleepyhead (Albanian)
Sleepyhead (Gujurati)
Heads, Shoulders, Knees & Toes (Albanian)
etc
I'm so sorry, but I do not understand the SELECT bit - is that a function
in
itself. My knowledge of query functions and syntax is so limited that I
am
really struggling to understand this. I'm not even managing to set up the
following in the right way:

SELECT COUNT(*)
FROM [Books Sent Out Query]
WHERE [Book Title & Language] = Sleepyhead ("Albanian")

I feel I may be wasting you time and perhaps I should go back to doing
this
all manually!
Is there a way I could post (or email you) with a copy of the database
(it's
no more than 300kb) - not with real data of course, but with a couple of
made-up entries?- Hide quoted text -

- Show quoted text -

create a new query
select the query [books sent out query] from the list of possible
queries
click the 'totals' icon on the toolbar (looks like the letter E or
Greek epsilon)
drag the [book title & language] field to the list of 'fields' you
want to see
add Sleepyhead("Albanian") to the 'criteria' of this field
add count to the 'total' field

I'm using access97, so if you using a different version, things might
be slightly different
So sorry, but I'm getting an error again. I'm doing the above exactly.
Previously it didn't like the () brackets in the Book Title & Language eg
Sleepyhead (Albanian)....Access thought it was an operand. So I have
changed the Titles to read eg Sleepyhead - Albanian. This, I have done in
all tables which are related. However, now I am now getting the error:
'Data type mismatch in criteria expression'. I cannot see any mismatch at
all.
Apr 23 '07 #23

P: n/a
On Apr 23, 12:20 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message

news:11**********************@n76g2000hsh.googlegr oups.com...


On Apr 20, 12:38 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message
>news:11**********************@y5g2000hsa.googlegr oups.com...
On Apr 19, 2:49 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message
>news:11**********************@y80g2000hsf.googleg roups.com...
On Mar 26, 7:52 pm, lesperan...@natpro.com wrote:
assuming that this is your data
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
and assuming
a) no. ordered is number of books you've received from the
supplier
b) you send out one copy of a book title to one individual
you can create a query (qryGivenOut)
that counts the number of books sent out to individuals
SELECT bookTitle, COUNT(bookTitle) as numberGivenOut
FROM tblIndividual GROUP BY bookTitle
and create a 2nd query (qryInStock) to give you total books in
stock
SELECT bookTitle, SUM(noOrdered) as numberInStock
FROM tblBook GROUP BY bookTitle
and create a 3rd query to give you the number of remaining books
SELECT qryInStock.bookTitle,
numberInStock,
numberGivenOut,
numberInStock - numberGivenOut as numberRemaining
FROM qryInStock left join
qryGivenOut on qryInStock.bookTitle =
qryGivenOut.bookTitle
On Mar 26, 3:37 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
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" <j...@NOSPAM.stallan.plus.comwrote in message
news:46**********************@ptn-nntp-reader02.plus.net...
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- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -
can you post the query you are using and the error you are getting
Hi there,
I'm having a real problem replicating the error! I'm getting
different
errors each time. I think I am going about this the totally wrong
way.
I
have set up a query using the 'Books Sent Out' Table. This works fine
and
produces a list of all the books that have been sent out so far.
Then from that query I am trying to set up a COUNT function as
follows:
COUNT(Sleepyhead (Albanian)) but am now getting the error:
'Can't have aggregate function in WHERE clause ([Books Sent Out
Query].[Book
Title & Language]=Count(Sleepyhead ("Albanian"))).'
I am totally baffled as I can't reporoduce the operator/operand error.
I
have a feeling that I may be writing the function in the wrong place,
but
to
be honest...I really haven't a clue!
Regards,
Jo- Hide quoted text -
- Show quoted text -
is Sleepyhead a function ? what does it do ? can you post it ?
what does your first query look like ? ie. SELECT ... FROM ...
WHERE ...
and your second query ?
SELECT ..
FROM [Books Sent Out Query]
WHERE
([Books Sent Out Query].[Book Title & Language]=Count(Sleepyhead
("Albanian")))
what do you get with the following
SELECT COUNT(*)
FROM [Books Sent Out Query]
WHERE [Book Title & Language] = Sleepyhead ("Albanian")
Sleepyhead is a book title....my table of book title & language is set up
with titles such as:
Sleepyhead (Albanian)
Sleepyhead (Gujurati)
Heads, Shoulders, Knees & Toes (Albanian)
etc
I'm so sorry, but I do not understand the SELECT bit - is that a function
in
itself. My knowledge of query functions and syntax is so limited that I
am
really struggling to understand this. I'm not even managing to set up the
following in the right way:
SELECT COUNT(*)
FROM [Books Sent Out Query]
WHERE [Book Title & Language] = Sleepyhead ("Albanian")
I feel I may be wasting you time and perhaps I should go back to doing
this
all manually!
Is there a way I could post (or email you) with a copy of the database
(it's
no more than 300kb) - not with real data of course, but with a couple of
made-up entries?- Hide quoted text -
- Show quoted text -
create a new query
select the query [books sent out query] from the list of possible
queries
click the 'totals' icon on the toolbar (looks like the letter E or
Greek epsilon)
drag the [book title & language] field to the list of 'fields' you
want to see
add Sleepyhead("Albanian") to the 'criteria' of this field
add count to the 'total' field
I'm using access97, so if you using a different version, things might
be slightly different

So sorry, but I'm getting an error again. I'm doing the above exactly.
Previously it didn't like the () brackets in the Book Title & Language eg
Sleepyhead (Albanian)....Access thought it was an operand. So I have
changed the Titles to read eg Sleepyhead - Albanian. This, I have done in
all tables which are related. However, now I am now getting the error:
'Data type mismatch in criteria expression'. I cannot see any mismatch at
all.- Hide quoted text -

- Show quoted text -
if the title of the book is

Sleepyhead("Albanian")

you'll need to put single quote (ie. ') around the title & language,
ie

'Sleepyhead("Albanian")'
'sleepyhead - albanian'

the book title using both brackets and double quotes is causing you
lots of grief unless you add the single quotes
because ms-access will think that sleepyhead(...) is calling the
function sleepyhead passing it an argument called albanian

and without the single quotes, sleepyhead - albanian means subtract
albanian from sleepyhead

the real solution is to break the 'title' and the 'language' into 2
distinct fields

Apr 23 '07 #24

P: n/a
Jo

<le*********@natpro.comwrote in message
news:11**********************@b58g2000hsg.googlegr oups.com...
On Apr 23, 12:20 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
><lesperan...@natpro.comwrote in message

news:11**********************@n76g2000hsh.googleg roups.com...


On Apr 20, 12:38 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message
>>news:11**********************@y5g2000hsa.googleg roups.com...
On Apr 19, 2:49 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message
>>news:11**********************@y80g2000hsf.google groups.com...
On Mar 26, 7:52 pm, lesperan...@natpro.com wrote:
assuming that this is your data
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
>and assuming
a) no. ordered is number of books you've received from the
supplier
b) you send out one copy of a book title to one individual
>you can create a query (qryGivenOut)
that counts the number of books sent out to individuals
SELECT bookTitle, COUNT(bookTitle) as numberGivenOut
FROM tblIndividual GROUP BY bookTitle
>and create a 2nd query (qryInStock) to give you total books in
stock
SELECT bookTitle, SUM(noOrdered) as numberInStock
FROM tblBook GROUP BY bookTitle
>and create a 3rd query to give you the number of remaining books
SELECT qryInStock.bookTitle,
numberInStock,
numberGivenOut,
numberInStock - numberGivenOut as
numberRemaining
FROM qryInStock left join
qryGivenOut on qryInStock.bookTitle =
qryGivenOut.bookTitle
>On Mar 26, 3:37 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
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" <j...@NOSPAM.stallan.plus.comwrote in message
>news:46**********************@ptn-nntp-reader02.plus.net...
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- Hide quoted text -
- Show quoted text -- Hide quoted text -
>- Show quoted text -
can you post the query you are using and the error you are
getting
>Hi there,
I'm having a real problem replicating the error! I'm getting
different
errors each time. I think I am going about this the totally wrong
way.
I
have set up a query using the 'Books Sent Out' Table. This works
fine
and
produces a list of all the books that have been sent out so far.
Then from that query I am trying to set up a COUNT function as
follows:
COUNT(Sleepyhead (Albanian)) but am now getting the error:
>'Can't have aggregate function in WHERE clause ([Books Sent Out
Query].[Book
Title & Language]=Count(Sleepyhead ("Albanian"))).'
>I am totally baffled as I can't reporoduce the operator/operand
error.
I
have a feeling that I may be writing the function in the wrong
place,
but
to
be honest...I really haven't a clue!
Regards,
Jo- Hide quoted text -
>- Show quoted text -
is Sleepyhead a function ? what does it do ? can you post it ?
what does your first query look like ? ie. SELECT ... FROM ...
WHERE ...
and your second query ?
SELECT ..
FROM [Books Sent Out Query]
WHERE
([Books Sent Out Query].[Book Title & Language]=Count(Sleepyhead
("Albanian")))
what do you get with the following
SELECT COUNT(*)
FROM [Books Sent Out Query]
WHERE [Book Title & Language] = Sleepyhead ("Albanian")
>Sleepyhead is a book title....my table of book title & language is set
up
with titles such as:
Sleepyhead (Albanian)
Sleepyhead (Gujurati)
Heads, Shoulders, Knees & Toes (Albanian)
etc
I'm so sorry, but I do not understand the SELECT bit - is that a
function
in
itself. My knowledge of query functions and syntax is so limited that
I
am
really struggling to understand this. I'm not even managing to set up
the
following in the right way:
>SELECT COUNT(*)
FROM [Books Sent Out Query]
WHERE [Book Title & Language] = Sleepyhead ("Albanian")
>I feel I may be wasting you time and perhaps I should go back to doing
this
all manually!
Is there a way I could post (or email you) with a copy of the database
(it's
no more than 300kb) - not with real data of course, but with a couple
of
made-up entries?- Hide quoted text -
>- Show quoted text -
create a new query
select the query [books sent out query] from the list of possible
queries
click the 'totals' icon on the toolbar (looks like the letter E or
Greek epsilon)
drag the [book title & language] field to the list of 'fields' you
want to see
add Sleepyhead("Albanian") to the 'criteria' of this field
add count to the 'total' field
I'm using access97, so if you using a different version, things might
be slightly different

So sorry, but I'm getting an error again. I'm doing the above exactly.
Previously it didn't like the () brackets in the Book Title & Language eg
Sleepyhead (Albanian)....Access thought it was an operand. So I have
changed the Titles to read eg Sleepyhead - Albanian. This, I have done
in
all tables which are related. However, now I am now getting the error:
'Data type mismatch in criteria expression'. I cannot see any mismatch
at
all.- Hide quoted text -

- Show quoted text -

if the title of the book is

Sleepyhead("Albanian")

you'll need to put single quote (ie. ') around the title & language,
ie

'Sleepyhead("Albanian")'
'sleepyhead - albanian'

the book title using both brackets and double quotes is causing you
lots of grief unless you add the single quotes
because ms-access will think that sleepyhead(...) is calling the
function sleepyhead passing it an argument called albanian

and without the single quotes, sleepyhead - albanian means subtract
albanian from sleepyhead

the real solution is to break the 'title' and the 'language' into 2
distinct fields
Access was putting the double quotes in - not me! I have tried putting
single quotes in the 'criteria' of the field and it still doesn't like it.
eg. 'Sleepyhead - Albanian' or 'Head, Shoulders, Knees & Toes - French'.
Think I might start again using 2 separate fields as you suggest. I set it
all up like this in the first place because I thought that 2 separate fields
would cause confusion as I have the same titles in many languages! (eg
Sleepyhead in Bengali, Albanian, French and about 10 other languages).
You can tell that I'm a real novice........thanks for your patience and help
so far. I'll have another go.
Regards,
Jo
Apr 24 '07 #25

P: n/a
On Apr 24, 2:06 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message

news:11**********************@b58g2000hsg.googlegr oups.com...
On Apr 23, 12:20 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message
>news:11**********************@n76g2000hsh.googleg roups.com...
On Apr 20, 12:38 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message
>news:11**********************@y5g2000hsa.googlegr oups.com...
On Apr 19, 2:49 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message
>news:11**********************@y80g2000hsf.googleg roups.com...
On Mar 26, 7:52 pm, lesperan...@natpro.com wrote:
assuming that this is your data
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
and assuming
a) no. ordered is number of books you've received from the
supplier
b) you send out one copy of a book title to one individual
you can create a query (qryGivenOut)
that counts the number of books sent out to individuals
SELECT bookTitle, COUNT(bookTitle) as numberGivenOut
FROM tblIndividual GROUP BY bookTitle
and create a 2nd query (qryInStock) to give you total books in
stock
SELECT bookTitle, SUM(noOrdered) as numberInStock
FROM tblBook GROUP BY bookTitle
and create a 3rd query to give you the number of remaining books
SELECT qryInStock.bookTitle,
numberInStock,
numberGivenOut,
numberInStock - numberGivenOut as
numberRemaining
FROM qryInStock left join
qryGivenOut on qryInStock.bookTitle =
qryGivenOut.bookTitle
On Mar 26, 3:37 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
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" <j...@NOSPAM.stallan.plus.comwrote in message
news:46**********************@ptn-nntp-reader02.plus.net...
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- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -
can you post the query you are using and the error you are
getting
Hi there,
I'm having a real problem replicating the error! I'm getting
different
errors each time. I think I am going about this the totally wrong
way.
I
have set up a query using the 'Books Sent Out' Table. This works
fine
and
produces a list of all the books that have been sent out so far.
Then from that query I am trying to set up a COUNT function as
follows:
COUNT(Sleepyhead (Albanian)) but am now getting the error:
'Can't have aggregate function in WHERE clause ([Books Sent Out
Query].[Book
Title & Language]=Count(Sleepyhead ("Albanian"))).'
I am totally baffled as I can't reporoduce the operator/operand
error.
I
have a feeling that I may be writing the function in the wrong
place,
but
to
be honest...I really haven't a clue!
Regards,
Jo- Hide quoted text -
- Show quoted text -
is Sleepyhead a function ? what does it do ? can you post it ?
what does your first query look like ? ie. SELECT ... FROM ...
WHERE ...
and your second query ?
SELECT ..
FROM [Books Sent Out Query]
WHERE
([Books Sent Out Query].[Book Title & Language]=Count(Sleepyhead
("Albanian")))
what do you get with the following
SELECT COUNT(*)
FROM [Books Sent Out Query]
WHERE [Book Title & Language] = Sleepyhead ("Albanian")
Sleepyhead is a book title....my table of book title & language is set
up
with titles such as:
Sleepyhead (Albanian)
Sleepyhead (Gujurati)
Heads, Shoulders, Knees & Toes (Albanian)
etc
I'm so sorry, but I do not understand the SELECT bit - is that a
function
in
itself. My knowledge of query functions and syntax is so limited that
I
am
really struggling to understand this. I'm not even managing to set up
the
following in the right way:
SELECT COUNT(*)
FROM [Books Sent Out Query]
WHERE [Book Title & Language] = Sleepyhead ("Albanian")
I feel I may be wasting you time and perhaps I should go back to doing
this
all manually!
Is there a way I could post (or email you) with a copy of the database
(it's
no more than 300kb) - not with real data of course, but with a couple
of
made-up entries?- Hide quoted text -
- Show quoted text -
create a new query
select the query [books sent out query] from the list of possible
queries
click the 'totals' icon on the toolbar (looks like the letter E or
Greek epsilon)
drag the [book title & language] field to the list of 'fields' you
want to see
add Sleepyhead("Albanian") to the 'criteria' of this field
add count to the 'total' field
I'm using access97, so if you using a different version, things might
be slightly different
So sorry, but I'm getting an error again. I'm doing the above exactly.
Previously it didn't like the () brackets in the Book Title & Languageeg
Sleepyhead (Albanian)....Access thought it was an operand. So I have
changed the Titles to read eg Sleepyhead - Albanian. This, I have done
in
all tables which are related. However, now I am now getting the error:
'Data type mismatch in criteria expression'. I cannot see any mismatch
at
all.- Hide quoted text -
- Show quoted text -
if the title of the book is
Sleepyhead("Albanian")
you'll need to put single quote (ie. ') around the title & language,
ie
'Sleepyhead("Albanian")'
'sleepyhead - albanian'
the book title using both brackets and double quotes is causing you
lots of grief unless you add the single quotes
because ms-access will think that sleepyhead(...) is calling the
function sleepyhead passing it an argument called albanian
and without the single quotes, sleepyhead - albanian means subtract
albanian from sleepyhead
the real solution is to break the 'title' and the 'language' into 2
distinct fields

Access was putting the double quotes in - not me! I have tried putting
single quotes in the 'criteria' of the field and it still doesn't like it.
eg. 'Sleepyhead - Albanian' or 'Head, Shoulders, Knees & Toes - French'.
Think I might start again using 2 separate fields as you suggest. I set it
all up like this in the first place because I thought that 2 separate fields
would cause confusion as I have the same ...

read more - Hide quoted text -

- Show quoted text -
yes, if you have query and the field is a text field, and you type ABC
as the criteria, access will automatically add the double quotes...
it's trying to create a valid query based on the information it
knows... but you can type 'ABC' and it should accept it and should
execute properly - what error does it give you ?

Apr 24 '07 #26

P: n/a
Jo

<le*********@natpro.comwrote in message
news:11**********************@o40g2000prh.googlegr oups.com...
On Apr 24, 2:06 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message

news:11**********************@b58g2000hsg.googlegr oups.com...
On Apr 23, 12:20 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message
>news:11**********************@n76g2000hsh.googleg roups.com...
On Apr 20, 12:38 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message
>news:11**********************@y5g2000hsa.googlegr oups.com...
On Apr 19, 2:49 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message
>news:11**********************@y80g2000hsf.googleg roups.com...
On Mar 26, 7:52 pm, lesperan...@natpro.com wrote:
assuming that this is your data
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
and assuming
a) no. ordered is number of books you've received from the
supplier
b) you send out one copy of a book title to one individual
you can create a query (qryGivenOut)
that counts the number of books sent out to individuals
SELECT bookTitle, COUNT(bookTitle) as numberGivenOut
FROM tblIndividual GROUP BY bookTitle
and create a 2nd query (qryInStock) to give you total books in
stock
SELECT bookTitle, SUM(noOrdered) as numberInStock
FROM tblBook GROUP BY bookTitle
and create a 3rd query to give you the number of remaining
books
SELECT qryInStock.bookTitle,
numberInStock,
numberGivenOut,
numberInStock - numberGivenOut as
numberRemaining
FROM qryInStock left join
qryGivenOut on qryInStock.bookTitle =
qryGivenOut.bookTitle
On Mar 26, 3:37 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
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" <j...@NOSPAM.stallan.plus.comwrote in message
news:46**********************@ptn-nntp-reader02.plus.net...
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- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -
can you post the query you are using and the error you are
getting
Hi there,
I'm having a real problem replicating the error! I'm getting
different
errors each time. I think I am going about this the totally
wrong
way.
I
have set up a query using the 'Books Sent Out' Table. This works
fine
and
produces a list of all the books that have been sent out so far.
Then from that query I am trying to set up a COUNT function as
follows:
COUNT(Sleepyhead (Albanian)) but am now getting the error:
'Can't have aggregate function in WHERE clause ([Books Sent Out
Query].[Book
Title & Language]=Count(Sleepyhead ("Albanian"))).'
I am totally baffled as I can't reporoduce the operator/operand
error.
I
have a feeling that I may be writing the function in the wrong
place,
but
to
be honest...I really haven't a clue!
Regards,
Jo- Hide quoted text -
- Show quoted text -
is Sleepyhead a function ? what does it do ? can you post it ?
what does your first query look like ? ie. SELECT ... FROM ...
WHERE ...
and your second query ?
SELECT ..
FROM [Books Sent Out Query]
WHERE
([Books Sent Out Query].[Book Title & Language]=Count(Sleepyhead
("Albanian")))
what do you get with the following
SELECT COUNT(*)
FROM [Books Sent Out Query]
WHERE [Book Title & Language] = Sleepyhead ("Albanian")
Sleepyhead is a book title....my table of book title & language is
set
up
with titles such as:
Sleepyhead (Albanian)
Sleepyhead (Gujurati)
Heads, Shoulders, Knees & Toes (Albanian)
etc
I'm so sorry, but I do not understand the SELECT bit - is that a
function
in
itself. My knowledge of query functions and syntax is so limited
that
I
am
really struggling to understand this. I'm not even managing to set
up
the
following in the right way:
SELECT COUNT(*)
FROM [Books Sent Out Query]
WHERE [Book Title & Language] = Sleepyhead ("Albanian")
I feel I may be wasting you time and perhaps I should go back to
doing
this
all manually!
Is there a way I could post (or email you) with a copy of the
database
(it's
no more than 300kb) - not with real data of course, but with a
couple
of
made-up entries?- Hide quoted text -
- Show quoted text -
create a new query
select the query [books sent out query] from the list of possible
queries
click the 'totals' icon on the toolbar (looks like the letter E or
Greek epsilon)
drag the [book title & language] field to the list of 'fields' you
want to see
add Sleepyhead("Albanian") to the 'criteria' of this field
add count to the 'total' field
I'm using access97, so if you using a different version, things might
be slightly different
So sorry, but I'm getting an error again. I'm doing the above exactly.
Previously it didn't like the () brackets in the Book Title & Language
eg
Sleepyhead (Albanian)....Access thought it was an operand. So I have
changed the Titles to read eg Sleepyhead - Albanian. This, I have done
in
all tables which are related. However, now I am now getting the error:
'Data type mismatch in criteria expression'. I cannot see any mismatch
at
all.- Hide quoted text -
- Show quoted text -
if the title of the book is
Sleepyhead("Albanian")
you'll need to put single quote (ie. ') around the title & language,
ie
'Sleepyhead("Albanian")'
'sleepyhead - albanian'
the book title using both brackets and double quotes is causing you
lots of grief unless you add the single quotes
because ms-access will think that sleepyhead(...) is calling the
function sleepyhead passing it an argument called albanian
and without the single quotes, sleepyhead - albanian means subtract
albanian from sleepyhead
the real solution is to break the 'title' and the 'language' into 2
distinct fields

Access was putting the double quotes in - not me! I have tried putting
single quotes in the 'criteria' of the field and it still doesn't like it.
eg. 'Sleepyhead - Albanian' or 'Head, Shoulders, Knees & Toes - French'.
Think I might start again using 2 separate fields as you suggest. I set
it
all up like this in the first place because I thought that 2 separate
fields
would cause confusion as I have the same ...

read more - Hide quoted text -

- Show quoted text -
yes, if you have query and the field is a text field, and you type ABC
as the criteria, access will automatically add the double quotes...
it's trying to create a valid query based on the information it
knows... but you can type 'ABC' and it should accept it and should
execute properly - what error does it give you ?
'Data type mismatch in criteria expression'
and that is with 'Sleepyhead - Albanian' as the criteria
Apr 25 '07 #27

P: n/a
Jo

<le*********@natpro.comwrote in message
news:11**********************@o40g2000prh.googlegr oups.com...
On Apr 24, 2:06 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message

news:11**********************@b58g2000hsg.googlegr oups.com...
On Apr 23, 12:20 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message
>news:11**********************@n76g2000hsh.googleg roups.com...
On Apr 20, 12:38 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message
>news:11**********************@y5g2000hsa.googlegr oups.com...
On Apr 19, 2:49 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message
>news:11**********************@y80g2000hsf.googleg roups.com...
On Mar 26, 7:52 pm, lesperan...@natpro.com wrote:
assuming that this is your data
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
and assuming
a) no. ordered is number of books you've received from the
supplier
b) you send out one copy of a book title to one individual
you can create a query (qryGivenOut)
that counts the number of books sent out to individuals
SELECT bookTitle, COUNT(bookTitle) as numberGivenOut
FROM tblIndividual GROUP BY bookTitle
and create a 2nd query (qryInStock) to give you total books in
stock
SELECT bookTitle, SUM(noOrdered) as numberInStock
FROM tblBook GROUP BY bookTitle
and create a 3rd query to give you the number of remaining
books
SELECT qryInStock.bookTitle,
numberInStock,
numberGivenOut,
numberInStock - numberGivenOut as
numberRemaining
FROM qryInStock left join
qryGivenOut on qryInStock.bookTitle =
qryGivenOut.bookTitle
On Mar 26, 3:37 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
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" <j...@NOSPAM.stallan.plus.comwrote in message
news:46**********************@ptn-nntp-reader02.plus.net...
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- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -
can you post the query you are using and the error you are
getting
Hi there,
I'm having a real problem replicating the error! I'm getting
different
errors each time. I think I am going about this the totally
wrong
way.
I
have set up a query using the 'Books Sent Out' Table. This works
fine
and
produces a list of all the books that have been sent out so far.
Then from that query I am trying to set up a COUNT function as
follows:
COUNT(Sleepyhead (Albanian)) but am now getting the error:
'Can't have aggregate function in WHERE clause ([Books Sent Out
Query].[Book
Title & Language]=Count(Sleepyhead ("Albanian"))).'
I am totally baffled as I can't reporoduce the operator/operand
error.
I
have a feeling that I may be writing the function in the wrong
place,
but
to
be honest...I really haven't a clue!
Regards,
Jo- Hide quoted text -
- Show quoted text -
is Sleepyhead a function ? what does it do ? can you post it ?
what does your first query look like ? ie. SELECT ... FROM ...
WHERE ...
and your second query ?
SELECT ..
FROM [Books Sent Out Query]
WHERE
([Books Sent Out Query].[Book Title & Language]=Count(Sleepyhead
("Albanian")))
what do you get with the following
SELECT COUNT(*)
FROM [Books Sent Out Query]
WHERE [Book Title & Language] = Sleepyhead ("Albanian")
Sleepyhead is a book title....my table of book title & language is
set
up
with titles such as:
Sleepyhead (Albanian)
Sleepyhead (Gujurati)
Heads, Shoulders, Knees & Toes (Albanian)
etc
I'm so sorry, but I do not understand the SELECT bit - is that a
function
in
itself. My knowledge of query functions and syntax is so limited
that
I
am
really struggling to understand this. I'm not even managing to set
up
the
following in the right way:
SELECT COUNT(*)
FROM [Books Sent Out Query]
WHERE [Book Title & Language] = Sleepyhead ("Albanian")
I feel I may be wasting you time and perhaps I should go back to
doing
this
all manually!
Is there a way I could post (or email you) with a copy of the
database
(it's
no more than 300kb) - not with real data of course, but with a
couple
of
made-up entries?- Hide quoted text -
- Show quoted text -
create a new query
select the query [books sent out query] from the list of possible
queries
click the 'totals' icon on the toolbar (looks like the letter E or
Greek epsilon)
drag the [book title & language] field to the list of 'fields' you
want to see
add Sleepyhead("Albanian") to the 'criteria' of this field
add count to the 'total' field
I'm using access97, so if you using a different version, things might
be slightly different
So sorry, but I'm getting an error again. I'm doing the above exactly.
Previously it didn't like the () brackets in the Book Title & Language
eg
Sleepyhead (Albanian)....Access thought it was an operand. So I have
changed the Titles to read eg Sleepyhead - Albanian. This, I have done
in
all tables which are related. However, now I am now getting the error:
'Data type mismatch in criteria expression'. I cannot see any mismatch
at
all.- Hide quoted text -
- Show quoted text -
if the title of the book is
Sleepyhead("Albanian")
you'll need to put single quote (ie. ') around the title & language,
ie
'Sleepyhead("Albanian")'
'sleepyhead - albanian'
the book title using both brackets and double quotes is causing you
lots of grief unless you add the single quotes
because ms-access will think that sleepyhead(...) is calling the
function sleepyhead passing it an argument called albanian
and without the single quotes, sleepyhead - albanian means subtract
albanian from sleepyhead
the real solution is to break the 'title' and the 'language' into 2
distinct fields

Access was putting the double quotes in - not me! I have tried putting
single quotes in the 'criteria' of the field and it still doesn't like it.
eg. 'Sleepyhead - Albanian' or 'Head, Shoulders, Knees & Toes - French'.
Think I might start again using 2 separate fields as you suggest. I set
it
all up like this in the first place because I thought that 2 separate
fields
would cause confusion as I have the same ...

read more - Hide quoted text -

- Show quoted text -
yes, if you have query and the field is a text field, and you type ABC
as the criteria, access will automatically add the double quotes...
it's trying to create a valid query based on the information it
knows... but you can type 'ABC' and it should accept it and should
execute properly - what error does it give you ?
I have now attempted to set up the tables with separate fields for Book
Title and Language. I have used the 'books sent out' query to set up a
count query with, for example, "Sleepyhead" in the Book Title field and
"Albanian" in the Language field. I have selected Totals and added Count
to the total field. But I am still getting the error as before.
'Data type mismatch in criteria expression'
I really am totally stumped.
May 6 '07 #28

P: n/a
On May 6, 3:36 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message

news:11**********************@o40g2000prh.googlegr oups.com...
On Apr 24, 2:06 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message
news:11**********************@b58g2000hsg.googlegr oups.com...
On Apr 23, 12:20 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
><lesperan...@natpro.comwrote in message
>>news:11**********************@n76g2000hsh.google groups.com...
On Apr 20, 12:38 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
><lesperan...@natpro.comwrote in message
>>news:11**********************@y5g2000hsa.googleg roups.com...
On Apr 19, 2:49 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
><lesperan...@natpro.comwrote in message
>>news:11**********************@y80g2000hsf.google groups.com...
On Mar 26, 7:52 pm, lesperan...@natpro.com wrote:
>assuming that this is your data
>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
>and assuming
> a) no. ordered is number of books you've received from the
>supplier
> b) you send out one copy of a book title to one individual
>you can create a query (qryGivenOut)
>that counts the number of books sent out to individuals
> SELECT bookTitle, COUNT(bookTitle) as numberGivenOut
> FROM tblIndividual GROUP BY bookTitle
>and create a 2nd query (qryInStock) to give you total booksin
>stock
> SELECT bookTitle, SUM(noOrdered) as numberInStock
> FROM tblBook GROUP BY bookTitle
>and create a 3rd query to give you the number of remaining
>books
> SELECT qryInStock.bookTitle,
> numberInStock,
> numberGivenOut,
> numberInStock - numberGivenOut as
>numberRemaining
> FROM qryInStock left join
> qryGivenOut on qryInStock.bookTitle =
>qryGivenOut.bookTitle
>On Mar 26, 3:37 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
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" <j...@NOSPAM.stallan.plus.comwrote in message
>news:46**********************@ptn-nntp-reader02.plus.net...
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 tryingto
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- Hide quoted text -
- Show quoted text -- Hide quoted text -
>- Show quoted text -
can you post the query you are using and the error you are
getting
>Hi there,
>I'm having a real problem replicating the error! I'm getting
>different
>errors each time. I think I am going about this the totally
>wrong
>way.
>I
>have set up a query using the 'Books Sent Out' Table. This works
>fine
>and
>produces a list of all the books that have been sent out so far.
>Then from that query I am trying to set up a COUNT function as
>follows:
>COUNT(Sleepyhead (Albanian)) but am now getting the error:
>'Can't have aggregate function in WHERE clause ([Books Sent Out
>Query].[Book
>Title & Language]=Count(Sleepyhead ("Albanian"))).'
>I am totally baffled as I can't reporoduce the operator/operand
>error.
>I
>have a feeling that I may be writing the function in the wrong
>place,
>but
>to
>be honest...I really haven't a clue!
>Regards,
>Jo- Hide quoted text -
>- Show quoted text -
is Sleepyhead a function ? what does it do ? can you post it ?
what does your first query look like ? ie. SELECT ... FROM ...
WHERE ...
and your second query ?
SELECT ..
FROM [Books Sent Out Query]
WHERE
([Books Sent Out Query].[Book Title & Language]=Count(Sleepyhead
("Albanian")))
what do you get with the following
SELECT COUNT(*)
FROM [Books Sent Out Query]
WHERE [Book Title & Language] = Sleepyhead ("Albanian")
>Sleepyhead is a book title....my table of book title & language is
>set
>up
>with titles such as:
>Sleepyhead (Albanian)
>Sleepyhead (Gujurati)
>Heads, Shoulders, Knees & Toes (Albanian)
>etc
>I'm so sorry, but I do not understand the SELECT bit - is that a
>function
>in
>itself. My knowledge of query functions and syntax is so limited
>that
>I
>am
>really struggling to understand this. I'm not even managing to set
>up
>the
>following in the right way:
>SELECT COUNT(*)
FROM [Books Sent Out Query]
WHERE [Book Title & Language] = Sleepyhead ("Albanian")
>I feel I may be wasting you time and perhaps I should go back to
>doing
>this
>all manually!
>Is there a way I could post (or email you) with a copy of the
>database
>(it's
>no more than 300kb) - not with real data of course, but with a
>couple
>of
>made-up entries?- Hide quoted text -
>- Show quoted text -
create a new query
select the query [books sent out query] from the list of possible
queries
click the 'totals' icon on the toolbar (looks like the letter E or
Greek epsilon)
drag the [book title & language] field to the list of 'fields' you
want to see
add Sleepyhead("Albanian") to the 'criteria' of this field
add count to the 'total' field
I'm using access97, so if you using a different version, things might
be slightly different
>So sorry, but I'm getting an error again. I'm doing the above exactly.
>Previously it didn't like the () brackets in the Book Title & Language
>eg
>Sleepyhead (Albanian)....Access thought it was an operand. So I have
>changed the Titles to read eg Sleepyhead - Albanian. This, I have done
>in
>all tables which are related. However, now I am now getting the error:
>'Data type mismatch in criteria expression'. I cannot see any mismatch
>at
>all.- Hide quoted text -
>- Show quoted text -
if the title of the book is
Sleepyhead("Albanian")
you'll need to put single quote (ie. ') around the title & language,
ie
'Sleepyhead("Albanian")'
'sleepyhead - albanian'
the book title using both brackets and double quotes is causing you
lots of grief unless you add the single quotes
because ms-access will think that sleepyhead(...) is calling the
function sleepyhead passing it an argument called albanian
and without the single quotes, sleepyhead - albanian means subtract
albanian from sleepyhead
the real solution is to break the 'title' and the 'language' into 2
distinct fields
Access was putting the double quotes in - not me! I have tried putting
single quotes in the 'criteria' of the field and it still doesn't like it.
eg. 'Sleepyhead - Albanian' or 'Head, Shoulders, Knees & Toes - French'.
Think I might start again using 2 separate fields as you suggest. I set
it
all up like this in the first place because I thought that 2 separate
fields
would cause confusion as I have the same ...
read more - Hide quoted text -
- Show quoted text -

yes, if you have query and the field is a text field, and you type ABC
as the criteria, access will automatically add the double quotes...
it's trying to create a valid query based on the information it
knows... but you can type 'ABC' and it should accept it and should
execute properly - what error does it give you ?

I have now attempted to set up the tables with separate fields for Book
Title and Language. I have used the 'books sent out' query to set up a
count query with, for example, "Sleepyhead" in the Book Title field and
"Albanian" in the Language field. I have selected Totals and added Count
to the total field. But I am still getting the error as before.
'Data type mismatch in criteria expression'
I really am totally stumped.
if you open the query, and remove one field at a time (just don't save
the query) and rerun the quere
which field is causing the 'mismatch' ?

is there a criteria for this field ?

how is the field defined in the table ? text ? date ? number ?

May 7 '07 #29

P: n/a
Jo

<le*********@natpro.comwrote in message
news:11**********************@o5g2000hsb.googlegro ups.com...
On May 6, 3:36 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message

news:11**********************@o40g2000prh.googlegr oups.com...
On Apr 24, 2:06 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message
news:11**********************@b58g2000hsg.googlegr oups.com...
On Apr 23, 12:20 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
><lesperan...@natpro.comwrote in message
>>news:11**********************@n76g2000hsh.google groups.com...
On Apr 20, 12:38 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
><lesperan...@natpro.comwrote in message
>>news:11**********************@y5g2000hsa.googleg roups.com...
On Apr 19, 2:49 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
><lesperan...@natpro.comwrote in message
>>news:11**********************@y80g2000hsf.google groups.com...
On Mar 26, 7:52 pm, lesperan...@natpro.com wrote:
>assuming that this is your data
>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
>and assuming
> a) no. ordered is number of books you've received from
>the
>supplier
> b) you send out one copy of a book title to one individual
>you can create a query (qryGivenOut)
>that counts the number of books sent out to individuals
> SELECT bookTitle, COUNT(bookTitle) as numberGivenOut
> FROM tblIndividual GROUP BY bookTitle
>and create a 2nd query (qryInStock) to give you total books
>in
>stock
> SELECT bookTitle, SUM(noOrdered) as numberInStock
> FROM tblBook GROUP BY bookTitle
>and create a 3rd query to give you the number of remaining
>books
> SELECT qryInStock.bookTitle,
> numberInStock,
> numberGivenOut,
> numberInStock - numberGivenOut as
>numberRemaining
> FROM qryInStock left join
> qryGivenOut on qryInStock.bookTitle =
>qryGivenOut.bookTitle
>On Mar 26, 3:37 pm, "Jo" <j...@NOSPAM.stallan.plus.com>
>wrote:
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" <j...@NOSPAM.stallan.plus.comwrote in message
>news:46**********************@ptn-nntp-reader02.plus.net...
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- Hide quoted text -
- Show quoted text -- Hide quoted text -
>- Show quoted text -
can you post the query you are using and the error you are
getting
>Hi there,
>I'm having a real problem replicating the error! I'm getting
>different
>errors each time. I think I am going about this the totally
>wrong
>way.
>I
>have set up a query using the 'Books Sent Out' Table. This
>works
>fine
>and
>produces a list of all the books that have been sent out so
>far.
>Then from that query I am trying to set up a COUNT function as
>follows:
>COUNT(Sleepyhead (Albanian)) but am now getting the error:
>'Can't have aggregate function in WHERE clause ([Books Sent Out
>Query].[Book
>Title & Language]=Count(Sleepyhead ("Albanian"))).'
>I am totally baffled as I can't reporoduce the operator/operand
>error.
>I
>have a feeling that I may be writing the function in the wrong
>place,
>but
>to
>be honest...I really haven't a clue!
>Regards,
>Jo- Hide quoted text -
>- Show quoted text -
is Sleepyhead a function ? what does it do ? can you post it ?
what does your first query look like ? ie. SELECT ... FROM ...
WHERE ...
and your second query ?
SELECT ..
FROM [Books Sent Out Query]
WHERE
([Books Sent Out Query].[Book Title & Language]=Count(Sleepyhead
("Albanian")))
what do you get with the following
SELECT COUNT(*)
FROM [Books Sent Out Query]
WHERE [Book Title & Language] = Sleepyhead ("Albanian")
>Sleepyhead is a book title....my table of book title & language is
>set
>up
>with titles such as:
>Sleepyhead (Albanian)
>Sleepyhead (Gujurati)
>Heads, Shoulders, Knees & Toes (Albanian)
>etc
>I'm so sorry, but I do not understand the SELECT bit - is that a
>function
>in
>itself. My knowledge of query functions and syntax is so limited
>that
>I
>am
>really struggling to understand this. I'm not even managing to set
>up
>the
>following in the right way:
>SELECT COUNT(*)
FROM [Books Sent Out Query]
WHERE [Book Title & Language] = Sleepyhead ("Albanian")
>I feel I may be wasting you time and perhaps I should go back to
>doing
>this
>all manually!
>Is there a way I could post (or email you) with a copy of the
>database
>(it's
>no more than 300kb) - not with real data of course, but with a
>couple
>of
>made-up entries?- Hide quoted text -
>- Show quoted text -
create a new query
select the query [books sent out query] from the list of possible
queries
click the 'totals' icon on the toolbar (looks like the letter E or
Greek epsilon)
drag the [book title & language] field to the list of 'fields' you
want to see
add Sleepyhead("Albanian") to the 'criteria' of this field
add count to the 'total' field
I'm using access97, so if you using a different version, things
might
be slightly different
>So sorry, but I'm getting an error again. I'm doing the above
>exactly.
>Previously it didn't like the () brackets in the Book Title &
>Language
>eg
>Sleepyhead (Albanian)....Access thought it was an operand. So I have
>changed the Titles to read eg Sleepyhead - Albanian. This, I have
>done
>in
>all tables which are related. However, now I am now getting the
>error:
>'Data type mismatch in criteria expression'. I cannot see any
>mismatch
>at
>all.- Hide quoted text -
>- Show quoted text -
if the title of the book is
Sleepyhead("Albanian")
you'll need to put single quote (ie. ') around the title & language,
ie
'Sleepyhead("Albanian")'
'sleepyhead - albanian'
the book title using both brackets and double quotes is causing you
lots of grief unless you add the single quotes
because ms-access will think that sleepyhead(...) is calling the
function sleepyhead passing it an argument called albanian
and without the single quotes, sleepyhead - albanian means subtract
albanian from sleepyhead
the real solution is to break the 'title' and the 'language' into 2
distinct fields
Access was putting the double quotes in - not me! I have tried putting
single quotes in the 'criteria' of the field and it still doesn't like
it.
eg. 'Sleepyhead - Albanian' or 'Head, Shoulders, Knees & Toes - French'.
Think I might start again using 2 separate fields as you suggest. I set
it
all up like this in the first place because I thought that 2 separate
fields
would cause confusion as I have the same ...
read more - Hide quoted text -
- Show quoted text -

yes, if you have query and the field is a text field, and you type ABC
as the criteria, access will automatically add the double quotes...
it's trying to create a valid query based on the information it
knows... but you can type 'ABC' and it should accept it and should
execute properly - what error does it give you ?

I have now attempted to set up the tables with separate fields for Book
Title and Language. I have used the 'books sent out' query to set up a
count query with, for example, "Sleepyhead" in the Book Title field and
"Albanian" in the Language field. I have selected Totals and added Count
to the total field. But I am still getting the error as before.
'Data type mismatch in criteria expression'
I really am totally stumped.
if you open the query, and remove one field at a time (just don't save
the query) and rerun the quere
which field is causing the 'mismatch' ?

is there a criteria for this field ?

how is the field defined in the table ? text ? date ? number ?

------------------------------------------------------------------------------------

I have taken out each field in turn and am still getting the same error.

My query (without anything taken out) look like the following:

With the 'Books Out Query' selected (this gave me a list of all books sent
out)

Field: Book Title
Language

Table: Books Out
Books Out

Total: Count
Count

Sort:

Show:


Criteria: " Head, Shoulders, Knees and Toes"
"French"
May 9 '07 #30

P: n/a
On May 9, 4:46 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message

news:11**********************@o5g2000hsb.googlegro ups.com...
On May 6, 3:36 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message
news:11**********************@o40g2000prh.googlegr oups.com...
On Apr 24, 2:06 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message
>news:11**********************@b58g2000hsg.googleg roups.com...
On Apr 23, 12:20 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message
>news:11**********************@n76g2000hsh.googleg roups.com...
On Apr 20, 12:38 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message
>news:11**********************@y5g2000hsa.googlegr oups.com...
On Apr 19, 2:49 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message
>news:11**********************@y80g2000hsf.googleg roups.com...
On Mar 26, 7:52 pm, lesperan...@natpro.com wrote:
assuming that this is your data
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
and assuming
a) no. ordered is number of books you've received from
the
supplier
b) you send out one copy of a book title to one individual
you can create a query (qryGivenOut)
that counts the number of books sent out to individuals
SELECT bookTitle, COUNT(bookTitle) as numberGivenOut
FROM tblIndividual GROUP BY bookTitle
and create a 2nd query (qryInStock) to give you total books
in
stock
SELECT bookTitle, SUM(noOrdered) as numberInStock
FROM tblBook GROUP BY bookTitle
and create a 3rd query to give you the number of remaining
books
SELECT qryInStock.bookTitle,
numberInStock,
numberGivenOut,
numberInStock - numberGivenOut as
numberRemaining
FROM qryInStock left join
qryGivenOut on qryInStock.bookTitle =
qryGivenOut.bookTitle
On Mar 26, 3:37 pm, "Jo" <j...@NOSPAM.stallan.plus.com>
wrote:
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" <j...@NOSPAM.stallan.plus.comwrote in message
news:46**********************@ptn-nntp-reader02.plus.net....
Hi there,
I'm Jo and it's the first time I've posted here. I'min
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 workin
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- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -
can you post the query you are using and the error you are
getting
Hi there,
I'm having a real problem replicating the error! I'm getting
different
errors each time. I think I am going about this the totally
wrong
way.
I
have set up a query using the 'Books Sent Out' Table. This
works
fine
and
produces a list of all the books that have been sent out so
far.
Then from that query I am trying to set up a COUNT function as
follows:
COUNT(Sleepyhead (Albanian)) but am now getting the error:
'Can't have aggregate function in WHERE clause ([Books Sent Out
Query].[Book
Title & Language]=Count(Sleepyhead ("Albanian"))).'
I am totally baffled as I can't reporoduce the operator/operand
error.
I
have a feeling that I may be writing the function in the wrong
place,
but
to
be honest...I really haven't a clue!
Regards,
Jo- Hide quoted text -
- Show quoted text -
is Sleepyhead a function ? what does it do ? can you post it ?
what does your first query look like ? ie. SELECT ... FROM ...
WHERE ...
and your second query ?
SELECT ..
FROM [Books Sent Out Query]
WHERE
([Books Sent Out Query].[Book Title & Language]=Count(Sleepyhead
("Albanian")))
what do you get with the following
SELECT COUNT(*)
FROM [Books Sent Out Query]
WHERE [Book Title & Language] = Sleepyhead ("Albanian")
Sleepyhead is a book title....my table of book title & languageis
set
up
with titles such as:
Sleepyhead (Albanian)
Sleepyhead (Gujurati)
Heads, Shoulders, Knees & Toes (Albanian)
etc
I'm so sorry, but I do not understand the SELECT bit - is that a
function
in
itself. My knowledge of query functions and syntax is so limited
that
I
am
really struggling to understand this. I'm not even managing to set
up
the
following in the right way:
SELECT COUNT(*)
FROM [Books Sent Out Query]
WHERE [Book Title & Language] = Sleepyhead ("Albanian")
I feel I may be wasting you time and perhaps I should go back to
doing
this
all manually!
Is there a way I could post (or email you) with a copy of the
database
(it's
no more than 300kb) - not with real data of course, but with a
couple
of
made-up entries?- Hide quoted text -
- Show quoted text -
create a new query
select the query [books sent out query] from the list of possible
queries
click the 'totals' icon on the toolbar (looks like the letter E or
Greek epsilon)
drag the [book title & language] field to the list of 'fields' you
want to see
add Sleepyhead("Albanian") to the 'criteria' of this field
add count to the 'total' field
I'm using access97, so if you using a different version, things
might
be slightly different
So sorry, but I'm getting an error again. I'm doing the above
exactly.
Previously it didn't like the () brackets in the Book Title &
Language
eg
Sleepyhead (Albanian)....Access thought it was an operand. So I have
changed the Titles to read eg Sleepyhead - Albanian. This, I have
done
in
all tables which are related. However, now I am now getting the
error:
'Data type mismatch in criteria expression'. I cannot see any
mismatch
at
all.- Hide quoted text -
- Show quoted text -
if the title of the book is
Sleepyhead("Albanian")
you'll need to put single quote (ie. ') around the title & language,
ie
'Sleepyhead("Albanian")'
'sleepyhead - albanian'
the book title using both brackets and double quotes is causing you
lots of grief unless you add the single quotes
because ms-access will think that sleepyhead(...) is calling the
function sleepyhead passing it an argument called albanian
and without the single quotes, sleepyhead - albanian means subtract
albanian from sleepyhead
the real solution is to break the 'title' and the 'language' into 2
distinct fields
Access was putting the double quotes in - not me! I have tried putting
single quotes in the 'criteria' of the field and it still doesn't like
it.
eg. 'Sleepyhead - Albanian' or 'Head, Shoulders, Knees & Toes - French'.
Think I might start again using 2 separate fields as you suggest. I set
it
all up like this in the first place because I thought that 2 separate
fields
would cause confusion as I have the same ...
read more - Hide quoted text -
- Show quoted text -
yes, if you have query and the field is a text field, and you type ABC
as the criteria, access will automatically add the double quotes...
it's trying to create a valid query based on the information it
knows... but you can type 'ABC' and it should accept it and should
execute properly - what error does it give you ?
I have now attempted to set up the tables with separate fields for Book
Title and Language. I have used the 'books sent out' query to set up a
count query with, for example, "Sleepyhead" in the Book Title field and
"Albanian" in the Language field. I have selected Totals and added Count
to the total field. But I am still getting the error as before.
'Data type mismatch in criteria expression'
I really am totally stumped.

if you open the query, and remove one field at a time (just don't save
the query) and rerun the quere
which field is causing the 'mismatch' ?

is there a criteria for this field ?

how is the field defined in the table ? text ? date ? number ?

---------------------------------------------------------------------------*---------

I have taken out each field in turn and am still getting the same error.

My query (without anything taken out) look like the following:

With the 'Books Out Query' selected (this gave me a list of all books sent
out)

Field: Book Title
Language

Table: Books Out
Books Out

Total: Count
Count

Sort:

Show:


Criteria: " Head, Shoulders, Knees and Toes"
"French"
the problem is that you've put the criteria 'head, shoulders...' as
the criteria of a count field, a count field returns a number not a
textual string.. thus the 'data mismatch'

what you need to do is add 2 more fields (book title, language) with
the total = 'Where' and move you criteria to those 2 fields

book title language book title language
count count where where

"head,...' french

or

book title language nbrCopies:1
where where count

"head,...' french

May 9 '07 #31

P: n/a
Jo

<le*********@natpro.comwrote in message
news:11**********************@l77g2000hsb.googlegr oups.com...
On May 9, 4:46 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message

news:11**********************@o5g2000hsb.googlegro ups.com...
On May 6, 3:36 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message
news:11**********************@o40g2000prh.googlegr oups.com...
On Apr 24, 2:06 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message
>news:11**********************@b58g2000hsg.googleg roups.com...
On Apr 23, 12:20 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message
>news:11**********************@n76g2000hsh.googleg roups.com...
On Apr 20, 12:38 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message
>news:11**********************@y5g2000hsa.googlegr oups.com...
On Apr 19, 2:49 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message
>news:11**********************@y80g2000hsf.googleg roups.com...
On Mar 26, 7:52 pm, lesperan...@natpro.com wrote:
assuming that this is your data
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
and assuming
a) no. ordered is number of books you've received from
the
supplier
b) you send out one copy of a book title to one
individual
you can create a query (qryGivenOut)
that counts the number of books sent out to individuals
SELECT bookTitle, COUNT(bookTitle) as numberGivenOut
FROM tblIndividual GROUP BY bookTitle
and create a 2nd query (qryInStock) to give you total
books
in
stock
SELECT bookTitle, SUM(noOrdered) as numberInStock
FROM tblBook GROUP BY bookTitle
and create a 3rd query to give you the number of remaining
books
SELECT qryInStock.bookTitle,
numberInStock,
numberGivenOut,
numberInStock - numberGivenOut as
numberRemaining
FROM qryInStock left join
qryGivenOut on qryInStock.bookTitle =
qryGivenOut.bookTitle
On Mar 26, 3:37 pm, "Jo" <j...@NOSPAM.stallan.plus.com>
wrote:
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" <j...@NOSPAM.stallan.plus.comwrote in message
news:46**********************@ptn-nntp-reader02.plus.net...
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- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -
can you post the query you are using and the error you are
getting
Hi there,
I'm having a real problem replicating the error! I'm getting
different
errors each time. I think I am going about this the totally
wrong
way.
I
have set up a query using the 'Books Sent Out' Table. This
works
fine
and
produces a list of all the books that have been sent out so
far.
Then from that query I am trying to set up a COUNT function
as
follows:
COUNT(Sleepyhead (Albanian)) but am now getting the error:
'Can't have aggregate function in WHERE clause ([Books Sent
Out
Query].[Book
Title & Language]=Count(Sleepyhead ("Albanian"))).'
I am totally baffled as I can't reporoduce the
operator/operand
error.
I
have a feeling that I may be writing the function in the
wrong
place,
but
to
be honest...I really haven't a clue!
Regards,
Jo- Hide quoted text -
- Show quoted text -
is Sleepyhead a function ? what does it do ? can you post it ?
what does your first query look like ? ie. SELECT ... FROM ...
WHERE ...
and your second query ?
SELECT ..
FROM [Books Sent Out Query]
WHERE
([Books Sent Out Query].[Book Title &
Language]=Count(Sleepyhead
("Albanian")))
what do you get with the following
SELECT COUNT(*)
FROM [Books Sent Out Query]
WHERE [Book Title & Language] = Sleepyhead ("Albanian")
Sleepyhead is a book title....my table of book title & language
is
set
up
with titles such as:
Sleepyhead (Albanian)
Sleepyhead (Gujurati)
Heads, Shoulders, Knees & Toes (Albanian)
etc
I'm so sorry, but I do not understand the SELECT bit - is that a
function
in
itself. My knowledge of query functions and syntax is so
limited
that
I
am
really struggling to understand this. I'm not even managing to
set
up
the
following in the right way:
SELECT COUNT(*)
FROM [Books Sent Out Query]
WHERE [Book Title & Language] = Sleepyhead ("Albanian")
I feel I may be wasting you time and perhaps I should go back to
doing
this
all manually!
Is there a way I could post (or email you) with a copy of the
database
(it's
no more than 300kb) - not with real data of course, but with a
couple
of
made-up entries?- Hide quoted text -
- Show quoted text -
create a new query
select the query [books sent out query] from the list of possible
queries
click the 'totals' icon on the toolbar (looks like the letter E
or
Greek epsilon)
drag the [book title & language] field to the list of 'fields'
you
want to see
add Sleepyhead("Albanian") to the 'criteria' of this field
add count to the 'total' field
I'm using access97, so if you using a different version, things
might
be slightly different
So sorry, but I'm getting an error again. I'm doing the above
exactly.
Previously it didn't like the () brackets in the Book Title &
Language
eg
Sleepyhead (Albanian)....Access thought it was an operand. So I
have
changed the Titles to read eg Sleepyhead - Albanian. This, I have
done
in
all tables which are related. However, now I am now getting the
error:
'Data type mismatch in criteria expression'. I cannot see any
mismatch
at
all.- Hide quoted text -
- Show quoted text -
if the title of the book is
Sleepyhead("Albanian")
you'll need to put single quote (ie. ') around the title & language,
ie
'Sleepyhead("Albanian")'
'sleepyhead - albanian'
the book title using both brackets and double quotes is causing you
lots of grief unless you add the single quotes
because ms-access will think that sleepyhead(...) is calling the
function sleepyhead passing it an argument called albanian
and without the single quotes, sleepyhead - albanian means subtract
albanian from sleepyhead
the real solution is to break the 'title' and the 'language' into 2
distinct fields
Access was putting the double quotes in - not me! I have tried
putting
single quotes in the 'criteria' of the field and it still doesn't like
it.
eg. 'Sleepyhead - Albanian' or 'Head, Shoulders, Knees & Toes -
French'.
Think I might start again using 2 separate fields as you suggest. I
set
it
all up like this in the first place because I thought that 2 separate
fields
would cause confusion as I have the same ...
read more - Hide quoted text -
- Show quoted text -
yes, if you have query and the field is a text field, and you type ABC
as the criteria, access will automatically add the double quotes...
it's trying to create a valid query based on the information it
knows... but you can type 'ABC' and it should accept it and should
execute properly - what error does it give you ?
I have now attempted to set up the tables with separate fields for Book
Title and Language. I have used the 'books sent out' query to set up a
count query with, for example, "Sleepyhead" in the Book Title field and
"Albanian" in the Language field. I have selected Totals and added
Count
to the total field. But I am still getting the error as before.
'Data type mismatch in criteria expression'
I really am totally stumped.

if you open the query, and remove one field at a time (just don't save
the query) and rerun the quere
which field is causing the 'mismatch' ?

is there a criteria for this field ?

how is the field defined in the table ? text ? date ? number ?

---------------------------------------------------------------------------*---------

I have taken out each field in turn and am still getting the same error.

My query (without anything taken out) look like the following:

With the 'Books Out Query' selected (this gave me a list of all books sent
out)

Field: Book Title
Language

Table: Books Out
Books Out

Total: Count
Count

Sort:

Show:


Criteria: " Head, Shoulders, Knees and Toes"
"French"
the problem is that you've put the criteria 'head, shoulders...' as
the criteria of a count field, a count field returns a number not a
textual string.. thus the 'data mismatch'

what you need to do is add 2 more fields (book title, language) with
the total = 'Where' and move you criteria to those 2 fields

book title language book title language
count count where where

"head,...' french

or

book title language nbrCopies:1
where where count

"head,...' french

Have done the above and am now getting the error 'Query must have at least
one destination field'.
(Sorry to be such hard work! I really appreciate all your help).

May 10 '07 #32

P: n/a
Jo

<le*********@natpro.comwrote in message
news:11**********************@l77g2000hsb.googlegr oups.com...
On May 9, 4:46 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message

news:11**********************@o5g2000hsb.googlegro ups.com...
On May 6, 3:36 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message
news:11**********************@o40g2000prh.googlegr oups.com...
On Apr 24, 2:06 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message
>news:11**********************@b58g2000hsg.googleg roups.com...
On Apr 23, 12:20 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message
>news:11**********************@n76g2000hsh.googleg roups.com...
On Apr 20, 12:38 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message
>news:11**********************@y5g2000hsa.googlegr oups.com...
On Apr 19, 2:49 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message
>news:11**********************@y80g2000hsf.googleg roups.com...
On Mar 26, 7:52 pm, lesperan...@natpro.com wrote:
assuming that this is your data
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
and assuming
a) no. ordered is number of books you've received from
the
supplier
b) you send out one copy of a book title to one
individual
you can create a query (qryGivenOut)
that counts the number of books sent out to individuals
SELECT bookTitle, COUNT(bookTitle) as numberGivenOut
FROM tblIndividual GROUP BY bookTitle
and create a 2nd query (qryInStock) to give you total
books
in
stock
SELECT bookTitle, SUM(noOrdered) as numberInStock
FROM tblBook GROUP BY bookTitle
and create a 3rd query to give you the number of remaining
books
SELECT qryInStock.bookTitle,
numberInStock,
numberGivenOut,
numberInStock - numberGivenOut as
numberRemaining
FROM qryInStock left join
qryGivenOut on qryInStock.bookTitle =
qryGivenOut.bookTitle
On Mar 26, 3:37 pm, "Jo" <j...@NOSPAM.stallan.plus.com>
wrote:
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" <j...@NOSPAM.stallan.plus.comwrote in message
news:46**********************@ptn-nntp-reader02.plus.net...
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- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -
can you post the query you are using and the error you are
getting
Hi there,
I'm having a real problem replicating the error! I'm getting
different
errors each time. I think I am going about this the totally
wrong
way.
I
have set up a query using the 'Books Sent Out' Table. This
works
fine
and
produces a list of all the books that have been sent out so
far.
Then from that query I am trying to set up a COUNT function
as
follows:
COUNT(Sleepyhead (Albanian)) but am now getting the error:
'Can't have aggregate function in WHERE clause ([Books Sent
Out
Query].[Book
Title & Language]=Count(Sleepyhead ("Albanian"))).'
I am totally baffled as I can't reporoduce the
operator/operand
error.
I
have a feeling that I may be writing the function in the
wrong
place,
but
to
be honest...I really haven't a clue!
Regards,
Jo- Hide quoted text -
- Show quoted text -
is Sleepyhead a function ? what does it do ? can you post it ?
what does your first query look like ? ie. SELECT ... FROM ...
WHERE ...
and your second query ?
SELECT ..
FROM [Books Sent Out Query]
WHERE
([Books Sent Out Query].[Book Title &
Language]=Count(Sleepyhead
("Albanian")))
what do you get with the following
SELECT COUNT(*)
FROM [Books Sent Out Query]
WHERE [Book Title & Language] = Sleepyhead ("Albanian")
Sleepyhead is a book title....my table of book title & language
is
set
up
with titles such as:
Sleepyhead (Albanian)
Sleepyhead (Gujurati)
Heads, Shoulders, Knees & Toes (Albanian)
etc
I'm so sorry, but I do not understand the SELECT bit - is that a
function
in
itself. My knowledge of query functions and syntax is so
limited
that
I
am
really struggling to understand this. I'm not even managing to
set
up
the
following in the right way:
SELECT COUNT(*)
FROM [Books Sent Out Query]
WHERE [Book Title & Language] = Sleepyhead ("Albanian")
I feel I may be wasting you time and perhaps I should go back to
doing
this
all manually!
Is there a way I could post (or email you) with a copy of the
database
(it's
no more than 300kb) - not with real data of course, but with a
couple
of
made-up entries?- Hide quoted text -
- Show quoted text -
create a new query
select the query [books sent out query] from the list of possible
queries
click the 'totals' icon on the toolbar (looks like the letter E
or
Greek epsilon)
drag the [book title & language] field to the list of 'fields'
you
want to see
add Sleepyhead("Albanian") to the 'criteria' of this field
add count to the 'total' field
I'm using access97, so if you using a different version, things
might
be slightly different
So sorry, but I'm getting an error again. I'm doing the above
exactly.
Previously it didn't like the () brackets in the Book Title &
Language
eg
Sleepyhead (Albanian)....Access thought it was an operand. So I
have
changed the Titles to read eg Sleepyhead - Albanian. This, I have
done
in
all tables which are related. However, now I am now getting the
error:
'Data type mismatch in criteria expression'. I cannot see any
mismatch
at
all.- Hide quoted text -
- Show quoted text -
if the title of the book is
Sleepyhead("Albanian")
you'll need to put single quote (ie. ') around the title & language,
ie
'Sleepyhead("Albanian")'
'sleepyhead - albanian'
the book title using both brackets and double quotes is causing you
lots of grief unless you add the single quotes
because ms-access will think that sleepyhead(...) is calling the
function sleepyhead passing it an argument called albanian
and without the single quotes, sleepyhead - albanian means subtract
albanian from sleepyhead
the real solution is to break the 'title' and the 'language' into 2
distinct fields
Access was putting the double quotes in - not me! I have tried
putting
single quotes in the 'criteria' of the field and it still doesn't like
it.
eg. 'Sleepyhead - Albanian' or 'Head, Shoulders, Knees & Toes -
French'.
Think I might start again using 2 separate fields as you suggest. I
set
it
all up like this in the first place because I thought that 2 separate
fields
would cause confusion as I have the same ...
read more - Hide quoted text -
- Show quoted text -
yes, if you have query and the field is a text field, and you type ABC
as the criteria, access will automatically add the double quotes...
it's trying to create a valid query based on the information it
knows... but you can type 'ABC' and it should accept it and should
execute properly - what error does it give you ?
I have now attempted to set up the tables with separate fields for Book
Title and Language. I have used the 'books sent out' query to set up a
count query with, for example, "Sleepyhead" in the Book Title field and
"Albanian" in the Language field. I have selected Totals and added
Count
to the total field. But I am still getting the error as before.
'Data type mismatch in criteria expression'
I really am totally stumped.

if you open the query, and remove one field at a time (just don't save
the query) and rerun the quere
which field is causing the 'mismatch' ?

is there a criteria for this field ?

how is the field defined in the table ? text ? date ? number ?

---------------------------------------------------------------------------*---------

I have taken out each field in turn and am still getting the same error.

My query (without anything taken out) look like the following:

With the 'Books Out Query' selected (this gave me a list of all books sent
out)

Field: Book Title
Language

Table: Books Out
Books Out

Total: Count
Count

Sort:

Show:


Criteria: " Head, Shoulders, Knees and Toes"
"French"
the problem is that you've put the criteria 'head, shoulders...' as
the criteria of a count field, a count field returns a number not a
textual string.. thus the 'data mismatch'

what you need to do is add 2 more fields (book title, language) with
the total = 'Where' and move you criteria to those 2 fields

book title language book title language
count count where where

"head,...' french

or

book title language nbrCopies:1
where where count

"head,...' french
I have finally got the count query to work! I am about to attempt the
second query....don't go away!
Thanks for all your help so far.
Jo
May 14 '07 #33

P: n/a
Jo

<le*********@natpro.comwrote in message
news:11**********************@b58g2000hsg.googlegr oups.com...
On Apr 23, 12:20 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
><lesperan...@natpro.comwrote in message

news:11**********************@n76g2000hsh.googleg roups.com...


On Apr 20, 12:38 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message
>>news:11**********************@y5g2000hsa.googleg roups.com...
On Apr 19, 2:49 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message
>>news:11**********************@y80g2000hsf.google groups.com...
On Mar 26, 7:52 pm, lesperan...@natpro.com wrote:
assuming that this is your data
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
>and assuming
a) no. ordered is number of books you've received from the
supplier
b) you send out one copy of a book title to one individual
>you can create a query (qryGivenOut)
that counts the number of books sent out to individuals
SELECT bookTitle, COUNT(bookTitle) as numberGivenOut
FROM tblIndividual GROUP BY bookTitle
>and create a 2nd query (qryInStock) to give you total books in
stock
SELECT bookTitle, SUM(noOrdered) as numberInStock
FROM tblBook GROUP BY bookTitle
>and create a 3rd query to give you the number of remaining books
SELECT qryInStock.bookTitle,
numberInStock,
numberGivenOut,
numberInStock - numberGivenOut as
numberRemaining
FROM qryInStock left join
qryGivenOut on qryInStock.bookTitle =
qryGivenOut.bookTitle
>On Mar 26, 3:37 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
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" <j...@NOSPAM.stallan.plus.comwrote in message
>news:46**********************@ptn-nntp-reader02.plus.net...
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- Hide quoted text -
- Show quoted text -- Hide quoted text -
>- Show quoted text -
can you post the query you are using and the error you are
getting
>Hi there,
I'm having a real problem replicating the error! I'm getting
different
errors each time. I think I am going about this the totally wrong
way.
I
have set up a query using the 'Books Sent Out' Table. This works
fine
and
produces a list of all the books that have been sent out so far.
Then from that query I am trying to set up a COUNT function as
follows:
COUNT(Sleepyhead (Albanian)) but am now getting the error:
>'Can't have aggregate function in WHERE clause ([Books Sent Out
Query].[Book
Title & Language]=Count(Sleepyhead ("Albanian"))).'
>I am totally baffled as I can't reporoduce the operator/operand
error.
I
have a feeling that I may be writing the function in the wrong
place,
but
to
be honest...I really haven't a clue!
Regards,
Jo- Hide quoted text -
>- Show quoted text -
is Sleepyhead a function ? what does it do ? can you post it ?
what does your first query look like ? ie. SELECT ... FROM ...
WHERE ...
and your second query ?
SELECT ..
FROM [Books Sent Out Query]
WHERE
([Books Sent Out Query].[Book Title & Language]=Count(Sleepyhead
("Albanian")))
what do you get with the following
SELECT COUNT(*)
FROM [Books Sent Out Query]
WHERE [Book Title & Language] = Sleepyhead ("Albanian")
>Sleepyhead is a book title....my table of book title & language is set
up
with titles such as:
Sleepyhead (Albanian)
Sleepyhead (Gujurati)
Heads, Shoulders, Knees & Toes (Albanian)
etc
I'm so sorry, but I do not understand the SELECT bit - is that a
function
in
itself. My knowledge of query functions and syntax is so limited that
I
am
really struggling to understand this. I'm not even managing to set up
the
following in the right way:
>SELECT COUNT(*)
FROM [Books Sent Out Query]
WHERE [Book Title & Language] = Sleepyhead ("Albanian")
>I feel I may be wasting you time and perhaps I should go back to doing
this
all manually!
Is there a way I could post (or email you) with a copy of the database
(it's
no more than 300kb) - not with real data of course, but with a couple
of
made-up entries?- Hide quoted text -
>- Show quoted text -
create a new query
select the query [books sent out query] from the list of possible
queries
click the 'totals' icon on the toolbar (looks like the letter E or
Greek epsilon)
drag the [book title & language] field to the list of 'fields' you
want to see
add Sleepyhead("Albanian") to the 'criteria' of this field
add count to the 'total' field
I'm using access97, so if you using a different version, things might
be slightly different

So sorry, but I'm getting an error again. I'm doing the above exactly.
Previously it didn't like the () brackets in the Book Title & Language eg
Sleepyhead (Albanian)....Access thought it was an operand. So I have
changed the Titles to read eg Sleepyhead - Albanian. This, I have done
in
all tables which are related. However, now I am now getting the error:
'Data type mismatch in criteria expression'. I cannot see any mismatch
at
all.- Hide quoted text -

- Show quoted text -

if the title of the book is

Sleepyhead("Albanian")

you'll need to put single quote (ie. ') around the title & language,
ie

'Sleepyhead("Albanian")'
'sleepyhead - albanian'

the book title using both brackets and double quotes is causing you
lots of grief unless you add the single quotes
because ms-access will think that sleepyhead(...) is calling the
function sleepyhead passing it an argument called albanian

and without the single quotes, sleepyhead - albanian means subtract
albanian from sleepyhead

the real solution is to break the 'title' and the 'language' into 2
distinct fields
16th May
I have now managed to sort out the first 2 queries and have attempted the
third. I have 1) a count of each title given out (works fine) 2) the sum
of number ordered (works fine) and 3) a calculation of remaining stock - but
the query only returns a figure for those titles which have been given out.
If a certain item is in stock and none have been given out I still need the
query to return the total figure so I have a complete list of titles in
stock and the number of each.
Most importantly I need the remaining stock to show a zero figure so I know
when to order a particular title. The query currently omits any stock which
has been depleted.
May 16 '07 #34

P: n/a
On May 16, 11:49 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message

news:11**********************@b58g2000hsg.googlegr oups.com...On Apr 23, 12:20 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message
>news:11**********************@n76g2000hsh.googleg roups.com...
On Apr 20, 12:38 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message
>news:11**********************@y5g2000hsa.googlegr oups.com...
On Apr 19, 2:49 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message
>news:11**********************@y80g2000hsf.googleg roups.com...
On Mar 26, 7:52 pm, lesperan...@natpro.com wrote:
assuming that this is your data
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
and assuming
a) no. ordered is number of books you've received from the
supplier
b) you send out one copy of a book title to one individual
you can create a query (qryGivenOut)
that counts the number of books sent out to individuals
SELECT bookTitle, COUNT(bookTitle) as numberGivenOut
FROM tblIndividual GROUP BY bookTitle
and create a 2nd query (qryInStock) to give you total books in
stock
SELECT bookTitle, SUM(noOrdered) as numberInStock
FROM tblBook GROUP BY bookTitle
and create a 3rd query to give you the number of remaining books
SELECT qryInStock.bookTitle,
numberInStock,
numberGivenOut,
numberInStock - numberGivenOut as
numberRemaining
FROM qryInStock left join
qryGivenOut on qryInStock.bookTitle =
qryGivenOut.bookTitle
On Mar 26, 3:37 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
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" <j...@NOSPAM.stallan.plus.comwrote in message
news:46**********************@ptn-nntp-reader02.plus.net...
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- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -
can you post the query you are using and the error you are
getting
Hi there,
I'm having a real problem replicating the error! I'm getting
different
errors each time. I think I am going about this the totally wrong
way.
I
have set up a query using the 'Books Sent Out' Table. This works
fine
and
produces a list of all the books that have been sent out so far.
Then from that query I am trying to set up a COUNT function as
follows:
COUNT(Sleepyhead (Albanian)) but am now getting the error:
'Can't have aggregate function in WHERE clause ([Books Sent Out
Query].[Book
Title & Language]=Count(Sleepyhead ("Albanian"))).'
I am totally baffled as I can't reporoduce the operator/operand
error.
I
have a feeling that I may be writing the function in the wrong
place,
but
to
be honest...I really haven't a clue!
Regards,
Jo- Hide quoted text -
- Show quoted text -
is Sleepyhead a function ? what does it do ? can you post it ?
what does your first query look like ? ie. SELECT ... FROM ...
WHERE ...
and your second query ?
SELECT ..
FROM [Books Sent Out Query]
WHERE
([Books Sent Out Query].[Book Title & Language]=Count(Sleepyhead
("Albanian")))
what do you get with the following
SELECT COUNT(*)
FROM [Books Sent Out Query]
WHERE [Book Title & Language] = Sleepyhead ("Albanian")
Sleepyhead is a book title....my table of book title & language is set
up
with titles such as:
Sleepyhead (Albanian)
Sleepyhead (Gujurati)
Heads, Shoulders, Knees & Toes (Albanian)
etc
I'm so sorry, but I do not understand the SELECT bit - is that a
function
in
itself. My knowledge of query functions and syntax is so limited that
I
am
really struggling to understand this. I'm not even managing to set up
the
following in the right way:
SELECT COUNT(*)
FROM [Books Sent Out Query]
WHERE [Book Title & Language] = Sleepyhead ("Albanian")
I feel I may be wasting you time and perhaps I should go back to doing
this
all manually!
Is there a way I could post (or email you) with a copy of the database
(it's
no more than 300kb) - not with real data of course, but with a couple
of
made-up entries?- Hide quoted text -
- Show quoted text -
create a new query
select the query [books sent out query] from the list of possible
queries
click the 'totals' icon on the toolbar (looks like the letter E or
Greek epsilon)
drag the [book title & language] field to the list of 'fields' you
want to see
add Sleepyhead("Albanian") to the 'criteria' of this field
add count to the 'total' field
I'm using access97, so if you using a different version, things might
be slightly different
So sorry, but I'm getting an error again. I'm doing the above exactly.
Previously it didn't like the () brackets in the Book Title & Language eg
Sleepyhead (Albanian)....Access thought it was an operand. So I have
changed the Titles to read eg Sleepyhead - Albanian. This, I have done
in
all tables which are related. However, now I am now getting the error:
'Data type mismatch in criteria expression'. I cannot see any mismatch
at
all.- Hide quoted text -
- Show quoted text -
if the title of the book is
Sleepyhead("Albanian")
you'll need to put single quote (ie. ') around the title & language,
ie
'Sleepyhead("Albanian")'
'sleepyhead - albanian'
the book title using both brackets and double quotes is causing you
lots of grief unless you add the single quotes
because ms-access will think that sleepyhead(...) is calling the
function sleepyhead passing it an argument called albanian
and without the single quotes, sleepyhead - albanian means subtract
albanian from sleepyhead
the real solution is to break the 'title' and the 'language' into 2
distinct fields

16th May
I have now managed to sort out the first 2 queries and have attempted the
third. I have 1) a count of each title given out (works fine) 2) the sum
of number ordered (works fine) and 3) a calculation of remaining stock - but
the query only returns a figure for those titles which have been given out.
If a certain item is in stock and none have been given out I still need the
query to return the total figure so I have a complete list of titles in
stock and the number of each.
Most importantly I need the remaining stock to show a zero figure so I know
when to order a particular title. The query currently omits any stock which
has been depleted.
edit query 3, double click on the 'line' that joins the 2 queries, and
indicate that you want to see all records in query 2
this will show all books in inventory not just those that been given
out
May 17 '07 #35

P: n/a
Jo

<le*********@natpro.comwrote in message
news:11*********************@l77g2000hsb.googlegro ups.com...
On May 16, 11:49 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
><lesperan...@natpro.comwrote in message

news:11**********************@b58g2000hsg.googleg roups.com...On Apr 23,
12:20 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
><lesperan...@natpro.comwrote in message
>>news:11**********************@n76g2000hsh.google groups.com...
On Apr 20, 12:38 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message
>>news:11**********************@y5g2000hsa.googleg roups.com...
On Apr 19, 2:49 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message
>>news:11**********************@y80g2000hsf.google groups.com...
On Mar 26, 7:52 pm, lesperan...@natpro.com wrote:
assuming that this is your data
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
>and assuming
a) no. ordered is number of books you've received from the
supplier
b) you send out one copy of a book title to one individual
>you can create a query (qryGivenOut)
that counts the number of books sent out to individuals
SELECT bookTitle, COUNT(bookTitle) as numberGivenOut
FROM tblIndividual GROUP BY bookTitle
>and create a 2nd query (qryInStock) to give you total books
in
stock
SELECT bookTitle, SUM(noOrdered) as numberInStock
FROM tblBook GROUP BY bookTitle
>and create a 3rd query to give you the number of remaining
books
SELECT qryInStock.bookTitle,
numberInStock,
numberGivenOut,
numberInStock - numberGivenOut as
numberRemaining
FROM qryInStock left join
qryGivenOut on qryInStock.bookTitle =
qryGivenOut.bookTitle
>On Mar 26, 3:37 pm, "Jo" <j...@NOSPAM.stallan.plus.com>
wrote:
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" <j...@NOSPAM.stallan.plus.comwrote in message
>news:46**********************@ptn-nntp-reader02.plus.net...
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- Hide quoted text -
- Show quoted text -- Hide quoted text -
>- Show quoted text -
can you post the query you are using and the error you are
getting
>Hi there,
I'm having a real problem replicating the error! I'm getting
different
errors each time. I think I am going about this the totally
wrong
way.
I
have set up a query using the 'Books Sent Out' Table. This
works
fine
and
produces a list of all the books that have been sent out so far.
Then from that query I am trying to set up a COUNT function as
follows:
COUNT(Sleepyhead (Albanian)) but am now getting the error:
>'Can't have aggregate function in WHERE clause ([Books Sent Out
Query].[Book
Title & Language]=Count(Sleepyhead ("Albanian"))).'
>I am totally baffled as I can't reporoduce the operator/operand
error.
I
have a feeling that I may be writing the function in the wrong
place,
but
to
be honest...I really haven't a clue!
Regards,
Jo- Hide quoted text -
>- Show quoted text -
is Sleepyhead a function ? what does it do ? can you post it ?
what does your first query look like ? ie. SELECT ... FROM ...
WHERE ...
and your second query ?
SELECT ..
FROM [Books Sent Out Query]
WHERE
([Books Sent Out Query].[Book Title & Language]=Count(Sleepyhead
("Albanian")))
what do you get with the following
SELECT COUNT(*)
FROM [Books Sent Out Query]
WHERE [Book Title & Language] = Sleepyhead ("Albanian")
>Sleepyhead is a book title....my table of book title & language is
set
up
with titles such as:
Sleepyhead (Albanian)
Sleepyhead (Gujurati)
Heads, Shoulders, Knees & Toes (Albanian)
etc
I'm so sorry, but I do not understand the SELECT bit - is that a
function
in
itself. My knowledge of query functions and syntax is so limited
that
I
am
really struggling to understand this. I'm not even managing to set
up
the
following in the right way:
>SELECT COUNT(*)
FROM [Books Sent Out Query]
WHERE [Book Title & Language] = Sleepyhead ("Albanian")
>I feel I may be wasting you time and perhaps I should go back to
doing
this
all manually!
Is there a way I could post (or email you) with a copy of the
database
(it's
no more than 300kb) - not with real data of course, but with a
couple
of
made-up entries?- Hide quoted text -
>- Show quoted text -
create a new query
select the query [books sent out query] from the list of possible
queries
click the 'totals' icon on the toolbar (looks like the letter E or
Greek epsilon)
drag the [book title & language] field to the list of 'fields' you
want to see
add Sleepyhead("Albanian") to the 'criteria' of this field
add count to the 'total' field
I'm using access97, so if you using a different version, things
might
be slightly different
>So sorry, but I'm getting an error again. I'm doing the above
exactly.
Previously it didn't like the () brackets in the Book Title & Language
eg
Sleepyhead (Albanian)....Access thought it was an operand. So I have
changed the Titles to read eg Sleepyhead - Albanian. This, I have
done
in
all tables which are related. However, now I am now getting the
error:
'Data type mismatch in criteria expression'. I cannot see any
mismatch
at
all.- Hide quoted text -
>- Show quoted text -
if the title of the book is
Sleepyhead("Albanian")
you'll need to put single quote (ie. ') around the title & language,
ie
'Sleepyhead("Albanian")'
'sleepyhead - albanian'
the book title using both brackets and double quotes is causing you
lots of grief unless you add the single quotes
because ms-access will think that sleepyhead(...) is calling the
function sleepyhead passing it an argument called albanian
and without the single quotes, sleepyhead - albanian means subtract
albanian from sleepyhead
the real solution is to break the 'title' and the 'language' into 2
distinct fields

16th May
I have now managed to sort out the first 2 queries and have attempted the
third. I have 1) a count of each title given out (works fine) 2) the
sum
of number ordered (works fine) and 3) a calculation of remaining stock -
but
the query only returns a figure for those titles which have been given
out.
If a certain item is in stock and none have been given out I still need
the
query to return the total figure so I have a complete list of titles in
stock and the number of each.
Most importantly I need the remaining stock to show a zero figure so I
know
when to order a particular title. The query currently omits any stock
which
has been depleted.

edit query 3, double click on the 'line' that joins the 2 queries, and
indicate that you want to see all records in query 2
this will show all books in inventory not just those that been given
out

On checking, it appears that I have already done this (I think). Are you
referriung to option 2 in the Join Properties box?
May 17 '07 #36

P: n/a
Jo

<le*********@natpro.comwrote in message
news:11*********************@p77g2000hsh.googlegro ups.com...
assuming that this is your data
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
and assuming
a) no. ordered is number of books you've received from the supplier
b) you send out one copy of a book title to one individual

you can create a query (qryGivenOut)
that counts the number of books sent out to individuals
SELECT bookTitle, COUNT(bookTitle) as numberGivenOut
FROM tblIndividual GROUP BY bookTitle

and create a 2nd query (qryInStock) to give you total books in stock
SELECT bookTitle, SUM(noOrdered) as numberInStock
FROM tblBook GROUP BY bookTitle

and create a 3rd query to give you the number of remaining books
SELECT qryInStock.bookTitle,
numberInStock,
numberGivenOut,
numberInStock - numberGivenOut as numberRemaining
FROM qryInStock left join
qryGivenOut on qryInStock.bookTitle =
qryGivenOut.bookTitle
On Mar 26, 3:37 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
>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" <j...@NOSPAM.stallan.plus.comwrote in message

news:46**********************@ptn-nntp-reader02.plus.net...
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- Hide quoted text -

- Show quoted text -

The query now lists all items of stock (and includes 0 when all of a
particular item has been used up), but the 'Stock Remaining' field only
holds values for those items where a title has been given out. In cases
where a particular title hasn't been given out the field is blank.
The only way I can see how many of these titles are left (ie the total
ordered as none have been given out to date) is to allow the 'SumOfNumber
Ordered' to be viewed.
I am almost there...
May 18 '07 #37

P: n/a
Jo

"Jo" <jo@NOSPAM.stallan.plus.comwrote in message
news:46**********************@ptn-nntp-reader02.plus.net...
>
<le*********@natpro.comwrote in message
news:11*********************@p77g2000hsh.googlegro ups.com...
>assuming that this is your data
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
and assuming
a) no. ordered is number of books you've received from the supplier
b) you send out one copy of a book title to one individual

you can create a query (qryGivenOut)
that counts the number of books sent out to individuals
SELECT bookTitle, COUNT(bookTitle) as numberGivenOut
FROM tblIndividual GROUP BY bookTitle

and create a 2nd query (qryInStock) to give you total books in stock
SELECT bookTitle, SUM(noOrdered) as numberInStock
FROM tblBook GROUP BY bookTitle

and create a 3rd query to give you the number of remaining books
SELECT qryInStock.bookTitle,
numberInStock,
numberGivenOut,
numberInStock - numberGivenOut as numberRemaining
FROM qryInStock left join
qryGivenOut on qryInStock.bookTitle =
qryGivenOut.bookTitle
On Mar 26, 3:37 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
>>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" <j...@NOSPAM.stallan.plus.comwrote in message

news:46**********************@ptn-nntp-reader02.plus.net...

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- Hide quoted text -

- Show quoted text -


The query now lists all items of stock (and includes 0 when all of a
particular item has been used up), but the 'Stock Remaining' field only
holds values for those items where a title has been given out. In cases
where a particular title hasn't been given out the field is blank.
The only way I can see how many of these titles are left (ie the total
ordered as none have been given out to date) is to allow the 'SumOfNumber
Ordered' to be viewed.
I am almost there...
To elaborate, my query results look like this example(I don't know if, when
I post this, it will stay put):

Book Title Language Stock Remaining SumOfNumber Ordered

Sleepyhead Albanian 13 15
Zoo Albanian 13 15
Sleepyhead Arabic 2
Zoo Bengali 13
15

etc

As no Sleepyhead has been given out in Arabic, the stock remaining equals
the SumOfNumber Ordered yet this isn't showing in the Stock Remaining field.
Where other items have been given out, the remaining stock is shown.
>

May 24 '07 #38

P: n/a
Jo

"Jo" <jo@NOSPAM.stallan.plus.comwrote in message
news:46**********************@ptn-nntp-reader02.plus.net...
>
"Jo" <jo@NOSPAM.stallan.plus.comwrote in message
news:46**********************@ptn-nntp-reader02.plus.net...
>>
<le*********@natpro.comwrote in message
news:11*********************@p77g2000hsh.googlegr oups.com...
>>assuming that this is your data
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
and assuming
a) no. ordered is number of books you've received from the supplier
b) you send out one copy of a book title to one individual

you can create a query (qryGivenOut)
that counts the number of books sent out to individuals
SELECT bookTitle, COUNT(bookTitle) as numberGivenOut
FROM tblIndividual GROUP BY bookTitle

and create a 2nd query (qryInStock) to give you total books in stock
SELECT bookTitle, SUM(noOrdered) as numberInStock
FROM tblBook GROUP BY bookTitle

and create a 3rd query to give you the number of remaining books
SELECT qryInStock.bookTitle,
numberInStock,
numberGivenOut,
numberInStock - numberGivenOut as numberRemaining
FROM qryInStock left join
qryGivenOut on qryInStock.bookTitle =
qryGivenOut.bookTitle
On Mar 26, 3:37 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
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" <j...@NOSPAM.stallan.plus.comwrote in message

news:46**********************@ptn-nntp-reader02.plus.net...

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- Hide quoted text -

- Show quoted text -


The query now lists all items of stock (and includes 0 when all of a
particular item has been used up), but the 'Stock Remaining' field only
holds values for those items where a title has been given out. In cases
where a particular title hasn't been given out the field is blank.
The only way I can see how many of these titles are left (ie the total
ordered as none have been given out to date) is to allow the 'SumOfNumber
Ordered' to be viewed.
I am almost there...
To elaborate, my query results look like this example(I don't know if,
when I post this, it will stay put):

Book Title Language Stock Remaining SumOfNumber
Ordered

Sleepyhead Albanian 13 15
Zoo Albanian 13
15
Sleepyhead Arabic
2
Zoo Bengali 13 15

etc

As no Sleepyhead has been given out in Arabic, the stock remaining equals
the SumOfNumber Ordered yet this isn't showing in the Stock Remaining
field.
Where other items have been given out, the remaining stock is shown.
>>

NB The last 15 (under Zoo) has moved during posting. It should be at the
bottom of the SumOfNumber Ordered column.

May 24 '07 #39

P: n/a
On May 24, 3:27 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
"Jo" <j...@NOSPAM.stallan.plus.comwrote in message

news:46**********************@ptn-nntp-reader02.plus.net...


<lesperan...@natpro.comwrote in message
news:11*********************@p77g2000hsh.googlegro ups.com...
assuming that this is your data
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
and assuming
a) no. ordered is number of books you've received from the supplier
b) you send out one copy of a book title to one individual
you can create a query (qryGivenOut)
that counts the number of books sent out to individuals
SELECT bookTitle, COUNT(bookTitle) as numberGivenOut
FROM tblIndividual GROUP BY bookTitle
and create a 2nd query (qryInStock) to give you total books in stock
SELECT bookTitle, SUM(noOrdered) as numberInStock
FROM tblBook GROUP BY bookTitle
and create a 3rd query to give you the number of remaining books
SELECT qryInStock.bookTitle,
numberInStock,
numberGivenOut,
numberInStock - numberGivenOut as numberRemaining
FROM qryInStock left join
qryGivenOut on qryInStock.bookTitle =
qryGivenOut.bookTitle
On Mar 26, 3:37 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
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" <j...@NOSPAM.stallan.plus.comwrote in message
>>news:46**********************@ptn-nntp-reader02.plus.net...
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- Hide quoted text -
>- Show quoted text -
The query now lists all items of stock (and includes 0 when all of a
particular item has been used up), but the 'Stock Remaining' field only
holds values for those items where a title has been given out. In cases
where a particular title hasn't been given out the field is blank.
The only way I can see how many of these titles are left (ie the total
ordered as none have been given out to date) is to allow the 'SumOfNumber
Ordered' to be viewed.
I am almost there...

To elaborate, my query results look like this example(I don't know if, when
I post this, it will stay put):

Book Title Language Stock Remaining SumOfNumber Ordered

Sleepyhead Albanian 13 15
Zoo Albanian 13 15
Sleepyhead Arabic 2
Zoo Bengali 13
15

etc

As no Sleepyhead has been given out in Arabic, the stock remaining equals
the SumOfNumber Ordered yet this isn't showing in the Stock Remaining field.
Where other items have been given out, the remaining stock is shown.

- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -
again, without seeing the query, it's hard to know what the problem is

can you open the query in 'sql view' and post it here ?

May 25 '07 #40

P: n/a
Jo

<le*********@natpro.comwrote in message
news:11**********************@q66g2000hsg.googlegr oups.com...
On May 24, 3:27 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
>"Jo" <j...@NOSPAM.stallan.plus.comwrote in message

news:46**********************@ptn-nntp-reader02.plus.net...


<lesperan...@natpro.comwrote in message
news:11*********************@p77g2000hsh.googlegr oups.com...
assuming that this is your data
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
>and assuming
a) no. ordered is number of books you've received from the supplier
b) you send out one copy of a book title to one individual
>you can create a query (qryGivenOut)
that counts the number of books sent out to individuals
SELECT bookTitle, COUNT(bookTitle) as numberGivenOut
FROM tblIndividual GROUP BY bookTitle
>and create a 2nd query (qryInStock) to give you total books in stock
SELECT bookTitle, SUM(noOrdered) as numberInStock
FROM tblBook GROUP BY bookTitle
>and create a 3rd query to give you the number of remaining books
SELECT qryInStock.bookTitle,
numberInStock,
numberGivenOut,
numberInStock - numberGivenOut as numberRemaining
FROM qryInStock left join
qryGivenOut on qryInStock.bookTitle =
qryGivenOut.bookTitle
>On Mar 26, 3:37 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
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" <j...@NOSPAM.stallan.plus.comwrote in message
>>>news:46**********************@ptn-nntp-reader02.plus.net...
>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- Hide quoted text -
>>- Show quoted text -
The query now lists all items of stock (and includes 0 when all of a
particular item has been used up), but the 'Stock Remaining' field only
holds values for those items where a title has been given out. In
cases
where a particular title hasn't been given out the field is blank.
The only way I can see how many of these titles are left (ie the total
ordered as none have been given out to date) is to allow the
'SumOfNumber
Ordered' to be viewed.
I am almost there...

To elaborate, my query results look like this example(I don't know if,
when
I post this, it will stay put):

Book Title Language Stock Remaining SumOfNumber
Ordered

Sleepyhead Albanian 13 15
Zoo Albanian 13
15
Sleepyhead Arabic
2
Zoo Bengali 13
15

etc

As no Sleepyhead has been given out in Arabic, the stock remaining equals
the SumOfNumber Ordered yet this isn't showing in the Stock Remaining
field.
Where other items have been given out, the remaining stock is shown.

- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

again, without seeing the query, it's hard to know what the problem is

can you open the query in 'sql view' and post it here ?
I'm sorry, but I don't understand what you mean by SQL View
May 25 '07 #41

P: n/a
Jo

<le*********@natpro.comwrote in message
news:11**********************@q66g2000hsg.googlegr oups.com...
On May 24, 3:27 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
>"Jo" <j...@NOSPAM.stallan.plus.comwrote in message

news:46**********************@ptn-nntp-reader02.plus.net...


<lesperan...@natpro.comwrote in message
news:11*********************@p77g2000hsh.googlegr oups.com...
assuming that this is your data
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
>and assuming
a) no. ordered is number of books you've received from the supplier
b) you send out one copy of a book title to one individual
>you can create a query (qryGivenOut)
that counts the number of books sent out to individuals
SELECT bookTitle, COUNT(bookTitle) as numberGivenOut
FROM tblIndividual GROUP BY bookTitle
>and create a 2nd query (qryInStock) to give you total books in stock
SELECT bookTitle, SUM(noOrdered) as numberInStock
FROM tblBook GROUP BY bookTitle
>and create a 3rd query to give you the number of remaining books
SELECT qryInStock.bookTitle,
numberInStock,
numberGivenOut,
numberInStock - numberGivenOut as numberRemaining
FROM qryInStock left join
qryGivenOut on qryInStock.bookTitle =
qryGivenOut.bookTitle
>On Mar 26, 3:37 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
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" <j...@NOSPAM.stallan.plus.comwrote in message
>>>news:46**********************@ptn-nntp-reader02.plus.net...
>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- Hide quoted text -
>>- Show quoted text -
The query now lists all items of stock (and includes 0 when all of a
particular item has been used up), but the 'Stock Remaining' field only
holds values for those items where a title has been given out. In
cases
where a particular title hasn't been given out the field is blank.
The only way I can see how many of these titles are left (ie the total
ordered as none have been given out to date) is to allow the
'SumOfNumber
Ordered' to be viewed.
I am almost there...

To elaborate, my query results look like this example(I don't know if,
when
I post this, it will stay put):

Book Title Language Stock Remaining SumOfNumber
Ordered

Sleepyhead Albanian 13 15
Zoo Albanian 13
15
Sleepyhead Arabic
2
Zoo Bengali 13
15

etc

As no Sleepyhead has been given out in Arabic, the stock remaining equals
the SumOfNumber Ordered yet this isn't showing in the Stock Remaining
field.
Where other items have been given out, the remaining stock is shown.

- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

again, without seeing the query, it's hard to know what the problem is

can you open the query in 'sql view' and post it here ?
Ah ha..I found the option to open in SQL View. Here it is...
>SELECT [Books Ordered].[Book Title], [Books Ordered].Language, [SumOfNumber
Ordered]-[CountOfBook Title] AS [Stock Remaining], [Books
Ordered].[SumOfNumber Ordered]
FROM [Books Ordered] LEFT JOIN [Count of Books Given Out] ON ([Books
Ordered].Language = [Count of Books Given Out].Language) AND ([Books
Ordered].[Book Title] = [Count of Books Given Out].[Book Title])
GROUP BY [Books Ordered].[Book Title], [Books Ordered].Language,
[SumOfNumber Ordered]-[CountOfBook Title], [Books Ordered].[SumOfNumber
Ordered], [Count of Books Given Out].[CountOfBook Title], [Count of Books
Given Out].CountOfLanguage
ORDER BY [Books Ordered].Language;

Hope this helps.
May 25 '07 #42

P: n/a
Jo

<le*********@natpro.comwrote in message
news:11**********************@q66g2000hsg.googlegr oups.com...
On May 24, 3:27 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
>"Jo" <j...@NOSPAM.stallan.plus.comwrote in message

news:46**********************@ptn-nntp-reader02.plus.net...


<lesperan...@natpro.comwrote in message
news:11*********************@p77g2000hsh.googlegr oups.com...
assuming that this is your data
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
>and assuming
a) no. ordered is number of books you've received from the supplier
b) you send out one copy of a book title to one individual
>you can create a query (qryGivenOut)
that counts the number of books sent out to individuals
SELECT bookTitle, COUNT(bookTitle) as numberGivenOut
FROM tblIndividual GROUP BY bookTitle
>and create a 2nd query (qryInStock) to give you total books in stock
SELECT bookTitle, SUM(noOrdered) as numberInStock
FROM tblBook GROUP BY bookTitle
>and create a 3rd query to give you the number of remaining books
SELECT qryInStock.bookTitle,
numberInStock,
numberGivenOut,
numberInStock - numberGivenOut as numberRemaining
FROM qryInStock left join
qryGivenOut on qryInStock.bookTitle =
qryGivenOut.bookTitle
>On Mar 26, 3:37 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
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" <j...@NOSPAM.stallan.plus.comwrote in message
>>>news:46**********************@ptn-nntp-reader02.plus.net...
>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- Hide quoted text -
>>- Show quoted text -
The query now lists all items of stock (and includes 0 when all of a
particular item has been used up), but the 'Stock Remaining' field only
holds values for those items where a title has been given out. In
cases
where a particular title hasn't been given out the field is blank.
The only way I can see how many of these titles are left (ie the total
ordered as none have been given out to date) is to allow the
'SumOfNumber
Ordered' to be viewed.
I am almost there...

To elaborate, my query results look like this example(I don't know if,
when
I post this, it will stay put):

Book Title Language Stock Remaining SumOfNumber
Ordered

Sleepyhead Albanian 13 15
Zoo Albanian 13
15
Sleepyhead Arabic
2
Zoo Bengali 13
15

etc

As no Sleepyhead has been given out in Arabic, the stock remaining equals
the SumOfNumber Ordered yet this isn't showing in the Stock Remaining
field.
Where other items have been given out, the remaining stock is shown.

- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

again, without seeing the query, it's hard to know what the problem is

can you open the query in 'sql view' and post it here ?
I hope you haven't given up on me.....we are almost there!
Jun 6 '07 #43

P: n/a
On May 25, 4:18 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message

news:11**********************@q66g2000hsg.googlegr oups.com...


On May 24, 3:27 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
"Jo" <j...@NOSPAM.stallan.plus.comwrote in message
>news:46**********************@ptn-nntp-reader02.plus.net...
<lesperan...@natpro.comwrote in message
news:11*********************@p77g2000hsh.googlegro ups.com...
assuming that this is your data
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
and assuming
a) no. ordered is number of books you've received from the supplier
b) you send out one copy of a book title to one individual
you can create a query (qryGivenOut)
that counts the number of books sent out to individuals
SELECT bookTitle, COUNT(bookTitle) as numberGivenOut
FROM tblIndividual GROUP BY bookTitle
and create a 2nd query (qryInStock) to give you total books in stock
SELECT bookTitle, SUM(noOrdered) as numberInStock
FROM tblBook GROUP BY bookTitle
and create a 3rd query to give you the number of remaining books
SELECT qryInStock.bookTitle,
numberInStock,
numberGivenOut,
numberInStock - numberGivenOut as numberRemaining
FROM qryInStock left join
qryGivenOut on qryInStock.bookTitle =
qryGivenOut.bookTitle
On Mar 26, 3:37 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
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" <j...@NOSPAM.stallan.plus.comwrote in message
>>news:46**********************@ptn-nntp-reader02.plus.net...
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- Hide quoted text -
>- Show quoted text -
The query now lists all items of stock (and includes 0 when all of a
particular item has been used up), but the 'Stock Remaining' field only
holds values for those items where a title has been given out. In
cases
where a particular title hasn't been given out the field is blank.
The only way I can see how many of these titles are left (ie the total
ordered as none have been given out to date) is to allow the
'SumOfNumber
Ordered' to be viewed.
I am almost there...
To elaborate, my query results look like this example(I don't know if,
when
I post this, it will stay put):
Book Title Language Stock Remaining SumOfNumber
Ordered
Sleepyhead Albanian 13 15
Zoo Albanian 13
15
Sleepyhead Arabic
2
Zoo Bengali 13
15
etc
As no Sleepyhead has been given out in Arabic, the stock remaining equals
the SumOfNumber Ordered yet this isn't showing in the Stock Remaining
field.
Where other items have been given out, the remaining stock is shown.
- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -
again, without seeing the query, it's hard to know what the problem is
can you open the query in 'sql view' and post it here ?

Ah ha..I found the option to open in SQL View. Here it is...
SELECT [Books Ordered].[Book Title], [Books Ordered].Language, [SumOfNumber
Ordered]-[CountOfBook Title] AS [Stock Remaining], [Books
Ordered].[SumOfNumber Ordered]

FROM [Books Ordered] LEFT JOIN [Count of Books Given Out] ON ([Books
Ordered].Language = [Count of Books Given Out].Language) AND ([Books
Ordered].[Book Title] = [Count of Books Given Out].[Book Title])
GROUP BY [Books Ordered].[Book Title], [Books Ordered].Language,
[SumOfNumber Ordered]-[CountOfBook Title], [Books Ordered].[SumOfNumber
Ordered], [Count of Books Given Out].[CountOfBook Title], [Count of Books
Given Out].CountOfLanguage
ORDER BY [Books Ordered].Language;

Hope this helps.- Hide quoted text -

- Show quoted text -
... FROM [Books Ordered] LEFT JOIN [Count of Books Given Out] ON ...
is [books ordered] a table or a query ?
is [count of books given out] a table or a query ?

if either or both answers is yes, can you provide the sql query for
them too
so I can duplicate the problem

I'm not sure why you need a GROUP BY when none of the fields are
aggregated (ie. sum, count, etc), so I'm guessing that [count of books
given out] is a query

sorry it took a while to get back to you, busy work schedule

Jun 13 '07 #44

P: n/a
Jo

"Roger" <le*********@natpro.comwrote in message
news:11**********************@x35g2000prf.googlegr oups.com...
On May 25, 4:18 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
><lesperan...@natpro.comwrote in message

news:11**********************@q66g2000hsg.googleg roups.com...


On May 24, 3:27 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
"Jo" <j...@NOSPAM.stallan.plus.comwrote in message
>>news:46**********************@ptn-nntp-reader02.plus.net...
<lesperan...@natpro.comwrote in message
news:11*********************@p77g2000hsh.googlegr oups.com...
assuming that this is your data
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
>and assuming
a) no. ordered is number of books you've received from the
supplier
b) you send out one copy of a book title to one individual
>you can create a query (qryGivenOut)
that counts the number of books sent out to individuals
SELECT bookTitle, COUNT(bookTitle) as numberGivenOut
FROM tblIndividual GROUP BY bookTitle
>and create a 2nd query (qryInStock) to give you total books in
stock
SELECT bookTitle, SUM(noOrdered) as numberInStock
FROM tblBook GROUP BY bookTitle
>and create a 3rd query to give you the number of remaining books
SELECT qryInStock.bookTitle,
numberInStock,
numberGivenOut,
numberInStock - numberGivenOut as numberRemaining
FROM qryInStock left join
qryGivenOut on qryInStock.bookTitle =
qryGivenOut.bookTitle
>On Mar 26, 3:37 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
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" <j...@NOSPAM.stallan.plus.comwrote in message
>>>news:46**********************@ptn-nntp-reader02.plus.net...
>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- Hide quoted text -
>>- Show quoted text -
The query now lists all items of stock (and includes 0 when all of a
particular item has been used up), but the 'Stock Remaining' field
only
holds values for those items where a title has been given out. In
cases
where a particular title hasn't been given out the field is blank.
The only way I can see how many of these titles are left (ie the
total
ordered as none have been given out to date) is to allow the
'SumOfNumber
Ordered' to be viewed.
I am almost there...
>To elaborate, my query results look like this example(I don't know if,
when
I post this, it will stay put):
>Book Title Language Stock Remaining SumOfNumber
Ordered
>Sleepyhead Albanian 13 15
Zoo Albanian 13
15
Sleepyhead Arabic
2
Zoo Bengali 13
15
>etc
>As no Sleepyhead has been given out in Arabic, the stock remaining
equals
the SumOfNumber Ordered yet this isn't showing in the Stock Remaining
field.
Where other items have been given out, the remaining stock is shown.
>- Hide quoted text -
>- Show quoted text -- Hide quoted text -
>- Show quoted text -
again, without seeing the query, it's hard to know what the problem is
can you open the query in 'sql view' and post it here ?

Ah ha..I found the option to open in SQL View. Here it is...
>SELECT [Books Ordered].[Book Title], [Books Ordered].Language,
[SumOfNumber
Ordered]-[CountOfBook Title] AS [Stock Remaining], [Books
Ordered].[SumOfNumber Ordered]

FROM [Books Ordered] LEFT JOIN [Count of Books Given Out] ON ([Books
Ordered].Language = [Count of Books Given Out].Language) AND ([Books
Ordered].[Book Title] = [Count of Books Given Out].[Book Title])
GROUP BY [Books Ordered].[Book Title], [Books Ordered].Language,
[SumOfNumber Ordered]-[CountOfBook Title], [Books Ordered].[SumOfNumber
Ordered], [Count of Books Given Out].[CountOfBook Title], [Count of Books
Given Out].CountOfLanguage
ORDER BY [Books Ordered].Language;

Hope this helps.- Hide quoted text -

- Show quoted text -

.. FROM [Books Ordered] LEFT JOIN [Count of Books Given Out] ON ...
is [books ordered] a table or a query ?
is [count of books given out] a table or a query ?

if either or both answers is yes, can you provide the sql query for
them too
so I can duplicate the problem

I'm not sure why you need a GROUP BY when none of the fields are
aggregated (ie. sum, count, etc), so I'm guessing that [count of books
given out] is a query

sorry it took a while to get back to you, busy work schedule
That's ok, I'm just pleased you're back. you've done me proud so far!

[books ordered] is a query:

SELECT [Book Orders].[Book Title], [Book Orders].Language, Sum([Book
Orders].[Number Ordered]) AS [SumOfNumber Ordered]
FROM [Book Orders]
GROUP BY [Book Orders].[Book Title], [Book Orders].Language
HAVING ((([Book Orders].[Book Title]) Like "*") AND (([Book
Orders].Language) Like "*"))
ORDER BY [Book Orders].Language;

[count of books given out] is a query too:

SELECT Count([All Books Out].[Book Title]) AS [CountOfBook Title],
Count([All Books Out].Language) AS CountOfLanguage, [All Books Out].[Book
Title], [All Books Out].Language
FROM [All Books Out]
GROUP BY [All Books Out].[Book Title], [All Books Out].Language
HAVING ((([All Books Out].[Book Title]) Like "*") AND (([All Books
Out].Language) Like "*"))
ORDER BY [All Books Out].Language;

I have only done what you have advised all the way though (I think) and I
have great results so far (being a newbie). The last bit is really only a
bit of tidying up, but it would be nice to have the complete results in one
table.
Jun 14 '07 #45

P: n/a
On Jun 14, 12:33 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
"Roger" <lesperan...@natpro.comwrote in message

news:11**********************@x35g2000prf.googlegr oups.com...
On May 25, 4:18 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message
>news:11**********************@q66g2000hsg.googleg roups.com...
On May 24, 3:27 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
"Jo" <j...@NOSPAM.stallan.plus.comwrote in message
>news:46**********************@ptn-nntp-reader02.plus.net...
<lesperan...@natpro.comwrote in message
news:11*********************@p77g2000hsh.googlegro ups.com...
assuming that this is your data
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
and assuming
a) no. ordered is number of books you've received from the
supplier
b) you send out one copy of a book title to one individual
you can create a query (qryGivenOut)
that counts the number of books sent out to individuals
SELECT bookTitle, COUNT(bookTitle) as numberGivenOut
FROM tblIndividual GROUP BY bookTitle
and create a 2nd query (qryInStock) to give you total books in
stock
SELECT bookTitle, SUM(noOrdered) as numberInStock
FROM tblBook GROUP BY bookTitle
and create a 3rd query to give you the number of remaining books
SELECT qryInStock.bookTitle,
numberInStock,
numberGivenOut,
numberInStock - numberGivenOut as numberRemaining
FROM qryInStock left join
qryGivenOut on qryInStock.bookTitle =
qryGivenOut.bookTitle
On Mar 26, 3:37 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
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" <j...@NOSPAM.stallan.plus.comwrote in message
>>news:46**********************@ptn-nntp-reader02.plus.net...
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- Hide quoted text -
>- Show quoted text -
The query now lists all items of stock (and includes 0 when all of a
particular item has been used up), but the 'Stock Remaining' field
only
holds values for those items where a title has been given out. In
cases
where a particular title hasn't been given out the field is blank.
The only way I can see how many of these titles are left (ie the
total
ordered as none have been given out to date) is to allow the
'SumOfNumber
Ordered' to be viewed.
I am almost there...
To elaborate, my query results look like this example(I don't know if,
when
I post this, it will stay put):
Book Title Language Stock Remaining SumOfNumber
Ordered
Sleepyhead Albanian 13 15
Zoo Albanian 13
15
Sleepyhead Arabic
2
Zoo Bengali 13
15
etc
As no Sleepyhead has been given out in Arabic, the stock remaining
equals
the SumOfNumber Ordered yet this isn't showing in the Stock Remaining
field.
Where other items have been given out, the remaining stock is shown.
- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -
again, without seeing the query, it's hard to know what the problem is
can you open the query in 'sql view' and post it here ?
Ah ha..I found the option to open in SQL View. Here it is...
SELECT [Books Ordered].[Book Title], [Books Ordered].Language,
[SumOfNumber
Ordered]-[CountOfBook Title] AS [Stock Remaining], [Books
Ordered].[SumOfNumber Ordered]
FROM [Books Ordered] LEFT JOIN [Count of Books Given Out] ON ([Books
Ordered].Language = [Count of Books Given Out].Language) AND ([Books
Ordered].[Book Title] = [Count of Books Given Out].[Book Title])
GROUP BY [Books Ordered].[Book Title], [Books Ordered].Language,
[SumOfNumber Ordered]-[CountOfBook Title], [Books Ordered].[SumOfNumber
Ordered], [Count of Books Given Out].[CountOfBook Title], [Count of Books
Given Out].CountOfLanguage
ORDER BY [Books Ordered].Language;
Hope this helps.- Hide quoted text -
- Show quoted text -
.. FROM [Books Ordered] LEFT JOIN [Count of Books Given Out] ON ...
is [books ordered] a table or a query ?
is [count of books given out] a table or a query ?
if either or both answers is yes, can you provide the sql query for
them too
so I can duplicate the problem
I'm not sure why you need a GROUP BY when none of the fields are
aggregated (ie. sum, count, etc), so I'm guessing that [count of books
given out] is a query
sorry it took a while to get back to you, busy work schedule

That's ok, I'm just pleased you're back. you've done me proud so far!

[books ordered] is a query:

SELECT [Book Orders].[Book Title], [Book Orders].Language, Sum([Book
Orders].[Number Ordered]) AS [SumOfNumber Ordered]
FROM [Book Orders]
GROUP BY [Book Orders].[Book Title], [Book Orders].Language
HAVING ((([Book Orders].[Book Title]) Like "*") AND (([Book
Orders].Language) Like "*"))
ORDER BY [Book Orders].Language;

[count of books given out] is a query too:

SELECT Count([All Books Out].[Book Title]) AS [CountOfBook Title],
Count([All Books Out].Language) AS CountOfLanguage, [All Books Out].[Book
Title], [All Books Out].Language
FROM [All Books Out]
GROUP BY [All Books Out].[Book Title], [All Books Out].Language
HAVING ((([All Books Out].[Book Title]) Like "*") AND (([All Books
Out].Language) Like "*"))
ORDER BY [All Books Out].Language;

I have only done what you have advised all the way though (I think) and I
have great results so far (being a newbie). The last bit is really only a
bit of tidying up, but it would be nice to have the complete results in one
table.- Hide quoted text -

- Show quoted text -
the problem is with the query you sent on May 25th, that has the
'stock remaining' field, currently defined as
Stock Remaining: [SumOfNumber Ordered]-[CountOfBook Title]

if you look at this query in design mode, you'll see that is does a
'left join', indicated by the little arrows that point to the 'book
title' and 'language' field of the 'count of books given out' query

so a left join will give you a list all the books ordered, and the
corresponding count of book titles given out, which is what you want

if no books are given out, the countOfBookTitle will be a special
value called NULL

now when doing anything type of calculation, NULL wins, so
null + 1 = null
null - 1 = null
null * 10 = null
null / 1= null

so the 'stock remaining' using the above calculation is ???
you got it - null

so, in this case, we need to tell our calculation that we want the
null to be 0
in other cases, we might want to be 1, or "apple" or .....

so you need to change the calculation to use the nz() function, as in

Stock Remaining: [SumOfNumber Ordered]-nz([CountOfBook Title],0)

Jun 14 '07 #46

P: n/a
Jo

"Roger" <le*********@natpro.comwrote in message
news:11*********************@d30g2000prg.googlegro ups.com...
On Jun 14, 12:33 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
>"Roger" <lesperan...@natpro.comwrote in message

news:11**********************@x35g2000prf.googleg roups.com...
On May 25, 4:18 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message
>>news:11**********************@q66g2000hsg.google groups.com...
On May 24, 3:27 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
"Jo" <j...@NOSPAM.stallan.plus.comwrote in message
>>news:46**********************@ptn-nntp-reader02.plus.net...
<lesperan...@natpro.comwrote in message
news:11*********************@p77g2000hsh.googlegr oups.com...
assuming that this is your data
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
>and assuming
a) no. ordered is number of books you've received from the
supplier
b) you send out one copy of a book title to one individual
>you can create a query (qryGivenOut)
that counts the number of books sent out to individuals
SELECT bookTitle, COUNT(bookTitle) as numberGivenOut
FROM tblIndividual GROUP BY bookTitle
>and create a 2nd query (qryInStock) to give you total books in
stock
SELECT bookTitle, SUM(noOrdered) as numberInStock
FROM tblBook GROUP BY bookTitle
>and create a 3rd query to give you the number of remaining books
SELECT qryInStock.bookTitle,
numberInStock,
numberGivenOut,
numberInStock - numberGivenOut as numberRemaining
FROM qryInStock left join
qryGivenOut on qryInStock.bookTitle =
qryGivenOut.bookTitle
>On Mar 26, 3:37 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
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" <j...@NOSPAM.stallan.plus.comwrote in message
>>>news:46**********************@ptn-nntp-reader02.plus.net...
>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- Hide quoted text -
>>- Show quoted text -
The query now lists all items of stock (and includes 0 when all
of a
particular item has been used up), but the 'Stock Remaining'
field
only
holds values for those items where a title has been given out.
In
cases
where a particular title hasn't been given out the field is
blank.
The only way I can see how many of these titles are left (ie the
total
ordered as none have been given out to date) is to allow the
'SumOfNumber
Ordered' to be viewed.
I am almost there...
>To elaborate, my query results look like this example(I don't know
if,
when
I post this, it will stay put):
>Book Title Language Stock Remaining
SumOfNumber
Ordered
>Sleepyhead Albanian 13
15
Zoo Albanian 13
15
Sleepyhead Arabic
2
Zoo Bengali 13
15
>etc
>As no Sleepyhead has been given out in Arabic, the stock remaining
equals
the SumOfNumber Ordered yet this isn't showing in the Stock
Remaining
field.
Where other items have been given out, the remaining stock is
shown.
>- Hide quoted text -
>- Show quoted text -- Hide quoted text -
>- Show quoted text -
again, without seeing the query, it's hard to know what the problem
is
can you open the query in 'sql view' and post it here ?
>Ah ha..I found the option to open in SQL View. Here it is...
>SELECT [Books Ordered].[Book Title], [Books Ordered].Language,
[SumOfNumber
Ordered]-[CountOfBook Title] AS [Stock Remaining], [Books
Ordered].[SumOfNumber Ordered]
>FROM [Books Ordered] LEFT JOIN [Count of Books Given Out] ON ([Books
Ordered].Language = [Count of Books Given Out].Language) AND ([Books
Ordered].[Book Title] = [Count of Books Given Out].[Book Title])
GROUP BY [Books Ordered].[Book Title], [Books Ordered].Language,
[SumOfNumber Ordered]-[CountOfBook Title], [Books
Ordered].[SumOfNumber
Ordered], [Count of Books Given Out].[CountOfBook Title], [Count of
Books
Given Out].CountOfLanguage
ORDER BY [Books Ordered].Language;
>Hope this helps.- Hide quoted text -
>- Show quoted text -
.. FROM [Books Ordered] LEFT JOIN [Count of Books Given Out] ON ...
is [books ordered] a table or a query ?
is [count of books given out] a table or a query ?
if either or both answers is yes, can you provide the sql query for
them too
so I can duplicate the problem
I'm not sure why you need a GROUP BY when none of the fields are
aggregated (ie. sum, count, etc), so I'm guessing that [count of books
given out] is a query
sorry it took a while to get back to you, busy work schedule

That's ok, I'm just pleased you're back. you've done me proud so far!

[books ordered] is a query:

SELECT [Book Orders].[Book Title], [Book Orders].Language, Sum([Book
Orders].[Number Ordered]) AS [SumOfNumber Ordered]
FROM [Book Orders]
GROUP BY [Book Orders].[Book Title], [Book Orders].Language
HAVING ((([Book Orders].[Book Title]) Like "*") AND (([Book
Orders].Language) Like "*"))
ORDER BY [Book Orders].Language;

[count of books given out] is a query too:

SELECT Count([All Books Out].[Book Title]) AS [CountOfBook Title],
Count([All Books Out].Language) AS CountOfLanguage, [All Books Out].[Book
Title], [All Books Out].Language
FROM [All Books Out]
GROUP BY [All Books Out].[Book Title], [All Books Out].Language
HAVING ((([All Books Out].[Book Title]) Like "*") AND (([All Books
Out].Language) Like "*"))
ORDER BY [All Books Out].Language;

I have only done what you have advised all the way though (I think) and I
have great results so far (being a newbie). The last bit is really only
a
bit of tidying up, but it would be nice to have the complete results in
one
table.- Hide quoted text -

- Show quoted text -

the problem is with the query you sent on May 25th, that has the
'stock remaining' field, currently defined as
Stock Remaining: [SumOfNumber Ordered]-[CountOfBook Title]

if you look at this query in design mode, you'll see that is does a
'left join', indicated by the little arrows that point to the 'book
title' and 'language' field of the 'count of books given out' query

so a left join will give you a list all the books ordered, and the
corresponding count of book titles given out, which is what you want

if no books are given out, the countOfBookTitle will be a special
value called NULL

now when doing anything type of calculation, NULL wins, so
null + 1 = null
null - 1 = null
null * 10 = null
null / 1= null

so the 'stock remaining' using the above calculation is ???
you got it - null

so, in this case, we need to tell our calculation that we want the
null to be 0
in other cases, we might want to be 1, or "apple" or .....

so you need to change the calculation to use the nz() function, as in

Stock Remaining: [SumOfNumber Ordered]-nz([CountOfBook Title],0)
Ah ha! I was on the right track....I have been playing around with the Nz
function, but to no avail. I'm off to work now so I'll take this info with
me and give it a go. I'll report back later.
Jun 14 '07 #47

P: n/a
Jo

"Roger" <le*********@natpro.comwrote in message
news:11*********************@d30g2000prg.googlegro ups.com...
On Jun 14, 12:33 am, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
>"Roger" <lesperan...@natpro.comwrote in message

news:11**********************@x35g2000prf.googleg roups.com...
On May 25, 4:18 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
<lesperan...@natpro.comwrote in message
>>news:11**********************@q66g2000hsg.google groups.com...
On May 24, 3:27 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
"Jo" <j...@NOSPAM.stallan.plus.comwrote in message
>>news:46**********************@ptn-nntp-reader02.plus.net...
<lesperan...@natpro.comwrote in message
news:11*********************@p77g2000hsh.googlegr oups.com...
assuming that this is your data
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
>and assuming
a) no. ordered is number of books you've received from the
supplier
b) you send out one copy of a book title to one individual
>you can create a query (qryGivenOut)
that counts the number of books sent out to individuals
SELECT bookTitle, COUNT(bookTitle) as numberGivenOut
FROM tblIndividual GROUP BY bookTitle
>and create a 2nd query (qryInStock) to give you total books in
stock
SELECT bookTitle, SUM(noOrdered) as numberInStock
FROM tblBook GROUP BY bookTitle
>and create a 3rd query to give you the number of remaining books
SELECT qryInStock.bookTitle,
numberInStock,
numberGivenOut,
numberInStock - numberGivenOut as numberRemaining
FROM qryInStock left join
qryGivenOut on qryInStock.bookTitle =
qryGivenOut.bookTitle
>On Mar 26, 3:37 pm, "Jo" <j...@NOSPAM.stallan.plus.comwrote:
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" <j...@NOSPAM.stallan.plus.comwrote in message
>>>news:46**********************@ptn-nntp-reader02.plus.net...
>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- Hide quoted text -
>>- Show quoted text -
The query now lists all items of stock (and includes 0 when all
of a
particular item has been used up), but the 'Stock Remaining'
field
only
holds values for those items where a title has been given out.
In
cases
where a particular title hasn't been given out the field is
blank.
The only way I can see how many of these titles are left (ie the
total
ordered as none have been given out to date) is to allow the
'SumOfNumber
Ordered' to be viewed.
I am almost there...
>To elaborate, my query results look like this example(I don't know
if,
when
I post this, it will stay put):
>Book Title Language Stock Remaining
SumOfNumber
Ordered
>Sleepyhead Albanian 13
15
Zoo Albanian 13
15
Sleepyhead Arabic
2
Zoo Bengali 13
15
>etc
>As no Sleepyhead has been given out in Arabic, the stock remaining
equals
the SumOfNumber Ordered yet this isn't showing in the Stock
Remaining
field.
Where other items have been given out, the remaining stock is
shown.
>- Hide quoted text -
>- Show quoted text -- Hide quoted text -
>- Show quoted text -
again, without seeing the query, it's hard to know what the problem
is
can you open the query in 'sql view' and post it here ?
>Ah ha..I found the option to open in SQL View. Here it is...
>SELECT [Books Ordered].[Book Title], [Books Ordered].Language,
[SumOfNumber
Ordered]-[CountOfBook Title] AS [Stock Remaining], [Books
Ordered].[SumOfNumber Ordered]
>FROM [Books Ordered] LEFT JOIN [Count of Books Given Out] ON ([Books
Ordered].Language = [Count of Books Given Out].Language) AND ([Books
Ordered].[Book Title] = [Count of Books Given Out].[Book Title])
GROUP BY [Books Ordered].[Book Title], [Books Ordered].Language,
[SumOfNumber Ordered]-[CountOfBook Title], [Books
Ordered].[SumOfNumber
Ordered], [Count of Books Given Out].[CountOfBook Title], [Count of
Books
Given Out].CountOfLanguage
ORDER BY [Books Ordered].Language;
>Hope this helps.- Hide quoted text -
>- Show quoted text -
.. FROM [Books Ordered] LEFT JOIN [Count of Books Given Out] ON ...
is [books ordered] a table or a query ?
is [count of books given out] a table or a query ?
if either or both answers is yes, can you provide the sql query for
them too
so I can duplicate the problem
I'm not sure why you need a GROUP BY when none of the fields are
aggregated (ie. sum, count, etc), so I'm guessing that [count of books
given out] is a query
sorry it took a while to get back to you, busy work schedule

That's ok, I'm just pleased you're back. you've done me proud so far!

[books ordered] is a query:

SELECT [Book Orders].[Book Title], [Book Orders].Language, Sum([Book
Orders].[Number Ordered]) AS [SumOfNumber Ordered]
FROM [Book Orders]
GROUP BY [Book Orders].[Book Title], [Book Orders].Language
HAVING ((([Book Orders].[Book Title]) Like "*") AND (([Book
Orders].Language) Like "*"))
ORDER BY [Book Orders].Language;

[count of books given out] is a query too:

SELECT Count([All Books Out].[Book Title]) AS [CountOfBook Title],
Count([All Books Out].Language) AS CountOfLanguage, [All Books Out].[Book
Title], [All Books Out].Language
FROM [All Books Out]
GROUP BY [All Books Out].[Book Title], [All Books Out].Language
HAVING ((([All Books Out].[Book Title]) Like "*") AND (([All Books
Out].Language) Like "*"))
ORDER BY [All Books Out].Language;

I have only done what you have advised all the way though (I think) and I
have great results so far (being a newbie). The last bit is really only
a
bit of tidying up, but it would be nice to have the complete results in
one
table.- Hide quoted text -

- Show quoted text -

the problem is with the query you sent on May 25th, that has the
'stock remaining' field, currently defined as
Stock Remaining: [SumOfNumber Ordered]-[CountOfBook Title]

if you look at this query in design mode, you'll see that is does a
'left join', indicated by the little arrows that point to the 'book
title' and 'language' field of the 'count of books given out' query

so a left join will give you a list all the books ordered, and the
corresponding count of book titles given out, which is what you want

if no books are given out, the countOfBookTitle will be a special
value called NULL

now when doing anything type of calculation, NULL wins, so
null + 1 = null
null - 1 = null
null * 10 = null
null / 1= null

so the 'stock remaining' using the above calculation is ???
you got it - null

so, in this case, we need to tell our calculation that we want the
null to be 0
in other cases, we might want to be 1, or "apple" or .....

so you need to change the calculation to use the nz() function, as in

Stock Remaining: [SumOfNumber Ordered]-nz([CountOfBook Title],0)
I am back from work now (just a short stint on a Thurs).....and it's done!
You have made me one happy lady. Have an imaginary pint on me.
I couldn't believe it - I was working along the right lines all along with
the Nz function (having resorted to a manual or two), but - not really
having a clue what I was doing with it - I was entering the function in the
totaly wrong place in the design!
It has taken nearly 3 months to get there, but thanks to you I now have a
working database. Thank-you so much.
Jo
Jun 14 '07 #48

This discussion thread is closed

Replies have been disabled for this discussion.