There are two tables.
The first Table Reader
contains information about the readers of the library.
Table fields: LibraryCardNumber, Readersurname, Readername, Readerpatronymic, Birthdate, Address, Phone
.
The second table BookDelivery
contains information about the books issued.
Table fields: Bookid, ReaderLibraryCardNumber, DateStart, DateretURned
.
I need to withdraw information about readers and the number of books seen by him.
Using the Count () aggregate function, I found the number thus:
select readerlibrarycardnumber as [reader's number],
Count (*) as [Number of books]
From BookDelivery
Group by readerlibraryCardNumber;
But it is not enough to know only the reader’s number, you need other information about the reader table (for example, the name and surname of the reader). Tried to get it in this way:
select bookdelivery.ReaderLibraryCardNumber AS [reader's number],
Reader.Readername as [Name],
Reader.Readersurname as [surname],
Count (*) as [Number of books]
From BookDelivery, Reader
Where BookDelivery.ReaderLibraryCardNumber = Reader.LibraryCardNumber
Group by readerlibraryCardNumber;
But Access swears: “Your request does not contain the specified expression” name “as part of the aggregate function.” Is it possible to somehow make a request so that the quantity is calculated, and the fields from another table were detected?
Answer 1, Authority 100%
select reader.libraryCardNumber AS [reader's number],
Reader.Readername as [Name],
Reader.Readersurname as [surname],
Count (*) as [Number of books]
From BookDelivery, Reader
Where BookDelivery.ReaderLibraryCardNumber = Reader.LibraryCardNumber
Group by reader.libraryCardNumber,
Reader.Readername
Reader.Readersurname;