Multiple record's in query 1

[Kirin]

I have created a database for books. Where on book can have multiple authors, publishers and translators.
When I create a query all with sum buy for one month. The query is wrong, because when a book have multiple authors, publishers or translators it will sum all, so istedet of 100 i get 300.

Can someone help a newbee ?

 

[r937]

divide the sum by the number of authors


rudy

 

[Kirin]

That won't do. Because it will differe's each time a new book will be registered. Sometimes it will be two publishers and somtimes it could be five author's and two publisher's and three translators, and sometimes anly one authors. The only constant is the BookID that is unique for each book.

 

[r937]

okay, show the query that isn't working, and some sample data, i'll see if i can figure it out

 

[Kirin]

Hi Rudy.

The expression is
Sum Totalt: Sum(([Innkjøpspris]))

Sample data:

Innkjøpsdato BokID Etternavn Fornavn Tittel Forlag Utgitt Innkjøpspris Sum Totalt Innkjøpsted Antall bøker
03.01.2003 1226 Euripides NN Medeia Den Norske Bokklubben 2002 Fra mamma 1
07.01.2003 1227 Jordan Robert Crossroads of Twilight Tor 2003 kr 198,00 kr 198,00 Norli Bokhandel, Oslo 1
07.01.2003 1229 Barthes Roland Mytologier Den Norske Bokklubben 2002 kr 300,00 kr 300,00 Den norske Bokklubben 1
09.01.2003 1228 Belyj Andrej Petersburg Solum Forlag 2001 kr 209,00 kr 209,00 Norli, Nedre Slottsgate, Oslo 1
15.01.2003 1230 Disney Walt Donald Duck & Co 1956 del III Egmont Serieforlaget AS 2003 kr 279,00 kr 279,00 Egmont Serieforlaget 1
16.01.2003 1231 Forfatter Ukjent Østkirken - Skrifter fra bysantinsk og ortodoks kristendom Den Norske Bokklubben 2002 kr 290,00 kr 290,00 Den norske Bokklubben 1
16.01.2003 1232 Bjørnstad Ketil Jæger H. Aschehoug & Co. (W. Nygaard) 2001 kr 69,00 kr 69,00 Bokklubben Nye Bøker 1
18.01.2003 1233 Eriksen Trond Berg Veien til Toscana H. Aschehoug & Co. (W. Nygaard) 2002 kr 69,00 kr 69,00 Bokklubben Nye Bøker 1
24.01.2003 1234 Falk Lee Det niende Fantomet 1700-1735 - Bind IX Egmont Serieforlaget AS 2003 kr 495,00 kr 495,00 Egmont Serieforlaget 1
25.01.2003 1235 Alighieri Dante Den guddommelige komedie Gyldendal Norsk Forlag AS 2000 kr 79,00 kr 79,00 Bokklubben Dagens Bok 1
12.02.2003 1236 Buber Martin Jeg og du Den Norske Bokklubben 2003 kr 274,00 kr 274,00 Den norske Bokklubben 1
19.02.2003 1237 Kertész Imre Uten skjebne Pax Forlag A/S 2002 kr 211,00 kr 211,00 Bokklubben Nye Bøker 1
21.02.2003 1238 Abrahamsen Unni Jord- og plantekultur 2003 Planteforsk 2003 kr 200,00 kr 200,00 Solør - Odal forsøksring 1
27.02.2003 1239 Dostojevskij Fjodor Mikhaljovitsj Brødrene Karamasov Den Norske Bokklubben 2003 kr 330,00 kr 330,00 Den norske Bokklubben 1
05.03.2003 1240 Forfatter Ukjent Greske myter og mysterier Den Norske Bokklubben 2003 kr 0,00 kr 0,00 Den norske Bokklubben 1
08.03.2003 1241 Makine Andrej Kjærlighet ved elven Amur Eide Forlag 2000 kr 89,00 kr 89,00 Haugen Bok, Volda 1
08.03.2003 1242 Makine Andrej Rekviem for Russland Eide Forlag 2001 kr 89,00 kr 89,00 Haugen Bok, Volda 1
08.03.2003 1243 Adkins Lesley Konspirasjon og kapitulasjon Adrian Forlag 2000 kr 149,00 kr 149,00 Haugen Bok, Volda 1
08.03.2003 1243 Ajtmatov Tsjingiz Konspirasjon og kapitulasjon Ansgar Forlag 2000 kr 149,00 kr 149,00 Haugen Bok, Volda 1
08.03.2003 1243 Alighieri Dante Konspirasjon og kapitulasjon Atheneum Forlag 2000 kr 149,00 kr 149,00 Haugen Bok, Volda 1
08.03.2003 1243 Borgersrud Lars Konspirasjon og kapitulasjon Oktober Forlag as 2000 kr 149,00 kr 149,00 Haugen Bok, Volda 1


When you look at the bookID 1243 I only want one sum total of these books

Thank you in advance

Kirin

 

[r937]

create this query --

SELECT DISTINCT BokID, Innkjøpspris
FROM books;

save this query as BokPris

now run this query --

SELECT sum(Innkjøpspris)
FROM BokPris;


rudy

 

[BSman]

Write a query that selects the data you need from the table that lists the books and just select the "first" of the bookID. Then base your query that counts sales (or whatever) by combining the bookID query with your sales table to create a grouping query to summarize the sales.

Your basic problem is that you need to re-design your tables and normalize them. Set up a separate table containing the bookID and the name of each author. The book list will have one entry for each book. The author table will have as many entries for each book as there are authors. This also avoids duplicating the same data in multiple records (as you have done now).

 

[Kirin]

To Rudy
Thank you very much. I had to justify the sql BokPris to this
SELECT BokTBL.BokID, BokTBL.Innkjøpspris, BokTBL.Innkjøpsdato
FROM BokTBL
WHERE (((BokTBL.Innkjøpsdato) Between #1/1/2003# And #12/31/2003#));

Because I just wanted books that I had bought this year.

The other sql was then:
SELECT [BokregisterL Spørring].Innkjøpsdato, [BokregisterL Spørring].BokID, [BokregisterL Spørring].Etternavn, [BokregisterL Spørring].Fornavn, [BokregisterL Spørring].Tittel, [BokregisterL Spørring].Forlag, [BokregisterL Spørring].Utgitt, [BokregisterL Spørring].Innkjøpspris, [BokregisterL Spørring].Innkjøpsted, Sum(BokPris.Innkjøpspris) AS [Sum]
FROM [BokregisterL Spørring], BokPris, [Sum Totalt]
GROUP BY [BokregisterL Spørring].Innkjøpsdato, [BokregisterL Spørring].BokID, [BokregisterL Spørring].Etternavn, [BokregisterL Spørring].Fornavn, [BokregisterL Spørring].Tittel, [BokregisterL Spørring].Forlag, [BokregisterL Spørring].Utgitt, [BokregisterL Spørring].Innkjøpspris, [BokregisterL Spørring].Innkjøpsted
HAVING ((([BokregisterL Spørring].Innkjøpsdato) Between #1/1/2003# And #12/31/2003#))
ORDER BY [BokregisterL Spørring].Innkjøpsdato;

I think i will give you a star for this.

To BSman:
The bookbase are set up with a many to many relation.
Where I have BokID, ForfatterID, OversetterID and ForlagID in one table. When I choose this table I will not have duplicate records, but when I create queries I will, because I then want to see all of the authors etc..
Or am'I wrong ?

Kirin

 

[BSman]

Something's wrong if you have a many to many relationship.

The relationship between two tables should be one to many (or many to one) basically in all cases. Otherwise you can get duplicate records where they should not occur. Basically, in the table that contains the "definition" of the ID (example: name of the book with the bookID) the bookID MUST be unique. That means that the relationship between that table and any table that just stores the bookID will be one (from the book list table) to many (in the other table).

To show all of a book's authors create a second form or a subreport (for a report). The second form will list all of the authors. Include that form on the book list form (that comes from the book list), related by the bookID. You could even put it on a tab on the form if you want. Same concept applies to a report (with no tabs, of course), but you set the subreport with the can grow property set to true so that if multiple authors exist the subreport on the report will get longer as needed for a specific book.

I don't know what all the IDs are for in the table you mention, but it sounds like a table that isn't needed. What you should have for the other IDs should follow my suggestion for the authors.

Here's a brief illustration of tables and contents:

BookList:
BookID, Book title

Authors:
BookID, Author
(note: you could add a dummy ID column if you want to also create a primary key column for this table, but that will not be used anywhere except internally by Access)

Forfatter:
ForfatterID, description
(same comment as with authors...this assumes that there can be multiple occurrences of forfatter for one book. Otherwise just include this data in the Book list table.)

(and so on with the other items like Oversetter and Forlag).

 

[Kirin]

Hi BSman

Here are the koblingTBL (all marked with primary key)
BokID Number
ForfatterID Number
OversetterID Number
ForlagID

This is the BokTBL
BokID Autonumber, primary key
Tittel Text
Undertittel Text
ISBN-nummer Text
UtgittID Number
Utgave Number
etcc.....

This is the ForfatterTBL
ForfatterID Autonumber, primary key
Fornavn text
Etternavn text
Født date
Død date

This is the ForlagTBL
ForlagID autonumber, primary key
ForfatterID number
Forlag text
Adresse1 text
Adresse2 text

This is the oversetterTBL
OversetterID autonumber, primary key
Oversetter, text

This tables are linked with one to many to the koblingTBL
Are this wrong thinking ?

Kirin

 

[BSman]

Drop koblingTbl.

Use BokTbl as your primary table to drive your form (or reports). You don't even need a query. Just set up the subforms (or subreports) as I mentioned, one subform for each of the other tables, linked to the main table (BokTbl)on the form by BokID. On a form you might use a separate table for each subform (with the first tab just showing the contents of the main source table, BokTbl). That should do what you need.

 

[Kirin]

Thank BSMan
I will set up an alternative to the bookbase I have, and see what I get.

Kirin

 

[BSman]

One additional comment. You only need a separate table for those items that can have multiple records for one book. My answer may have been a bit confusing because I didn't consider the tables ForfatterTBL, ForlagTBL, oversetterTBL as being lookup tables. If that's what they are (and it makes sense to do that), then if we assume forfatterRBF can have multiple records for one book, you would set up BokTbl to include the columns ForlagID and oversetterID, while you would add a new table to list the authors of each book. That table would contain just two columns, BokID and ForfatterID (I'm just guessing that this is the author ID). You then combine the records in the book authors table with the book list table and you will get one record for each author of each book (meaning the two records for the book in your example with the two authors). If you want descriptions included in your query, relate the ID columns to their respective lookup tables. Since those are many to one relationships you will not get extra records.