Library: New Loan Form has book duplicates (1 Viewer)

StrangePowers

New member
Local time
Today, 19:21
Joined
Apr 14, 2020
Messages
6
Hi there,

I've only started building a database in Access a few days ago, so I'm real new at this. Through Youtube videos though I've been able to at least get all tables and queries for my library database in order. The problem I have is with the Form for lending out books. I have a table with "Books", but I also have a Table with "Copies"; since we have multiple copies for some of the books.

In the Form for lending new books, I've made a combo box where users can select the book they want to rent. I changed the row source so it has the CopyID in one column, and then the Title, First Name and Surname of the Author in the second, by typing this in the Builder:

Titel: [QRY_BÜCHER_IM_HAUS]![Titel] & "; " & [QRY_BÜCHER_IM_HAUS]![Vorname] & " " & [QRY_BÜCHER_IM_HAUS]![Nachname]

(I write in german, since that's where I'm from. In case it's unclear: I take everything from the query "QRY_BÜCHER_IM_HAUS" or, "books in".

The combo box now shows multiple similar titles (all witht their own unique CopyID behind the scenes), since of course, the CopyID is the identifying factor here, so switching the Unique Values of the query to Yes won't make a difference here.

Is there a way for me to show just one copy of each Title, and then automatically choose the lowest number in the CopyID of this book still available (I won't be adding an actual CopyID to the actual physical books, as I think this would just be too much work and it would mean an extra hassle for the end users. I also thought about just adding a lable telling users to just pick one of the duplicate titles at random, but I'm sure there's a more sophisticated solution)?

Your input would be greatly appreciated. This is my first post to this forum, so if I did anything wrong please excuse me, I'll be happy to correct any mistakes.
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 18:21
Joined
Jan 14, 2017
Messages
18,186
Change your query to only show unique values.
In SQL view, it will begin with SELECT DISINCT …,
 

StrangePowers

New member
Local time
Today, 19:21
Joined
Apr 14, 2020
Messages
6
I'm sorry, like I said, I'm a real newbie at this, but isn't that the same as doing that in the Properties Field? Like I said, doing that doesn't change anything, because the CopyIDs are all unique.

I thought about not using the CopyID in the combo box, and instead just use the Title, but then I have no field to store the data, since the Loan Table (which is the record source for the form) doesn't have (and shouldn't need, in my opinion) a Title column.

I feel like there are multiple solutions to this problem. Maybe I SHOULD add a Title column to my Loan table, but I'm not sure if that conforms with normalisation rules. My best guess is that I should remember the value for later use, but I don't know what to do with that value then.

Thanks for replying!
 
Last edited:

plog

Banishment Pending
Local time
Today, 13:21
Joined
May 11, 2011
Messages
11,611
You don't a Copies table:

...(I won't be adding an actual CopyID to the actual physical books,

Your book titles are fungible (https://en.wikipedia.org/wiki/Fungibility). If you have 5 'Mein Kampfs' (that's the only German title I know, blame history not me) you don't care which specific Mein Kampf is loaned out, you simply care that a Mein Kampf in general was loaned. Your database is set up to track books specifically, but you just need it to track generally.

The simplest way would be to add a numeric 'Copies' field to tblBooks to identify the number of items you have. You would no longer need the Copies table. To see if a book was in stock, you would add up all the loaned out ones and subtract that from the Copies field in tblBooks to make sure that number was greater than 0.
 

StrangePowers

New member
Local time
Today, 19:21
Joined
Apr 14, 2020
Messages
6
That sounds great! It would mean quite a lot of reworking of my other queries and tables, but that's all in the game.

Thank you very much for your reply. I would have never thought of that solution. You reckon thinking about databases like this will get easier over time?
 

plog

Banishment Pending
Local time
Today, 13:21
Joined
May 11, 2011
Messages
11,611
You reckon thinking about databases like this will get easier over time?

Yes, and no. You definitely apply things you learn all the time and that means avoiding issues like this because you have experience in handling them. But, I still encounter new things, apply a solution I think is right, but later learn its sub-optimal and go back and do it right.
 

Micron

AWF VIP
Local time
Today, 14:21
Joined
Oct 20, 2018
Messages
3,476
I see you've used that word elsewhere. Take this how you like, but there are/were posts about being too familiar, twisting people's user names and the like. Such things can be regional as well. There was a time when locally, 'dude' was a derogatory thing to call someone. Just saying...
Please ignore these comments if you think they're of no value.
 

isladogs

MVP / VIP
Local time
Today, 18:21
Joined
Jan 14, 2017
Messages
18,186
Dude, the thread is not for the advertisement.
Whilst I agree with Micron's comment, I will also point out the the post with the inappropriate link has since been deleted as has the account as a probable spammer
 

plog

Banishment Pending
Local time
Today, 13:21
Joined
May 11, 2011
Messages
11,611
I think this is next-level spamming. Check out StephenLewisrew's history:


Joined today, then said "Welcome to the forum" to 10 people, then reposted a spammers message but replied derogatorily to him ensuring the link still remained on the forum. I wonder the time difference between the original spammer and Stephen's reply.

I'm in awe actually.
 

Micron

AWF VIP
Local time
Today, 14:21
Joined
Oct 20, 2018
Messages
3,476
Most/none of those welcome posts have a link to anything, nor does his signature or anything below his avatar - so it's very odd.
 

isladogs

MVP / VIP
Local time
Today, 18:21
Joined
Jan 14, 2017
Messages
18,186
For info, I've already flagged those posts as being from a potential spammer. There have been a lot of spammers trying different tactics in the last few weeks. We catch them all, usually fairly quickly, but reporting suspicious posts is the best way to get the attention of mods
 

Micron

AWF VIP
Local time
Today, 14:21
Joined
Oct 20, 2018
Messages
3,476
Maybe you should post a thread in the VIP lounge explaining what to look for? I wouldn't report posts like that because I figure it's the wrong thing to do. If it's what is wanted, then np. However, I'm not sure what sort of things you guys want to look at but you can't make that public, right? Hence the vip lounge.
 

plog

Banishment Pending
Local time
Today, 13:21
Joined
May 11, 2011
Messages
11,611
Post #7 of this thread still has the spam link.
 

Users who are viewing this thread

Top Bottom