help with database design required

Hello Everyone,

I have a system that has to track the movement of tools from in and out of 
the stores.

Borrower Details:
tblname: id-PK,name,department

tbltoolmaster
ToolID-PK
Toolno
ToolDesc
Location

tbltooltransaction
Tranid-PK
TrantoolID - FK
NameID - FK
DateTaken
DateReturned

what i am attempting to do is for every tool borrowed from the tool store i 
need to be able to record the date taken and date returned.

If the date returned is not null - Tool is Available

Once the tool is burrowed by a person A on a given date than the status 
should be updated to loaned and when he returns it should be updated to 
available.

How do i do this .Do i store status in  tbltoolmaster. how to update this 
with every transaction ?

In the end of the day i should be able to account for how many tools are 
loaned and how many are available in the store and who burrowed them.

how do i accomplish this. I would really appreciate your inputs .

thanks for your patience in reading this post.


0
Utf
11/7/2007 1:43:01 PM
access.formscoding 7493 articles. 0 followers. Follow

5 Replies
476 Views

Similar Articles

[PageSpeed] 35

On Nov 7, 7:43 am, vandy <va...@discussions.microsoft.com> wrote:
> Hello Everyone,
>
> I have a system that has to track the movement of tools from in and out of
> the stores.
>
> Borrower Details:
> tblname: id-PK,name,department
>
> tbltoolmaster
> ToolID-PK
> Toolno
> ToolDesc
> Location
>
> tbltooltransaction
> Tranid-PK
> TrantoolID - FK
> NameID - FK
> DateTaken
> DateReturned
>
> what i am attempting to do is for every tool borrowed from the tool store i
> need to be able to record the date taken and date returned.
>
> If the date returned is not null - Tool is Available
>
> Once the tool is burrowed by a person A on a given date than the status
> should be updated to loaned and when he returns it should be updated to
> available.
>
> How do i do this .Do i store status in  tbltoolmaster. how to update this
> with every transaction ?
>
> In the end of the day i should be able to account for how many tools are
> loaned and how many are available in the store and who burrowed them.
>
> how do i accomplish this. I would really appreciate your inputs .
>
> thanks for your patience in reading this post.

You can change the status of the record in tbltoolmaster in the
AfterUpdate event of tbltooltransaction.  Your fieldnames are
confusing.  I will use some of my own in the following example...
BTW, you will have to create a boolean field called "Available".  This
assumes that me.toolno is a numeric value.

dim db as dao.database
set db = currentdb()
db.execute "UPDATE tbltoolmaster SET [Available]=" & format(NOT
isnull(DateReturned)) & WHERE [ToolNo] = " & me!toolno
db.close
set db=nothing

0
OldPro
11/7/2007 3:06:30 PM
Your table design looks ok.
Don't store the status! It is already indicated by the state of your 
transaction table i.e. if any tool has no return date then it is not 
available.

-Dorian

"vandy" wrote:

> Hello Everyone,
> 
> I have a system that has to track the movement of tools from in and out of 
> the stores.
> 
> Borrower Details:
> tblname: id-PK,name,department
> 
> tbltoolmaster
> ToolID-PK
> Toolno
> ToolDesc
> Location
> 
> tbltooltransaction
> Tranid-PK
> TrantoolID - FK
> NameID - FK
> DateTaken
> DateReturned
> 
> what i am attempting to do is for every tool borrowed from the tool store i 
> need to be able to record the date taken and date returned.
> 
> If the date returned is not null - Tool is Available
> 
> Once the tool is burrowed by a person A on a given date than the status 
> should be updated to loaned and when he returns it should be updated to 
> available.
> 
> How do i do this .Do i store status in  tbltoolmaster. how to update this 
> with every transaction ?
> 
> In the end of the day i should be able to account for how many tools are 
> loaned and how many are available in the store and who burrowed them.
> 
> how do i accomplish this. I would really appreciate your inputs .
> 
> thanks for your patience in reading this post.
> 
> 
0
Utf
11/7/2007 3:10:01 PM
Thanks for your reply.

My tool no is numeric like ET-2 how should i change this code.

Also you want the available feild to be a yes/no feild in the tblmaster 
table is that correct.
thanks


"OldPro" wrote:

> On Nov 7, 7:43 am, vandy <va...@discussions.microsoft.com> wrote:
> > Hello Everyone,
> >
> > I have a system that has to track the movement of tools from in and out of
> > the stores.
> >
> > Borrower Details:
> > tblname: id-PK,name,department
> >
> > tbltoolmaster
> > ToolID-PK
> > Toolno
> > ToolDesc
> > Location
> >
> > tbltooltransaction
> > Tranid-PK
> > TrantoolID - FK
> > NameID - FK
> > DateTaken
> > DateReturned
> >
> > what i am attempting to do is for every tool borrowed from the tool store i
> > need to be able to record the date taken and date returned.
> >
> > If the date returned is not null - Tool is Available
> >
> > Once the tool is burrowed by a person A on a given date than the status
> > should be updated to loaned and when he returns it should be updated to
> > available.
> >
> > How do i do this .Do i store status in  tbltoolmaster. how to update this
> > with every transaction ?
> >
> > In the end of the day i should be able to account for how many tools are
> > loaned and how many are available in the store and who burrowed them.
> >
> > how do i accomplish this. I would really appreciate your inputs .
> >
> > thanks for your patience in reading this post.
> 
> You can change the status of the record in tbltoolmaster in the
> AfterUpdate event of tbltooltransaction.  Your fieldnames are
> confusing.  I will use some of my own in the following example...
> BTW, you will have to create a boolean field called "Available".  This
> assumes that me.toolno is a numeric value.
> 
> dim db as dao.database
> set db = currentdb()
> db.execute "UPDATE tbltoolmaster SET [Available]=" & format(NOT
> isnull(DateReturned)) & WHERE [ToolNo] = " & me!toolno
> db.close
> set db=nothing
> 
> 
0
Utf
11/7/2007 4:53:02 PM
See my reply. You should not be storing the status, it is superfluous!

-Dorian

"vandy" wrote:

> 
> Thanks for your reply.
> 
> My tool no is numeric like ET-2 how should i change this code.
> 
> Also you want the available feild to be a yes/no feild in the tblmaster 
> table is that correct.
> thanks
> 
> 
> "OldPro" wrote:
> 
> > On Nov 7, 7:43 am, vandy <va...@discussions.microsoft.com> wrote:
> > > Hello Everyone,
> > >
> > > I have a system that has to track the movement of tools from in and out of
> > > the stores.
> > >
> > > Borrower Details:
> > > tblname: id-PK,name,department
> > >
> > > tbltoolmaster
> > > ToolID-PK
> > > Toolno
> > > ToolDesc
> > > Location
> > >
> > > tbltooltransaction
> > > Tranid-PK
> > > TrantoolID - FK
> > > NameID - FK
> > > DateTaken
> > > DateReturned
> > >
> > > what i am attempting to do is for every tool borrowed from the tool store i
> > > need to be able to record the date taken and date returned.
> > >
> > > If the date returned is not null - Tool is Available
> > >
> > > Once the tool is burrowed by a person A on a given date than the status
> > > should be updated to loaned and when he returns it should be updated to
> > > available.
> > >
> > > How do i do this .Do i store status in  tbltoolmaster. how to update this
> > > with every transaction ?
> > >
> > > In the end of the day i should be able to account for how many tools are
> > > loaned and how many are available in the store and who burrowed them.
> > >
> > > how do i accomplish this. I would really appreciate your inputs .
> > >
> > > thanks for your patience in reading this post.
> > 
> > You can change the status of the record in tbltoolmaster in the
> > AfterUpdate event of tbltooltransaction.  Your fieldnames are
> > confusing.  I will use some of my own in the following example...
> > BTW, you will have to create a boolean field called "Available".  This
> > assumes that me.toolno is a numeric value.
> > 
> > dim db as dao.database
> > set db = currentdb()
> > db.execute "UPDATE tbltoolmaster SET [Available]=" & format(NOT
> > isnull(DateReturned)) & WHERE [ToolNo] = " & me!toolno
> > db.close
> > set db=nothing
> > 
> > 
0
Utf
11/7/2007 6:17:01 PM
On Nov 7, 12:17 pm, mscertified <rup...@tigerlily.com> wrote:
> See my reply. You should not be storing the status, it is superfluous!
>
> -Dorian
>
> "vandy" wrote:
>
> > Thanks for your reply.
>
> > My tool no is numeric like ET-2 how should i change this code.
>
> > Also you want the available feild to be a yes/no feild in the tblmaster
> > table is that correct.
> > thanks
>

To correct it, add single quotes like so:

dim db as dao.database
set db = currentdb()
db.execute "UPDATE tbltoolmaster SET [Available]=" & format(NOT
isnull(DateReturned)) & WHERE [ToolNo] = ' " & me!toolno & " ' "
db.close
set db=nothing

Dorian is correct in that it is a general rule of thumb to not
duplicate values in a database.  It creates the potential for
competing values when one is in error.  However, in this case the
choice is between having an explicit field that shows the current
status for each tool, or inferring the status from whether the return
date is missing from the latest record in a transaction table.  The
later is more complicated and has the potential of error if any of the
assumptions for any reason become false, either presently or with any
future code additions.  I would choose the explicit [Available] field;
just make sure it gets updated properly.  And yes, it should be a Yes/
No field.

0
OldPro
11/8/2007 2:31:47 PM
Reply:

Similar Artilces:

Move MultiUser Access Database onto Terminal Services
Can anyone tell me if there are any inherant problems running a multiuser (max 15 at one time) Access Database over terminal services? I'm worried this may give file lock issues, but by using terminal services it would revolutionise the access to the system. Currently the database is split into a front and back end and sat on a shared network. All users have the mde for the front end on their hard drives and this accesses the backend data on the server via the linked tables. Any help on whether terminal services would work would be much appreciated!! Dave Systems Developer There was an...

Query a Access database that has a module from Excel
I'm very new to most SQL, VB, etc. and I think this is pretty simple so I hope someone can help!!! I have a database that I store all of my research data in, then I query it in Excel to do analysis. I've put in a function that I found from the Microsoft KnowledgeBase to find the minimum value across various fields in ONE record. However, when I try to query the database now it gives me a "Undefined function in expression" message. I've read around and now realize that the modules aren't part of the Microsoft Jet Database (or something like that!!) and thus c...

Help Me!!!
I used this forum back in May this year to find out how to make a drop down list on which i can click on the name and it takes me straight to that worksheet. This is working fine but i know need to add more names to the list. I think i was given a formula to out in somewhere but i cannot remember what it was or how to view it. Can someone please tell me how i can do this. Can anyone that replies send it in simplified form. Im only 13 so im not sure of all the theqnical terms for things. Can Someone Help me please!! -- beng120 ---------------------------------------------------------------...

LOST FILE HELP
hey there, i've been using excel 2000 at my work for a while, and i usually open sheets directly from the email. i accidentally closed excel, so i reopend excel and expected to be able to open the excel sheet from the file menu, but it couldn't find the file. i was wondering if someone knows how to get that file back? tia I do not believe those files you open in an email message will show up in the Recently used file list. These files exist in a temporary folder (when you open them from the email message). So you have to save them somewhere on your HD. You should be able to get t...

Help with populating cells using formulas or macros
Hi, I have a problem that I've spent hours working on but even Microsof customer support has not been any help. I'm trying to work with a large data file where the data is groupe into two types. Let's call them A & B. For each day of data, I have tw data points, A & B. These are entered next to each other in the sam row. It looks something like this: A1 B1 A2 B2 A3 B3 ... That is, for Day 1, I have data point A and data point B. I want to be able to easily group all the "A" data and all the "B" dat separately. That is, in a separate part of the w...

Chart repeats data when printed pls help !
I have a spreadsheet that consists of a long list of data on the left with a simple x-y scatter chart on the right - both list and chart are longer than one page, and therefore must be printed across several pages. When I print the spreadsheet, the chart on each page prints the first two rows of data from the beginning of the chart, so for example if the data runs from 1-100, the first page chart printout shows 1-20, the second page shows 1-2 then 13-20, the third page shows 1-2 then 23-30. Could it be something to do with excel printing out the x axis on each page, given that the x axis is ...

Error upon opening any word document or clicking on the help
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Hi, <br><br>I am getting an error each time I open up any word documents that I have previously saved and closes word automatically. Also if I just open word up and try going to help it closes down word with an error. <br><br>Here is the error I get when opening up Word. <br><br>Microsoft Error Reporting log version: 2.0 <br><br>Error Signature: <br> Exception: EXC_BAD_ACCESS <br> Date/Time: 2010-05-13 18:34:44 +0200 <br> Application Name: M...

2nd request
I recently installed Outlook 2003 on windows XP and cannot find a way to open multiple jpg attachments easily. My daughter has a new baby and constantly sends many .jpgs attached to one email. Previously, With Outlook 2000 I was able to highlight all the attachments and open them all at once. Now I have to open one, then go back to the email to open another, etc. Is there something I can do with Outlook 2003 to open them all at once? No, it is the default behavior. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert la...

Excel report help
I did a quick search on this topic and to be honest found that I know s little about this topic, that I was lost trying to understand what saw. So to be honest, I am looking for someone to basically hand hol me through this problem. A coworker has a report that she files once a month, this report is culmination of four Excel reports into one. The four reports we ar combining consist of a beginning of month inventory, end of mont inventory, cost of inventory, and product ordered and shipped. The problem I am running into is that none of the reports she is abl to pull from our database has a...

Help addressing the last cell
Here is my code: Dim LastRow As Long Dim LastCol As Long Dim LastCell As Range Dim intNumCols As Long Dim DataCells As Range 'Find the last row, column and last cell of the worksheet. LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column LastCell = ActiveSheet.Cells(LastRow, LastCol).Address Excel fails at the LastCell line. It tells me that the object is not defined, but clearly my variable is defined. Both LastRow and LastCol return the correct value. What is the problem? Thanks in ...

Current sessions, spam, smtp, relay
In the past week we have been getting a lot of spam mail. And just like all you admins out there.. GGGRRRRAAAAHHHHH!!! It's weird because they are coming and going from people that do business with us. Not only are they sending us spam, we are sending them spam as well. I checked to see if the relay settings were correct. Read http://support.microsoft.com/?id=310380 and set everything up as they said to do. I unchecked the "Allow all computers with successfully authenticate to relay, regardless ..." Now i don't know if that was the way they were sending spam but i also am log...

Eulogy speech help?
I am trying to figure out the best way to write a Eulogy for my favorite Uncle that just past away 2 days ago. Any tips, ideas or encouragement? I need advise ASAP. The funeral is tomorrow, Sat. May 15. Thanks for any suggestions. Sorry, but this isn't the right place to ask for help on document contents; this newsgroup deals with the technical aspects on using Word. -- Stefan Blom Microsoft Word MVP "Melonhead58" <Melonhead58@discussions.microsoft.com> wrote in message news:41D3BC38-FFC1-4668-A9AC-C3C3440B311A@microsoft.com... >I am trying ...

COUNT IF.... Feature, please help ASAP
Does anyone out there know if it is possible to have the count if feature can retrieve and count data from more than column, ie; I want it to count all of my X data in column A and then out of all of those X's in column, how many of those belong to Y in column C. Thanks greatly appreciated, this is for a major spreadhseet I'm producing for work and I want to impress my boss. BR Hi, Use =SUMPRODUCT(($A$2:$A$7="X")*(C2:C7="Y")) Regards Govind. Count If wrote: > Does anyone out there know if it is possible to have the count if feature can > retrieve ...

No info coming up in Publisher 2007 Help
Just purchased Publisher2007. When I press F1 for Help, another screen comes up for Publisher Help but nothing listed below. Can enter in search field, press go -- nothing comes up. This appears for both Microsoft Online and Offline options. What do I need to do flosew wrote: > Just purchased Publisher2007. When I press F1 for Help, another > screen comes up for Publisher Help but nothing listed below. Can > enter in search field, press go -- nothing comes up. > > This appears for both Microsoft Online and Offline options. > > What do I need to do ================...

How can I add a warning meassage to a macros designed to delete/insert a Row?
every time when a row (or a cell in a certain range) in a shared worksheet is to be deleted or inserted, I want a warning message. But how? Thanks. Zean ...

Design for a service business
What is the best way to design a database for my service business? Invoices are sent out just once a month and the customers pretty much stays the same from month to month. Each customer has a customer number and each invoice has an invoice number (the invoice number is really just an abbreviation of the month and year attached to the customer number). Should I have a seperate table for each month or do it some other way? Presently, using different database software, I create a different database file for each month. Many thanks. 1st off, and I don't mean to scare you, but de...

Pls help me !
Hello there! Greeting to all. Problems with creating a Bar Chart like this. (Pls refer to file attached; BarChrat123.xls) How can I have 2 bars(TypeA & TypeB) on a single month (eg: Jan-Jul and all the firgures or number of items within the portion of the bar? NOTE: BarChart123.xls only show a single bar with the details of item in a month. Do I need to modify my table? or something else?!! Thank you very much davidiew Reply to this email address: rentashop@hotmail.com Thanks -- Message posted from http://www.ExcelForum.com ...

Open Query Design In SQL view
Is it possible to open the query design window directly to the SQL View in existing queries or in creating new queries? Presently it is two or three clicks to get there. If the existing query was saved in SQL view, opening it subsequently in Design view should open it in the SQL view. I'm not aware of any way to have new queries automatically open in SQL view. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "tcb" <tbenson@mn.rr.com> wrote in message news:1190637257.981140.239390@19g2000hsx.googlegroups.com... > Is it possible t...

HELP -- XP Address Book: No Microsoft Solution Listed
What is the plain and step-by-step simple solution for this very annoying error: "A new entry could not be added. You must have a personal address book (PAB) to create an entry. Add a PAB to your profile using the E-mail accounts command on the Tools Menu." It doesn't allow me to create one!!! Even when I do this: right click on my contacts folder, click on 'Properties', then 'Outlook address book' tab. The option 'show this folder as an email address book' is blanked out and not available for me to check or uncheck. HELP You first. What is it t...

Can't stop Excel help autotile :-(
Here is an export of (what should be) the relevant registry setting: [HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Common\HelpViewer] "DxExpanded"=dword:000000f3 "ExpandedState"=dword:00000001 "FloatingSize"=dword:021302c0 "FloatingPosition"=dword:01340035 "DockedSize"=dword:00000277 "DockedState"=dword:00000002 "IsFloating"=dword:00000001 But both my Excel and my VBA editor windows still resize when I open the help. Can anyone please tell me what I've done wrong? ...

Graphing Database Growth Rate
Hello, all -- I have a job that grabs the size of each SQL Server database and log file size and saves one row per database into a table. I run this routine on a daily basis, so I basically grab the size each day and can monitor growth. The table data is below. What I would like to do is have a SQL Query setup to graph the data in Excel to show the growth rate of each database by week, month, year. I'm not sure the best way to do the SQL and the best way to do the graph. Since new databases are created all the time, new rows will appear in the output -- so creating specific graph...

mail merge labels from excel database
Can anyone give me the instructions to do a mail merge for labels from an excel database? Using Word with Excel as the data source is easiest, if you can ; you can find instructions here http://www.mvps.org/dmcritchie/excel/mailmerg.htm If Word n/a, post back with more details of your data layout. Rgds, Andy ...

How do I save data on a form to my database?
I am new to Access and was wondering how I would save a record from my forum to the database. I am using an unbound forum. Thanks adamskiii, Why have you created an "unbound" form to operate on a table? If you're new to Access, you've just made your project much more difficult than it needs to be. You'll need to create a button on your form that, when clicked, runs an Append query against your table, using the current values on the open form. -- hth Al Campagna Microsoft Access MVP 2007-2009 http://home.comcast.net/~cccso...

More Newbie help needed
(These are probably childs play for an experienced Exchange admin but I'm JUST starting with Exchange so any guidance is really appreciated!) Now that I have Exchange 2003 Server installed how do I do the followiing: 1) I have a user with three e-mail accounts: a) Company account: joe@domain.com b) Company account: info@domain.com c) Comcast personal mail: jsomebody@comcast.net He would like to get mail to all 3 accounts. How do I do that? 2) How do I redirect our email system on the web to send mail to our new exchange server? We are using a pay for service lik...

Modified Trasnpose
Hi, I have data stored in Table1 as such (Field names on row 1): Id - Type1 - Type2 123 - 0 - 50 456 - 10 - 100 789 - 50 - 0 And I would like to output it as such (Field names on row 1): Id - Type - Amount 123 - Type1 - 50 456 - Type1 - 10 456 - Type2 - 100 789 - Type1 - 50 I will have more the 20 Types in my actual table, so I can't just make compounded queries. Any help is appreciated, thanks! SELECT Id, "Type1" As Type, Type1 As Amount FROM MyTable UNION SELECT Id, "Type2", Type2 FROM MyTable UNION SELECT Id, "Type3", Type3 ...