I use Access on a daily basis as an end user at work and am building my very first database for my woodworking business I started this year.
So what I am wanting to do is import my Excel inventory list to a table. Along with the categories I have in Excel, I have created inventory numbers for my products. Since I'm not exactly a businessman and accustomed to inventory systems, my inventory numbers just start with "AA" and go on up (I chose this as opposed to assigning numbers so customers aren't confused when they see a price tag).
Anyways, what I am doing is creating a Sales table and accompanying form for entering sales of each product. What I would like to happen is every time I make enter a record into the Sales form, I would like for the item to be deleted automatically from the inventory table.
I have a sheet of inventory I am working on creating. The price for storage for an item is determined by 2 things. 1. the type of material and 2. the size of that material. I have created a table with the material types and sizes. Is there a way that I can have the price automatically populate when I select the type of material and then size?
I'm a newbie. I'm setting up a database to monitor inventory then allow users to make reservations from the items in inventory. I'm a little confused. I have the follow tables setup.
After a lot of searching, printing and highlighting I want to double check that I have the right idea.
I have a simple inventory control database. From reading the forum, it seems the best approach is to drop the detail lines table of the purchase order (incoming) and the detail lines table of the sale invoices (outgoing) and make one table for all transactions.
The Transactions Table would also keep records of inventory adjustments from inventory counts, etc. Each transaction would be given a type so I know in which direction the stock count moves in (adding or subtracting) when I sum the totals for each item.
Otherwise, I need union queries to ensure all tables are considered in my calculations.
My database is simple (thankfully). Let me know that I am on the right track I like the solution but like any new student, a little reassurance is appreciated.
Hi, I was asked to help create a very simple Access DB for a small restaurant to help them keep track of inventory.
The only things that the owner wants to keep track of are: 1.Item Catalog 2.Date and Quantity of Item IN or OUT 3.Current # of Items
My problem here is that there are 5 Item categories, each with a relatively lost list of Items under each one. 1.Drinks 2.Ice Cream 3.Kitchen Supplies 4.Grocery 5.Etc.
Will it be advisable for me to create an Item Catalog TABLE for each of the category instead of piling all the Items into just one Item Catalog TABLE?
The Designs I’m currently considering are:
A.)
TABLE SET 1 : Catalog of Items Fields (ID, Name) •TABLE Drinks •TABLE Ice Cream •TABLE Kitchen Supplies •TABLE Grocery •TABLE ETC --------------------------------------------------- TABLE SET 2 : Movement IN/OUT of Items Fields (Movement ID, Name, Date, IN-Amount, OUT-Amount) •TABLE Drinks •TABLE Ice Cream •TABLE Kitchen Supplies •TABLE Grocery •TABLE ETC
B.) TABLE 1: Catalog of Items Fields (ID, Category, Name)
TABLE 2: Movement IN/OUT of Items Fields (Movement ID, Name, Category, Date, IN-Amount, OUT-Amount)
I’m really more inclined to choice A because I just re-learned Access last month and will be creating my first serious database just now. I don’t want to choose a more complex table structure (choice B) that might cause problems with the DB later (as it will really be used to keep track of real-life inventory and might screw up their operations if it goes haywire). Is Design A viable? Or flawed?
Another question I have is with the structure of the Movement – IN/OUT table. Is this workable? My thought is just to let the user enter something like:
In the database, then compute current Quantity of the Item using a query (Sum[IN] – Sum[OUT]) . Will this work? I’m a bit apprehensive with this because I’m thinking I should have another TABLE that will store the actual inventory Quantity instead of the Quantity just being computed through query. But doing another TABLE for Quantity does complicate stuff a lot since it will have to determine which Quantity value gets added to or subtracted from based on many criteria. Will it be OK just to use the query to compute Quantity left?
I am in Access 2003 and I need to create a chart of the inventory levels of a product. I have the daily production and I know that the amount distributed out of the warehouse will be the amount produced that day up to 200 items. However, I can't figure out how to keep a running balance of 'ending inventory' if the amount is over 200, so that it would be the next day's beginning inventory.
I am trying to figure this based on the formula:
beginning inventory + new production - distribution = ending inventory day 1 day 1 ending inventory (beginning inventory) + new production - distribution = ending inventory day 2
etc
I wrote a query to put the new production and the daily balance to retain in inventory (the lesser of daily production or 200). I need my query to be run so that I can put it into a chart, so I need a column to be 'ending inventory' by day so that I can have a line chart going across time.
I'm wrestling with a problem with a hardware and software database, on the software side. I'm trying to figure out where to store the license and media cost for a piece of software. Right now I have the following tables:
1. Software name (MS Word, Office, for example) 2. Software version, which also stores whether a license is required 3. Software license, which includes the license code and whether that license expires 4. Software inventory table, which stores the # of licenses we have available for installation. The purchase order (PO) # and date are currently here. 5. Software PO generating table. This is used when we need to reorder more licenses, or a new piece of software.
I need to figure out where to store the cost of the software, the cost of the license, how many people are covered by the license. I've been trying to decide which of the last three tables should store this information.
There are two issues: we need to keep a history of how much older versions of the software and its licenses cost, as well as be able to enter information to order new versions of the software and/or licenses.
I am a beginner at best with Access. (I'm using Access 2003) I have a small in home business as a second job making silk flower arrangements. I am now to the point that I need to track my inventory, quantity used, COGS, etc. Most of my arrangements are in gift shops on consignment.
In lieu of buying Quickbooks Manufacturing ($$$), I want to start tracking everything with Access so I can at least get some nice reports at tax time.
The problem I am having is I cannot translate the tutorials that Microsoft supplies to this application, they only cover setting up basic tables. (ie: in a retail/inventory application - do I set up 1 table, 1 table & subtable, 2 tables????)
I set up a table so far with 4 basic inventory fields: Item description (record includes: flower, vase, foam, etc) / Color / Qty on Hand / Cost. Then (on the same table) finished goods fields of: Qty Used / Arrangement # / Qty Left / Sold for. While trying to figure out how I am going to track how much each arrangement cost, I'm not sure this set up will work.
I probably went into too much detail for my question, but I wanted to give some kind of background. Is there a CD/Book/Tutorial that specifically guides one through setting up a retail/inventory table?
I'm trying to add items received to my inventory table. If the item is already in the table, I just want to update the number and cost etc. If the item is not in the inventory table I want to add it. My problem is determining which item in the source table is already in the target table so I can either update of add. how to find an item number in target table by looping through the source table?
Here's the code I've written which doesn't work.
Dim I As Integer Dim db As Database Dim rs As Recordset Dim rs2 As Recordset Set rs = Nothing Set rs2 = Nothing Set db = CurrentDb
I have a database that I use to put in orders for our shop and keep track of our part informations as well as paint and packing materials. Everything works good on this but I am trying to create a table for adjusting quantities on hand for packing material based on the part quantities and for adjusting paint in stock based on information given to me after the job is run.
There is already a relationship between parts and packing material as well as parts and paint. When I put an order in I would like to have it adjust out that many packing materials that are related to that part. Once a job is run I need to be able to adjust out the amount of paint used.
For the paint side of this I want it to track the paint used by order, we are trying to get a grasp on how much paint we are using for parts so it is important for me to know how much and when.
I am thinking I need a table that connects paint to orders and has quantities in it, then create a query and do the calculations from there... I do not know how to accomplish that but it sounds like it could be right...
I have a list box that allows multiple selections [Inventory]. I also have a combo box that has multiple selections [Shows].
Right now, user selects from list box and from a combo box and clicks a button. On button click, the items from the list box are associated with the PK from the combo and stored in a junction table. This allows me to quickly associate many inventory items to one show.
I realized that there I currently have no way to prevent duplicate Inventory+show records in the junction table besides having a composite key. This would be fine except no records get inserted into the junction table if there's a duplicate entry.
Ideally, I think that the user should select from the combo box [Shows]. This should narrow down what shows up in the list box [Inventory] in a way that Inventory items already associated with the show are not displayed.
If I have 10 Inventory items and Inventory items 1-5 are already associated with Show 1; after I select the combo box, the list box only displays Inventory items 6-10.
Here's the associated code
Option Compare Database Option Explicit Private Sub cmdAddRecords_Click() Dim strSQL As String Dim db As DAO.Database Dim rs As DAO.Recordset Dim ctl As Control
I am relatively new to Access. But over the past few weeks I have developed a large Db. I have searched the Forum under almost every thing I can imagine and have tried to implement some of them.
What I need to do is delete the records in one table from another table. I tried using a delete query but simply get told I "You cannot delete from the specified tables." I have checked almost everywhere I can find to check and the tables are not in read only status and I built it.
I have also tried doing it from a module in VBA with the code:
I am using a large database, which usually works fine, and is set to compact on close.
Occasionally it has been losing a lot of data in the main table, probably when it compacts, down to a round number of records. This time it left me with 10,000 records exactly. (It has been different round numbers before)
Does anyone have any ideas as to what is causing this?
I wonder if any of you could help me with what I assume to be a simple request, but one I've stuggled to find an answer to in the 'help' searches that I've done.
I'm working with an Access 2003 database that was originally designed to use a copy of a live table held elsewhere (this copy only used a subset of the fields from the main table). I now need to change the database so that it references the live table only, to ensure data integrity. To do this, I want to remove the 'copy table' and make all forms, queries, reports, macros etc. refer to the live table instead.
Is there a 'where used' procedure that I can run to find all the objects that use this table, rather than my having to go through every single one and check if this table is referenced (there are hundreds of such objects in this database)?
Hi, I am using Vb6.0 as a front end and msaccess as the db. i want 2 delete all the records in a table "Register" by clicking a menu The connection is made using ADODB Till now i hv the code
Private Sub mnudel_Click() Docmd.SetWarnings False Docmd.RunSql ("DELETE * FROM Register;") Docmd.SetWarnings True End Sub
But when i execute this a error msg displays Run time error "424" Object required
Whats the problem in me plz help me to come out from this...
I have a table to which items must be added and others must be removed. Items in the table must be numbered consecutively At present I switch from the table to a query and manually reset the reqirements in the criteria section for each alteration. Can I set up a Query, Update query or something similar so the when I add or delete an Item the numbers field automatically renumbers the rest of the field. If this is understood. Thanks to anyone who can help.
I have a simple form with a subform on it (see enclosed Access 2000 Database). I'm trying to add/change/delete records from table: Component Name. Adding and changing records is not a problem but deleting is. It only deletes the value of the field "Interative Component Name" and not the entire record. I have my joins defined properly on the tables. What am I missing? I wish this Access stuff was easier to learn. Any help/clues would be greatly appreciated.
I have created a form to record addresses, phone numbers and other information. How do I go about creating a DELETE button that when pressed, deletes ALL records from the table.
Many thanks in advance and for those of you who celebrate Thanksgiving, have a Happy Thanksgiving!
Hi, Im working on a database that has to be shared by 3 or 4 users. In on of the actions that the database performs, it has to delete records from a table first. When sharing the database, it always asks the users (using access viewer) whether 'they are sure that the want to delete the records'. Does this always happen? And why? How can I get rid of it? Would it matter if I change it from a table to a query?
I want to automatically delete records from a table in MS Access 2002, I want to keep the existing table structure and therefore I do not want to delete the entire table. I cannot find a way to do this through a macro. Any help on this would be appreciated
I have a corrupt record in a table that I am trying to delete. I keep getting the following error when I try and delete the record: "The search key was not found in any record".
Can anyone help me with this. All I am trying to do is delete this one record
Apologies if there is a previous post that answers this - I've looked, but can't find anything that works.
I have two tables with identical structures. tblA contains a subset of the records on tblB, with identical values on all fields except ID. I need to remove from tblB all records appearing on tblA. I thought the following would work:
DELETE tblB.* from tblB INNER JOIN tblA ON tblB.Field1 = tblA.Field1 AND tblB.Field2 = tblA.Field2 AND tblB.Field3 = tblA.Field3...
but I get "Could not delete from specified tables".
Hi, I have a problem deleting a specific row and field that my query have.
What I want to do is mainly delete a specific rows in the my Query table that has : Field 1, Field 2, Field 3, Field4
what i tried to do was doing is create and array that loop through the Query table and check in field 1 for a certain value and if that certain value appears I want to delete that row: for example
Dim IntRow As Integer Dim IntCol As Integer Dim varData As Variant
For IntCol = 0 To numFields Step 1 For IntRow = 0 To numRows Step 1 If (rst.EOF = False) Then 'Check Field 1 for value 47-72 to delete Else If (varData(0,IntRow) > 47 And varData(0,IntRow) < 72) Then varData(IntCol, IntRow).Delete End If End If Next IntRow Next IntCol
In the example Above my syntax is not correct for : If (varData(0,IntRow) > 47 And varData(0,IntRow) < 72) and for varData(IntCol, IntRow).Delete.
can anyone help me to find the exact syntax i need to use to execute the program above?
