Microsoft Access Help

[Duchess Jack]

Alright. I am super familiar with excel, but am just now teaching myself access.

 

I am putting together a catalogue of the retain samples we pull all throughout our manufacturing process. I've the following catagories.

 

Freezer, Rack, Box, Row, Column, Run, Process Step and Lot.

 

We had a problem where it would take the department who does this weeks to find the vials they needed so I catalogued and orginized around 20K vials so they can easilly be found.

 

Vials are put into retain all the time and I have a form to take care of this but I still need to database to do a bit more.

 

I need it to be able to run a report once a week to show all of our empty 'spots'. We've 26 racks per freezer. There are 6 boxes per rack and 81 spots (nine columns and nine rows) per box. Easy enough. The database currently has all of a freezer entered with empty spots being labeled as 'empty' in the Process Step field. I am hoping that the department can print this report out once per week and cross off the empty spots on the report as they are filled.

 

So... my issue is this. Is there any way to 'remove' a vial from the database (actually I'd love to keep it in the database for tracking purposes) when it is delivered to QC without having to find it and physically remove the entry? Ideally, I'd like to be able to pull up the vial in a manner where I can quickly add some info to it (who it was delivered to, when was it delivered, who delivered it, etc) and leave the info in the database. Once done, I'd need the spot to be marked as empty to allow the newly opened spot show up on the report.

 

And once the spot is marked as 'empty' is there any way when one of the operators wants to add a vial to that spot that he/she can just pull up the location in a form and plug in the needed info rather than running a search or sort to find that spot via the table view and change it manually? Is there any way for access to see spots as exclusive? (eg - there can only be one freezer 18, rack B, box 2, row 5, column B )

 

I might not be explaining myself well at all.

Edited May 14, 2009 by Duchess Jack

[Duchess Jack]

a thought I had....

 

is there a way to run a report where when there are two, three or four entries for the same location that the report only looks at the most recent?

 

The system is designed so when a vial needs to be pulled the user goes to a pivot table and selects a run, lot and process step. This should tell them exactly where the vial is so it can be pulled. Once pulled and delivery information is entered I am hoping that the user can than enter that location a second time into the form (freezer, rack, box, row, column) and the word 'empty' in the process step field. I can see then running a report that looks for the most recent entry for every location and then lists all of those entries that has 'empty' in the process step. So I might have had a vial in a spot - pulled it - re-entered the spot as empty - filled the spot again and re-entered it as filled only to pull it and enter it as empty again. So the same location might be listed in the database four times. Can a sort only address the most recent entry for every spot if the entries for similar spots are all over the place? I just have no idea how this program works.

[Big Country]

Yes.. all of those things can be done to the best of my knowledge.

 

Without being actually involved, I'm not sure I can really help, and some of the stuff may be above my level of experience.

 

Let me chew on this a bit, PM me if I don't get back to it in a day or so, and hopefulyl someone with a bit mroe Access experience can help out.

 

Essentially what you need are tables for each vial, each location, a table that tracks what vial is at what location, a table for storing notes on each vial (maybe even a note on each location), a query to pull the most recent status only for each location (and probably also for each vial) and then to tie this to a few reports.

 

It would give you a real time look at each freezer as well as the status of each vial with a status/location history for each.

 

Definitely doable.

[Kid Cid]

Yes, it's not like a spread sheet at all. That being said, a DB can do all of what you're asking and more. The key though will be designing the database itself in such a way that queries and manipulations are quick and don't bog things down as well as minimize data storage.

 

If as you say, you plan on retaining historical data, then you will want some way to archive things as well because otherwise, the database will continue to grow, thereby slowing things down. Not terribly difficult, but something that needs to be part of the design up front.

 

I can walk you through the process of designing and building this thing and assist with programming and such. Feel free to do it here or PM me.

 

For this to be successful, it needs to end up being as complete as possible before you use it.. Access databases often take on a life of their own, needing constant care and feeding. Following a software design plan will minimize the amount of tweaking you'll have to do further on down the line. I'm pretty sure you don't want to spend more time working on this thing than you do on your primary job.

 

As such, you should probably follow these steps to produce the final product.

 

Functional Requirements - In detail, what does this DB need to do? Data entry, track vials, report on location, etc. The more you can put down here, the better off you will be.

Data Requirements - Exactly what data will need to be stored to support the Functional Requirements?

Data Design - How will the tables be structured to hold the required data ?

Functional Design - A general design of the interface and workflow including archiving.

Report Design - Detailed report output designs including SQL to pull the appropriate data from the database.

 

That's the hard part. Once you get all that put together, the coding and building of the database is pretty simple in comparison. With all that being said, you probably have 2/3 of this already in your head, it's just a matter of writing it down.

[BiggieFries]

Yes, it's not like a spread sheet at all. That being said, a DB can do all of what you're asking and more. The key though will be designing the database itself in such a way that queries and manipulations are quick and don't bog things down as well as minimize data storage.

 

If as you say, you plan on retaining historical data, then you will want some way to archive things as well because otherwise, the database will continue to grow, thereby slowing things down. Not terribly difficult, but something that needs to be part of the design up front.

 

I can walk you through the process of designing and building this thing and assist with programming and such. Feel free to do it here or PM me.

 

For this to be successful, it needs to end up being as complete as possible before you use it.. Access databases often take on a life of their own, needing constant care and feeding. Following a software design plan will minimize the amount of tweaking you'll have to do further on down the line. I'm pretty sure you don't want to spend more time working on this thing than you do on your primary job.

 

As such, you should probably follow these steps to produce the final product.

 

Functional Requirements - In detail, what does this DB need to do? Data entry, track vials, report on location, etc. The more you can put down here, the better off you will be.

Data Requirements - Exactly what data will need to be stored to support the Functional Requirements?

Data Design - How will the tables be structured to hold the required data ?

Functional Design - A general design of the interface and workflow including archiving.

Report Design - Detailed report output designs including SQL to pull the appropriate data from the database.

 

That's the hard part. Once you get all that put together, the coding and building of the database is pretty simple in comparison. With all that being said, you probably have 2/3 of this already in your head, it's just a matter of writing it down.

 

+1

 

Also if you're planning on having this grow with archived data and write an actual application that does most of the work for you (ie. an interface that interacts with the database) I would probably stay away from Access if there is a lot of data. I would download MySQL or some other free database. Microsoft and Oracle both have their free versions as well (with limitations of course, but these might be overkill for your use).

 

Just something else to think about.

[T_bone65]

Yes, it's not like a spread sheet at all. That being said, a DB can do all of what you're asking and more. The key though will be designing the database itself in such a way that queries and manipulations are quick and don't bog things down as well as minimize data storage.

 

If as you say, you plan on retaining historical data, then you will want some way to archive things as well because otherwise, the database will continue to grow, thereby slowing things down. Not terribly difficult, but something that needs to be part of the design up front.

 

I can walk you through the process of designing and building this thing and assist with programming and such. Feel free to do it here or PM me.

 

For this to be successful, it needs to end up being as complete as possible before you use it.. Access databases often take on a life of their own, needing constant care and feeding. Following a software design plan will minimize the amount of tweaking you'll have to do further on down the line. I'm pretty sure you don't want to spend more time working on this thing than you do on your primary job.

 

As such, you should probably follow these steps to produce the final product.

 

Functional Requirements - In detail, what does this DB need to do? Data entry, track vials, report on location, etc. The more you can put down here, the better off you will be.

Data Requirements - Exactly what data will need to be stored to support the Functional Requirements?

Data Design - How will the tables be structured to hold the required data ?

Functional Design - A general design of the interface and workflow including archiving.

Report Design - Detailed report output designs including SQL to pull the appropriate data from the database.

 

That's the hard part. Once you get all that put together, the coding and building of the database is pretty simple in comparison. With all that being said, you probably have 2/3 of this already in your head, it's just a matter of writing it down.

 

I guess Whomper need not reply now.

 

Very sage advice here from the master

Edited May 15, 2009 by T_bone65

[Kid Cid]

+1

 

Also if you're planning on having this grow with archived data and write an actual application that does most of the work for you (ie. an interface that interacts with the database) I would probably stay away from Access if there is a lot of data. I would download MySQL or some other free database. Microsoft and Oracle both have their free versions as well (with limitations of course, but these might be overkill for your use).

 

Just something else to think about.

 

I agree to a point. There are other considerations such as a server to house MySQL or other database where an Access DB can just be thrown on a shared drive (assuming a networked environment). They would also require much more programming experience and the front end would have to be written from scratch in VB or Java where everything is self contained in Access. I'm guessing that this would be biting off more than they want to at this time.

[Big Country]

Just go with kid Cid... I'm just an amateur at this compared to him...

[westvirginia]

Just go with kid Cid... I'm just an amateur at this compared to him...

 

+1

[Duchess Jack]

thanks everybody. Cid... I will compose something in the next couple days to send to you just to see if I am on the right track. Thanks again.