Insert into where

[jzwp11]

It is hard to help without knowing more details about your table structure. How are the tables su_file_checkou, su_file and su_box_location related?

[Richie27]

Hey sorry dude. Got there in the end..........asleep at the wheel today................. no need to reply....cheers.

Oh and apologies for my lack of description! I guess by the time I get to this forum I am frustrated and inclined to abbreviate which probably isn't that considerate!

[jzwp11]

Glad to hear that you got it worked out. Good luck with your project.

[Richie27]

Hey thanks! It's nearly finished now - or so I keep thinking!

Boss wanted me to do some stuff that required unforseen VBA which I haven't much of a clue about.

Seems you can't get away from it though!

Thanks again!

[jzwp11]

You're welcome. I find that a database never seems to ever be "done". Once users start using it, they always want to have the database do more.

[Richie27]

Hello again.

Was just wondering. You know how I have the file table (1) and the checkout table (many) when in fact any instance of checkout could involve multiple instances of file. Should I have an intermediary table to adhere to normalization and are there any pitfalls to leaving it as it is?

Thank ya!

Oh and instead of having a checkout table, I now have an events table of which checkout is one of several events - so should be fun trying to make your code fit my new and exiting purposes!

lol

[jzwp11]

You know how I have the file table (1) and the checkout table (many) when in fact any instance of checkout could involve multiple instances of file. Should I have an intermediary table to adhere to normalization and are there any pitfalls to leaving it as it is?

Are you basically saying that you have an event and many files can be associated with the event and a file can also appear in many events? If so, then you have a many-to-many relationship which requires a junction table (I assume that is what you mean by intermediary table).

tblEvents
-pkEventID primary key, autonumber
-dteEvent (date of event)
-fkEventTypeID foreign key to tblEventType (check out, check in etc.)


tblFiles
-pkFileID primary key, autonumber
-txtFile

tblEventFiles (junction table)
-pkEventFileID primary key, autonumber
-fkEventID foreign key to tblEvents
-fkFileID foreign key to tblFiles

[Richie27]

That's pretty on the nose with the old assumptions there. It actually amazes me how accurate your assumptions are inclined to be - considering I don't always give enough information.

Thanks a billion.

[Richie27]

That really was a nice piece of code you originally gave me! I reckon I will be using it quite a bit! (don't worry - I'll stick your name in the credits)

[jzwp11]

You're welcome. Good luck with your project.

[Richie27]

Just found a bug! and thought you could help since you are intimately acquainted with this part of my project.

Previously you helped me with code for checking files out by box and you put in a bit where the files were only checked out if they were not checked out before.

I wrote a little update query to checkback all of the files in the box - but I just realised there is a big problem as I would be even checking back files that perhaps shouldn't be checked back yet as they may have been checked out by a whole other person. I have attached my new db schema for a bit of clarity.

I have now changed the file checkout table to file actions as I am using it for many different actions now.

If it's too much hassle, is okay. You've helped me loads already and I really appreciate it.

[Richie27]

Hmmm.......maybe can use the actionDate and the actionReason as the unique identifiers........unless your wise self has a better idea, I shall run with that!! ;-)

[jzwp11]

If you know the person who checked out the items, all you would need to do is find those items they have checked out and update those records or a portion of those records. In other words, filter by the person returning the items. You might use a form that has a combo box based on the person followed by a multi-select list box of items they have checked out. You could then select the one or more items from that list to update.

[Richie27]

OOhhhh....thanks for your insight!

That'll be something to chew on for the afternoon!

Cheers!

[Richie27]

What's a multi-select list box? Much too them?