Fishtank Database, Please Help !!!! (1 Viewer)

[david0121]

I'm new to access, This is my first database.

Previously I used excel to keep a list of what was happening with our Fish Tank. but with excel i wasn't able to keep an eye on stocks and losses.

So I decided it was time to have a go at Access to keep my fishtank logs, Currently I use the TBL_LOGS Notes to log a description for one of the following ie "Water Test" "Water Change" "Stock" "losses" "Maintenance".

I've been using a Log Form to keep the logs up to date, and a transaction form to look after the stock, I find myself having to type the same information twice.

Some log items ie Water Change does not use any stock, Where as New Stock, Losses and Maintenence will make changes to stock levels.

I'm at a loss on how to make changes to what i have to give me the options i'm looking for.

I have sofar "TBL_LOGS"
Key
Date
Time
Notes
Temperature
PH Range
Ammonia
Nitrite
Nitrate

TBL_Inventory
Id
Item
Description
Supplier Cost

TBL_TRANS
ID
Item
Transaction Type
Quantity

I've tried to attach a copy of database but keep getting an error

I look forward to any assistance

Kind Regards

David

 

[Privateer]

David,
Welcome to the database world. As you are finding out, setting up the tables can be the trickest part. You try to have fields that are unique to the subject of the table and relate it to other tables to avoid duplication. And by unique, I mean a one to one relationship. So with people, you have a FirstName, LastName, BloodType, Gender, BirthDate, DeathDate and that is it basically. You could have multiple hire and termination dates, so put that in another table along with the PersonID number. You could have multiple spouses, so that goes into another table. You have the basic idea, but you need to add the unique numbers from one table to the other to make the relationship. And this is done on the forms with combo boxes, but the fields need to be in the underlining table to accept the value of the combo box.

You are pretty close with the inventory table, but I would add a field to hold the number of items you have in stock, maybe StockTotal. And I would change the ID field to InventoryID, which should be an auto number field and you should have clicked on the key icon to make it the primary key. Every table should have a primary key, autonumber, and indexed, with no duplicates. When you add a primary key (PK) to another table it is called a foreign key (FK) and it is how you relate the two and get information together from two sources. This is how you prevent duplication.

The transaction table also needs some adjustment. Again, the ID field should be renamed to TransactionID to differenciate it from other primary key fields. Because, you also need to add InventoryID to this table. This way, when you select an inventory item (with a combo box) you get all the information from the inventory table in your transaction form. And for each transaction you will use a certain number of items, which can be subtracted from the inventory. You could also change Quantity to StockUsed to clear things up.

The logs table also needs some modification. As mentioned above you need a primary key for every table, so create a LogID field here. The date and time fields have to go. Those two words are "reserved words" in Access. Change Date to LogDate and get rid of time. In Access as well as Excel, the date and time are in the same field. The integer part of the date is the date, the decimal is the time. So today's date, 12/10/2011 in Excel, unformatted is 40,887 days since 1/1/1900, which is date 1. The time, works like this, .25 is 6am, .5 is noon, .75 is 6pm, etc. So when you build the log table, make sure LogDate is formatted as General Date to get both the date and time. Later, you can format the text boxes with the short date format to view just the date. The other fields look OK, but I would get in the habit of removing spaces in the field names; causes problems later.

To complete the lesson, the transaction type should probably be a separate table with its own PK and a description, like buy, sell, etc. Each type has its own PK number and it is this number you want to store in the transaction table. So you would have to change Transaction Type which you have as text, to TransactionTypeID, long integer. Now with a combo box on the form, you store the number in the table, not the text. Combo boxes are key to databases and you have to learn everything about them; no way around it. It is how you pick something by reading the text, but you store the number in the table. Later, when you run a query, you use this number to get the text back.

Anyway, that is a lot to digest. Let me know how you are coming and let me know if you need any help with combo boxes. Good luck.
Privateer

 

[david0121]

Privateer,
Thank You for all of the information, this was a massive help. I've made the necessary changes to the tables, and removed all of the spaces from the field names, Don't know why I did That. Hopefully today I will get a chance to look at the combo boxes.


All the best,
David


P.s I will keep you updated with the progress

 

[david0121]

Let me know how you are coming and let me know if you need any help with combo boxes. Good luck.
Privateer[/QUOTE]


This is what I have so far, the first four tables -: Employees, filter’s, suppliers and the transaction types, I have only given a brief description for these items. The other items I have listed in detail.

TBL_Employees
TBL_Filters
TBL_Suppliers
TBL_TransactionTypes


TBL_Inventory
Inventory ID (PK) auto number
InventoryItem Text
Description Text
Location Text
Supplier Number
Costs Currency
Reorder level Number
Target StockLevel Number
Attachments Attachment
Discontinued Yes/No
Comments Memo
Size In CM’S Number


TBL_Inventory Transactions
Transaction ID Auto Number
Transaction item Number
Employee Number
Transaction type Number
Transaction quantity Number
Created date date/time
PO Number text
Comments text

TBL_LOGS
LogKey (PK) Auto Number
LogDate Date/Time
Time Text (This Will be Removed)
Notes Text
Temperature Number
PHRange Number
Amonia Number
Nitrite Number
Nitrate Number


I have made the necessary changes as per your instructions, and removed spaces from the field names “don’t know why I did that”.
Earlier today I made changes to the log table and inserted a new field to display the transaction ID. At this point I get confused as to whether I should be using the transaction ID or the Inventory ID.

My attempt at what I thought to be the solution to my problem would be to create a new field for the transaction ID and select the transactions from the combo box to match the notes in the log file. Unfortunately I was only able to display numbers, not text. This made it almost impossible for me to match the log notes to the stock transactions previously created.

So far I have 268 records stored within the logs table of which I need to be able to relate the notes within this table to the stock transactions previously created.

Once again many thanks for your time, and comments, they are greatly appreciated.

All the best
David

 

[Privateer]

David,
Great work with the tables. Your confusion with the Inv/Trans is understandable. The answer is the transaction table will have the InventoryID in it, because each transaction has to have a part associated with it. So with that one number, (TID) you can get anything in the transaction table. Could you explain what the transaction item is?

Now, the transactions table should have its own PK for each record, and the PK from the employee table, and the PK from the inventory table, something like this. The EID and IID are referred to as foreign keys because they are in another table, not their own.

tblTransactions
TransactionsID, EmployeeID, InventoryID, CreatedDate, etc.

So on the transactions form, the Employee and Inventory are both combo boxes, where you want to view the Person/Part text but store the number. But you can't see the text so lets talk about combo boxes.

I will assume you are in the transactions form in design view and the control source for the form is the transaction table. Now, open the properties sheet for the employee combo box. On the data tab click in the control source and click the arrow and select the EmployeeID field. Now click in the row source property and click on the button with three periods. This opens a query builder. Add in the employee table. Double click on the Employee ID and the first name fields. Don't try to save this, just click the X to close it. This puts the sql in the row source property. The bound column, right below, is column one, the EmployeeID so put a 1 there.

Now click on the format tab. The column count should be two, (EID and FN) and the column widths should be 0;2 meaning, zero inches wide for the EID, because we don't need to see the number, and two inches to see the name. The column heads is no and the list width should be 2.25 inches, two inches for the names and .25 for the verticle scroll bar. Now save the form and see if it works.

So, if all this works, make the same changes to the inventory combo.

And if all this works, let's fix the employees name. Edit the combo row source and change the first name field to this:

EmployeeFull: FirstName & " " & LastName
but some people like to look others up by last name, so you could use this
EmployeeFlip: LastName & ", " & FirstName
Then click in the sort field and select accending so the list is displayed in order.
That's about it. Let me know how this lesson goes.

Privateer

 

[david0121]

David,
Could you explain what the transaction item is?
Privateer

The transaction item will keep track of all of the stock purchases and usages, My main form will be the log form showing -:

LogID
LogDate
Notes
Temperature
Amonia
Nitrite
Nitrate

In addition to the above I need to make the necessary changes to the Inventory.

Should this be the TransactionID "This Should allow me to make the Physical Changes to stock"

Currently i have to make an entry in the log form completing the notes field for reference and then remember to make an entry in the Inventory form to apply stock purchases or filter usages.

I would like to do all this from the LOGS form, So that everything gets updated in on go.

Hopefully this answers your question

Kind Regards

David

 

[Privateer]

David,
I am a little confused because the transactions table implies things happen there. If I take money out of an ATM, that is a transaction, and my account / inventory is reduced by that amount. Perhaps we should rename the transaction table to Supply, or ReStock or something more descriptive.

If this ReStock form is to be used to buy more supplies, then you will need the inventoryID and the StockNew fields to record your purchases. Once you get the tables setup, I can help you do an update query where you take the StockNew and add it to the StockTotal and "update" the StockTotal field with the combined total.

So what is the purpose of the log table, the name doesn't tell me much? Are you depleting your stock on this form? Or are you just recording water chemistry?

If you want the log table to handle most things, meaning keep track of when you use inventory, then put an inventoryID and a StockUsed field in the log table. The goal here is to get a PK number in log that matches a PK number in inventory so those two records can be lined up. Then the StockUsed in log can be lined up with the StockTotal of inventory and in a query, you subtract the total from the used and get whats left.

Let me know how you are doing. And I apologize for the delayed response. On rare occasions I can post from work, most times it fails and I have to wait till I get home.

Privateer

 

[david0121]

David,
So what is the purpose of the log table, the name doesn't tell me much? Are you depleting your stock on this form? Or are you just recording water chemistry?

If you want the log table to handle most things, meaning keep track of when you use inventory, then put an inventoryID and a StockUsed field in the log table. The goal here is to get a PK number in log that matches a PK number in inventory so those two records can be lined up. Then the StockUsed in log can be lined up with the StockTotal of inventory and in a query, you subtract the total from the used and get whats left.

Privateer

Sorry for the confusion,

The main purpose of the log table is to record all activity, things like water chemistry, water changes, water tests, have no affect on stock levels. Things like maintenance, medication, stock purchases, stock losses, all affect stock levels.


Currently I use two forms all of the above items are recorded on the log table, I then have to partially repeat the process in another form to maintain stock levels. I.e. if a fish dies we recorded the activity in the log table as "LOSS MALE GUPPY X 1". Then I have to create transaction as "TF001 Male Guppy x 1" with the transaction type as "Loss" to maintain stock levels.


The main object now is to log all activity, and stock within the same form. Obviously with those items that do not affect stock these will still be recorded in the same way, but there will be no transactions to be made.

Once again sorry for any confusion, your assistance is greatly appreciated


Kind regards
David

 

[Privateer]

David,
OK, I think I understand now. The log is really a log of all fistank activities and some of those activities impact the inventory. Here is the kicker, your primary goal.

"The main object now is to log all activity, and stock within the same form."

So how do we know when the activity is an inventory related activity? Don't get me wrong, there are a few ways to do this, but some involve advanced coding, or maybe a sub form or a checkbox next to the item, all of which complicate the issue.

When someone is just starting out with Access it might be best to keep the process simple. Meaning get each form working perfectly. All forms have a table or query as the recordsource and no fancy stuff like taking a log form item and slamming it into the transactions table. My best suggestion for now is that when that poor guppy bites the dust, you copy the text and paste it into the transaction form. Maybe you have both forms open at the same time. Get the database working is my best advice, because you are going to need reports for inventory, orders and god knows what else. You have the tables setup correctly, so building on that foundation will be easy.

Keep me in the loop.
Privateer

 

[david0121]

Privateer, Thank you for your input, I was up until 03.00 inputting all of my data and I now have all of my Forms,Reports,Queries all working, now at the click of a button I can see everything that I need to know, I'am able to look at all costs, running cost and more importantly losses and stocks.

Thank you.

Kind Regards

David

 

[david0121]

Fishtank Database Programming assistance required!!!

Hi Everyone, I'm looking for some assistance with programming advice in order to enable me to maintain activity logs and also maintain stock levels from with in one form.

I'm currently using two forms but this is not ideal, as I'm having to type most of the data twice, which takes time.

I have setup all of my tables as in earlier notes,

The log is really a log of all fishtank activities, some of those activities impact on the inventory. My primary goal would be,to log all activity, and maintain stock from within one form.

Kind regards

David

 

[david0121]

How do I write a PK to a TransactionID field

So close to completing my first database, only got one little mod to sort out.

I don't know how to write the Transaction pk to the TransactionID on my log form. I have added a yes/no to amend stock. if y then as follows -:

Private Sub AmendStock_AfterUpdate()
If Me.AmendStock = -1 Then
DoCmd.OpenForm "Inventory Transactions Form", , , , acFormAdd
DoCmd.Close acForm, "Inventory Transactions"
End If
End Sub

Once I close the transaction form I need to write the primary key to the TransactionID

Any help greatly appreciated

 

[david0121]

How do I write a PK from my Transaction Form to a TransactionID field on my log form?

I have added a yes/no to amend stock. if y then as follows -:

Private Sub AmendStock_AfterUpdate()
If Me.AmendStock = -1 Then
DoCmd.OpenForm "Inventory Transactions Form", , , , acFormAdd
DoCmd.Close acForm, "Inventory Transactions"
End If
End Sub

Once I close the transaction form I need to write the primary key from the transaction form to the TransactionID on my log form,

David

 

[david0121]

Is it possible to create a Kit/Parts Explosion List for Product’s as follows

Is it possible to create a Kit/Parts Explosion List for Product’s as follows -:

Product Code -: WC0001
Description -: 25% Water Change

The components used to complete a 25% Water Change are as follows -:

Product Code Description Quantity
WC0000 Water Per Litre 60
AS0001 Aqua Safe Per ml 30
Z88049 Maintenance 88049 Weekly Filter Change 1

Currently for the above example, I have to complete my LogForm, place a tick in the amend stock field, This action then opens the stock transaction form to make the necessary usage adjustment for the Water Change, My current system does not allow me to monitor stock levels for components.

My answer to the above problem is -:

1. Add a field to the stock details called “Kit ” Y/N.
2. Create a new table for “KitList” which will contain the Product Code from the stock file. And a list of all of the components and quantity’s to make that product, (Note that the components are also based within the stock file)

When I complete the LogForm and place a tick in the amend stock field. My transaction form opens as normal, create a new transaction as normal.

As the transaction is updated I’m thinking that now is the best time to check if the product is a Kit.

If the product is part of a KIT, The following needs to happen. -:

I need to Increase the Product code with the transaction Type of “REC” which is technically the Receipt for the item, and then decrease the components by the quantity based on the Kit list, the transaction type will need to be “ISX” all of the above will happen in the background. I will then carry on with my transaction and decrease the product code with Type “usage”

At this point I’m lost,

How the hell do I get my database to do that,

I have spent the past five days looking for the answer, and still no further.

Help!!!!!!!!!!!!!!!

 

[Privateer]

Dave,
I lost track of you. You should post these new questions as a new thread, don't continue the old one, they get buried and lost. Anyway, here is the code you are looking for. It is a bit advanced but I have confidence it you. Let me know which version of Access you are using, my code is for Access XP, 2002; 2007 will need a few modifications.

Dim TID as long 'variable to store the transaction ID value temporarily
Dim rstLog As DAO.Recordset
Dim rstTrans As DAO.Recordset

Set dbs = CurrentDb
Set rstLog = dbs.OpenRecordset("tblLog", dbOpenDynaset, dbOpenTable)
Set rstTrans = dbs.OpenRecordset("tblTransactions", dbOpenDynaset, dbOpenTable)

With Forms(Me.Form.Name)
rstTrans.AddNew
rstTrans!EmployeeID = .EmployeeOption.value 'The table value gets the value of the combo box
rstTrans!TransactionTypeID = .TransactionTypeOption.value 'The table value gets the value of the combo box
'continue with all the information that goes into the transaction table
rstTrans!LastUpdated = Now() 'this puts the date and time in the field LastUpdated in the transaction table. Make sure field formatted as General Date
rstTrans.Update
rstTrans.Bookmark = rstTrans.LastModified 'move the "cursor" to the new record
TID = rstTrans!TransactionID 'Grab and store the newly created transaction PK
rstTrans.Close
rstLog.AddNew
rstLog!TransactionID = TID 'The transaction, stored in this variable is entered into the log table
rstLog!TankNumber = .TankNumber 'add the form fields to the log table
'continue with all the information that goes into the log table
rstLog.Update
rstLog.Close
End With
What is going on is you are creating a copy of the two tables in RAM and you are adding a new record to each. The trick is as you add a new transaction, you move to that last modified record, the new one, and store the new primary key in a variable. Then when you are adding the information to the log table you include the variable. Its simple when you see the code, but hard to find. Just a reminder, commands are preceded by a period, table fields are preceded by an exclaimation point. And the With Forms ... line allows you to get rid of the Me before each form object, but leave the period. This speeds up the code a little. I usually write the code using the me. to get the form object spelled correctly, then I go back and delete the me.
That is the jist of it. You can put this code in the on click event of a button on the form. Let me know how it turns out.
Cheers
Privateer