SQL View - Library Database

  • Thread starter Thread starter Marko
  • Start date Start date
M

Marko

My SQL is - SELECT Loan.[Student Name], Loan.[Book
Number], Loan.[Loan Date], DateAdd("m",1,[Loan Date])+IIf
(Weekday(DateAdd("m",1,[Loan Date]),2)<6,0,8-Weekday
(DateAdd("m",1,[Loan Date]),2)) AS [Loan Due], Loan.[Loan
Returned], DateDiff("d",[Loan Due],[Loan Returned]) AS
DaysLate, Fines.finAmount
FROM Loan, Fines;

can you help
 
Dear Marko:

I've edited your query a bit to study it:

SELECT L.[Student Name], L.[Book Number], L.[Loan Date],
DateAdd("m", 1, [L,Loan Date]) +
IIf(Weekday(DateAdd("m", 1, L.[Loan Date]),2) < 6, 0, 8 -
Weekday(DateAdd("m", 1, L.[Loan Date]), 2)) AS [Loan Due],
L.[Loan Returned], DateDiff("d", L.[Loan Due], L.[Loan Returned])
AS DaysLate, F.finAmount
FROM Loan L, Fines F;

One problem would be that you do not have a JOIN between Loan and
Fines. Now, how this should be done depends on how your database is
constructed. There would need to be a unique key (perhaps the Primary
Key) to Loan on which to base the relationship between these two
tables. I would suggest perhaps that might be:

Student Name
Book Number
Loan Date

This would presume that no student would check out the same book twice
on the same day. I'm not sure that's a sefe assumption, but given the
columns you show here, that's the closest thing I can see to something
that uniquely identifies a Loan.

Because of this, I'm thinking what you have is possibly a table design
problem. The ability to associate a Fine with a specific Loan is
definitely dependent on the fundamental design of the tables.

You may need to disclose more details of your table design if you feel
it is already adequate for the task, or you may want to get some
guidance on making it functional if it is not.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
my table relationships are

STUDENT LOAN INSTANCE BOOK

Student Name Student Name Book Number ISBN
Age Book Number ISBN Author
Class Loan Date Instance Number Genre
Loan Due Department
Loan Returned Age Group
Fine Publisher
Book Title

From studying my SQL view can you see what I need to do
now? Currently in the student table the key field is
student name, in the loan table I have a composite key
which is Student name and book number in instance the
primary key is Book Number and ISBN is the foreign key
and finally in Book ISBN is my primary key.

My query works ok [does what I want it to do but
replicates each entry five times why is this?

Can you help?
-----Original Message-----
Dear Marko:

I've edited your query a bit to study it:

SELECT L.[Student Name], L.[Book Number], L.[Loan Date],
DateAdd("m", 1, [L,Loan Date]) +
IIf(Weekday(DateAdd("m", 1, L.[Loan Date]),2) < 6, 0, 8 -
Weekday(DateAdd("m", 1, L.[Loan Date]), 2)) AS [Loan Due],
L.[Loan Returned], DateDiff("d", L.[Loan Due], L. [Loan Returned])
AS DaysLate, F.finAmount
FROM Loan L, Fines F;

One problem would be that you do not have a JOIN between Loan and
Fines. Now, how this should be done depends on how your database is
constructed. There would need to be a unique key (perhaps the Primary
Key) to Loan on which to base the relationship between these two
tables. I would suggest perhaps that might be:

Student Name
Book Number
Loan Date

This would presume that no student would check out the same book twice
on the same day. I'm not sure that's a sefe assumption, but given the
columns you show here, that's the closest thing I can see to something
that uniquely identifies a Loan.

Because of this, I'm thinking what you have is possibly a table design
problem. The ability to associate a Fine with a specific Loan is
definitely dependent on the fundamental design of the tables.

You may need to disclose more details of your table design if you feel
it is already adequate for the task, or you may want to get some
guidance on making it functional if it is not.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


My SQL is - SELECT Loan.[Student Name], Loan.[Book
Number], Loan.[Loan Date], DateAdd("m",1,[Loan Date]) +IIf
(Weekday(DateAdd("m",1,[Loan Date]),2)<6,0,8-Weekday
(DateAdd("m",1,[Loan Date]),2)) AS [Loan Due], Loan. [Loan
Returned], DateDiff("d",[Loan Due],[Loan Returned]) AS
DaysLate, Fines.finAmount
FROM Loan, Fines;

can you help
Click to expand...

.
Click to expand...
 
Dear Marko:

Perhaps this is some of what you need:

SELECT L.[Student Name], L.[Book Number], L.[Loan Date],
DateAdd("m", 1, [L,Loan Date]) +
IIf(Weekday(DateAdd("m", 1, L.[Loan Date]),2) < 6, 0, 8 -
Weekday(DateAdd("m", 1, L.[Loan Date]), 2)) AS [Loan Due],
L.[Loan Returned], DateDiff("d", L.[Loan Due], L.[Loan Returned])
AS DaysLate, F.finAmount
FROM Loan L,
INNER JOIN Fines F ON F.STUDENT = L.STUDENT
AND F.LOAN = L.LOAN AND F.INSTANCE = L.INSTANCE
AND F.BOOK = L.BOOK

That is, set equijoins on the 4 columns that make up the relationship.

I'm thinking this may not be all you were missing, but it may be a big
piece of it.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


my table relationships are

STUDENT LOAN INSTANCE BOOK

Student Name Student Name Book Number ISBN
Age Book Number ISBN Author
Class Loan Date Instance Number Genre
Loan Due Department
Loan Returned Age Group
Fine Publisher
Book Title

From studying my SQL view can you see what I need to do
now? Currently in the student table the key field is
student name, in the loan table I have a composite key
which is Student name and book number in instance the
primary key is Book Number and ISBN is the foreign key
and finally in Book ISBN is my primary key.

My query works ok [does what I want it to do but
replicates each entry five times why is this?

Can you help?
-----Original Message-----
Dear Marko:

I've edited your query a bit to study it:

SELECT L.[Student Name], L.[Book Number], L.[Loan Date],
DateAdd("m", 1, [L,Loan Date]) +
IIf(Weekday(DateAdd("m", 1, L.[Loan Date]),2) < 6, 0, 8 -
Weekday(DateAdd("m", 1, L.[Loan Date]), 2)) AS [Loan Due],
L.[Loan Returned], DateDiff("d", L.[Loan Due], L. [Loan Returned])
AS DaysLate, F.finAmount
FROM Loan L, Fines F;

One problem would be that you do not have a JOIN between Loan and
Fines. Now, how this should be done depends on how your database is
constructed. There would need to be a unique key (perhaps the Primary
Key) to Loan on which to base the relationship between these two
tables. I would suggest perhaps that might be:

Student Name
Book Number
Loan Date

This would presume that no student would check out the same book twice
on the same day. I'm not sure that's a sefe assumption, but given the
columns you show here, that's the closest thing I can see to something
that uniquely identifies a Loan.

Because of this, I'm thinking what you have is possibly a table design
problem. The ability to associate a Fine with a specific Loan is
definitely dependent on the fundamental design of the tables.

You may need to disclose more details of your table design if you feel
it is already adequate for the task, or you may want to get some
guidance on making it functional if it is not.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


My SQL is - SELECT Loan.[Student Name], Loan.[Book
Number], Loan.[Loan Date], DateAdd("m",1,[Loan Date]) +IIf
(Weekday(DateAdd("m",1,[Loan Date]),2)<6,0,8-Weekday
(DateAdd("m",1,[Loan Date]),2)) AS [Loan Due], Loan. [Loan
Returned], DateDiff("d",[Loan Due],[Loan Returned]) AS
DaysLate, Fines.finAmount
FROM Loan, Fines;

can you help
Click to expand...

.
Click to expand...
Click to expand...
 
Back
Top