Inventory - Auto Deleting From Table

Jul 26, 2012

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.

View Replies

Tables :: Inventory Sheet - Auto Population Of Field

Mar 16, 2014

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?

View 3 Replies View Related

Auto-Sequence Number When Deleting Field Midpoint

Nov 21, 2011

At present I am running Revit Architecture with DBLink to access to edit fields for shelving etc.

What i am doing is fitting out warehouses with 600+ shelving units and they are all numbered.

These numbers are tagged in the drawing.

What I am hoping to do is export the data to access (completed) then amend any changes within access.

Normally, we would put say 600 shelving units on a drawing all tagged 1-600. But then the client may want to delete shelving unit 321.

Leaving the drawing with all shelving units tagged 1-320,322-600.

How would i go about it so access renumbers all data after the deleted field (ie number 321)?

View 2 Replies View Related

Inventory Table

Feb 27, 2008

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.

Category
Charger,BB Data Only, BB Phone, Broadband Wireless Card, Cellular Phone, Laptop, Laptop Accessory

BB Data Only
Device, Model#,Serial#,IMEI,PIN#,Deactivated

BB Phone
Device,Model#, Phone,#IMEI, PIN#, Deactivated

Chargers
Device, MTHMID, Type of Charger

Laptop
Device, MTHMID, Manufacturer, Model#, Serial#

I also have tables for manufactures and service provider, can i add a lookup to the service provider table to the bbphone table

basically i want to create a form that will change the information requested based on the type of device selected.

I'm confused about how to setup my main table, and the relationships between the tables.. please help

View 3 Replies View Related

Transaction Table For Inventory Control

Oct 27, 2005

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.

View 3 Replies View Related

Help Please On Table Design For Simple Inventory DB

Feb 12, 2007

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:

Drink [date] [IN] [OUT]
Coke [date] [2] [0]
Coke [date] [0] [1]

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?

Thank you so much for any help on this!

View 14 Replies View Related

Help To Create Inventory Table For A Chart

Nov 29, 2004

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.

Thanks for any help. I'm stuck.

View 2 Replies View Related

Table Organization For Inventory Database

Sep 1, 2005

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.

Any input would be appreciated!

View 1 Replies View Related

Beginner-Tutorial For Retail/inventory Table(s) Set Up

May 5, 2007

Access Guru's:

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?

Sorry so wordy.

Catherine

View 1 Replies View Related

Modules & VBA :: Add Items Received To Inventory Table?

Feb 18, 2014

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

[code]....

View 5 Replies View Related

Tables :: Transaction Table Setup To Adjust Inventory

Apr 22, 2013

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...

View 1 Replies View Related

Modules & VBA :: Inventory - Show List Of Items That Aren't Already In A Table

Jun 19, 2015

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

[Code] ....

View 13 Replies View Related

Deleting One Table From Another

Jul 26, 2007

Hi All!

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:

DoCmd.RunSQL "DELETE tblNewHRoster.* FROM tblRosterwoNHires.*" & _
"WHERE tblRosterwoNHires.[Rep_Name] = tblNewHRoster.[Rep_Name]"

On this I get told there is an "Syntax Error in Query. Incomplete Query Clause."

Can any one help? As you can probably tell I need a lot of it.

View 2 Replies View Related

Db Deleting Records From A Table

May 18, 2005

Hello.

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?

Thanks.

View 14 Replies View Related

Doing A 'where Used' Check Before Deleting A Table

Jul 3, 2006

Hi,

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)?

Thanks for your time,

View 4 Replies View Related

Deleting All The Records In A Table

Dec 27, 2007

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...

View 2 Replies View Related

Adding And Deleting To A Table

May 27, 2006

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.

View 6 Replies View Related

Deleting Records From Table

Feb 2, 2005

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.

Thanks

Sue

View 4 Replies View Related

Deleting All Records From A Table

Nov 23, 2004

Hello:

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!

Regrards,

Dion

View 1 Replies View Related

Deleting All The Records In A Table

Dec 22, 2004

Is it possible to have all the records deleted from either a command button on a form or from the switchboard...

View 6 Replies View Related

Deleting From A Table In A 'shared Environment

Oct 4, 2005

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?

Thanx,
Stacey

View 5 Replies View Related

Deleting Records From A Table In Access

Jul 27, 2006

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

View 1 Replies View Related

Error Deleting Record In Table

Mar 16, 2005

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

Thanks!

View 1 Replies View Related

Deleting Matching Records From Another Table

Dec 6, 2005

Hi

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".

What am I doing wrong? Or is there an easier way?

Dave

View 2 Replies View Related

Deleting Query Table With An Array

Apr 11, 2008

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?

THANK YOU!