I have been writing a stock control database for my small Jewellery Shop and thanks to all the help and encouragement I have received here can see the light at the end of the tunnel - until I reaised a major design error!
My system is based around every product having a unique Product Code in the form ABC1234
AB is a 2 letter code for the Supplier
C is the category (Ring, Bracelet, Pendant etc)
1234 starts at 1 and gets incremented by 1 for each different product
The problem is that I have no current way of recording different sizes for rings with the same Product Code. So when I run a stock level check I may have 3 rings with the code JWR0021 and if my reorder level is set to 2 I would not reorder any of them. The ring may be a top seller in larger sizes but nobody buys them in the smaller sizes, I am sure you can see my problem.
What I want to do is to add another table for ring sizes but still keep the same Product Code. That way I can keep the code as ABC1234 but have A,B,C etc as subset of data for sizes.
What would be my best way of doing this without having to re write everything?
I have 3 parcel tables. Table 1 contains the parcels of the whole county. Table 2 contains open space parcels and table 3 contains preserved farmlands. I would like to remove parcels in tables 2 and 3 from table 1. Tables 2 and 3 are subsets of table 1.
How to sort a subset in a query. I have three fields BatchID,Month and Year. The BatchID can have multiple months and years. I would like to order the final sort so as the Batchs appeared in the order of the highest date combination with in each Batch.
The following is the SQL of my first Query which establish's the lowest and highest date in each batch.
Code: SELECT tblRevOnlySelYear.BatchID, tblRevOnlySelYear.OperatorID, Max([Year] & IIf(Len([Month])=1,0 & [Month],[Month])) AS Expr1FROM tblRevOnlySelYear GROUP BY tblRevOnlySelYear.BatchID, tblRevOnlySelYear.OperatorID HAVING (((tblRevOnlySelYear.OperatorID)=1)) ORDER BY Max([Year] & IIf(Len([Month])=1,0 & [Month],[Month]));
The next SQL is the second query which results in the Batchs in the correct order by year but the months are not correct.
Code: SELECT tblRevOnlySelYear.BatchID, tblRevOnlySelYear.OperatorID, tblRevOnlySelYear.Month, tblRevOnlySelYear.Year, qryRevTest4_1.[1stDate], qryRevTest4_1.LastDateFROM qryRevTest4 AS qryRevTest4_1 INNER JOIN tblRevOnlySelYear ON qryRevTest4_1.BatchID = tblRevOnlySelYear.BatchID WHERE (((tblRevOnlySelYear.OperatorID)=1)) ORDER BY qryRevTest4_1.LastDate;
I have a form that when initially displayed shows all client records in my database with the ability to add new ones through a "Add New" command button. I have another command button called "Search" that displays a second form containing a dozen or so fields to search and filter down on.
When the Search button is pressed, I display search form and enter data in some of the fields for searching. I then click ok and the main form should now display a subset of the records based on the data I entered on the search form.
My dilemma is that the WhereCondition of the docmd.OpenForm doesn't work. The doccmd works to open the form when I don't include the WhereCondition, but the moment I put the WhereCondition in, I get an unknown runtime error (3000).
Here's the code. I've stripped out most of the search fields to target just the one until I can get it working, then I'll add the others back in.
Private Sub cmdSearch_Click() Dim varWhere As Variant ' Initialize the search string to Null varWhere = Null If Not IsNothing(Me.srchLastName) Then varWhere = "([p_last_nam] LIKE '" & Me.srchLastName & "*')"
I created a form to enter the lookup data and build the SQL string to create a recordset in VBA that is a subset of the original data. Using the Form wizard I created a form that displays ALL of the records in that original table (actually a QUERY). What I want to do is open this second form but have it reference the recordset I built in VBA.
I've built a PostgreSQL database for some ecology data and am trying to use MS Access 2010 to make a front-end for it.
I've created a form to display site data; it includes such fields as site ID, site name, county, state, landscape, etc. (There are more, but if I can get the idea down with a couple of these, I should be good to handle the rest). One can click through the form and see 14 pages of site details, which is fine because there are 14 sites.
What I want to do now is make a search form in which one can enter values for site ID, site name, county, etc, and somehow get the pages of the display form that match those values. It's not terribly important to me how that output looks - whether it's a list of matches that allows for clicking on a match and showing just one page of the display form, or whether it's a filtered subset of the display form pages that one still clicks through to view them all.
if there is code to write, where do I type it? Do I start my search form by making a form with the search fields as values, or is there some other way? How do I make those search fields into search boxes rather than display boxes?
I'm developing a database for the hospital I work in. One purpose of this is to keep track of patients with temporary invasive devices (there are many types such as urinary catheters, ventilators, etc.), specifically how many days each patient has a device for. Each device is associated with one patient only, but one patient may have many devices. Here's how the associated tables are set up:
1. tblPatients - PatientID (PK), LastName, FirstName, DOB,... 2. tblLocations - LocationID (PK), LocationLabel,.... 3. tblDevices - DeviceID (PK), DeviceType, Device, DeviceDesc 4. tblDeviceUse - DeviceUseID (PK), DeviceID (FK), PatientID (FK), LocationID (FK) (where in the hospital was device inserted, e.g., operating room, bedside, etc.), DeviceStartDate, DeviceEndDate 5. tblDeviceDailyLog - DeviceLogID (PK), DeviceUseID (FK), DeviceDate, PatientLocID (FK) (area in the hospital that patient is in)
All primary keys except for PatientID & LocationID are Autonumbers; and the tables are linked appropriately.
Whenever an entry is made into tblDeviceUse, I want there to be new records to be automatically generated in tblDeviceDailyLog for each date between the Start and End Dates. For example, patient A123 had a urinary catheter from 1/1/2000 to 1/10/2000 that was inserted while the patient was in the ICU, but the patient was moved to the Medical Ward on 1/7/2000. So tblDeviceDailyLog should have 10 new records associated with this device, one for each calendar day, with the appropriate location for each day.
I'd like to learn how to do them quickly and effectively in Access. One of those things seems like it would be an incredibly simple, intuitive operation, but it's not, at least not to me.Say I have some data that really should only be on one table, but it's currently on two tables in Access: "List Main" and "List September Adds." They both have the following fields: "Full Name" "Email" "Address" "City" "State" "ZIP" "Phone."
The first table is my main list of contacts. The second contains new info, consisting of 1. a few new contacts, and 2. updated info for a few of the contacts already in table "List Main."
I would simply like to put all the new contacts from "List September Adds" into my "List Main" table, and I'd also like to fill in a few missing e-mails in "List Main" with newly gathered e-mails for those contacts, info that is in my table "List September Adds."I'm sure there is built-in functionality to do something as basic as essentially turning two pages of the same spreadsheet into one. In fact, I know I could import data from an Excel file and have it "append" to a preexisting table if the fields are all the same.
However, I'd like to know how to do it when the tables are already in Access (without having to learn any SQL, mostly because I'm fairly certain I don't need to know SQL to do something like this). I've heard of Append and Update Queries and given their names, it sounds like they'd be useful, maybe with use of "Totals" and "Group By" to get rid of duplicates; however, I can't seem to get any of this to work right.
Mostly, when I think I am doing an Append correctly, it doesn't add new data at the bottom of a table; it just wipes out all the data that was there, and replaces it with data from the source. For instance, when I've been running an Append Query to get my "List September Adds" rows into my "Main List" table, what I end up with is only the "List September Adds" rows, and all the "Main List" rows gone.
I have two tables they both share column 'Mile Markers'. Table 1 lists all mile marker from 0-60 in 0.1 mile increments, in order. It also has a column for x and y coordinates. Table two only lists 435 random mile markers. Is there a way to add the correct coordinates to the 435 mile markers in table 2 from table 1?
The attachment is the database Im working on and I have a problem. I need to add a supplier Product number in there some where so I could keep the numbe as reference and so it may be easier to enter items recieved. in the form section, I will have Invoice with a sub form Invoice details. I need to put suppliers Item number somehow so it would search and if the item is on the product list it would fill out the rest of the values.
should I make a separate table for suppliers product number or should I just include that into the invoice detail? I dont want to put it in the product table since I get same Items from different suppliers.
I have a table with a bunch of values for time spent on a variety of projects. Each persons time on a project is entered using a form. What I'd like is to then be able to add some of these times to get a total department time and a total project time in the table to help with our project costs estimating. Anyone have any ideas as there doesn't seem to be an easy way in the table itself to merely add columns. Thanks in Advance.
Hope someone can help! I was wondering if is possible to add a series of records that are in serquence to a table by just entering the first and last numbers. For example a string of numbers starting at TP11000 and ending at TP11100, the prefix TP doesn't change. Currently I have to enter every one manually, so any help would be much appreciated!
I have 2 fields in a table 1. called CommAmtDue and the other called CommAmtPd, When the user inputs a value in the CommAmtPd I want the CommAmtDue - CommAmtPd = CommAmtBal
I want this value to be stored in another field in the same table called CommAmtBal
I can do the formula in a query but how can I get the value to be stored in the field to user for later.
Hello folks first post here. I tried reading through the boards to find someone with a similiar question, but not luck yet. I have been working with a basics of Access for about a year. I did take take a bit of programming in college but I don't remember much of it, so I looking for a little help. I can usually Google for answers but I cannot seem to find the write key words this time.
A while back I created a table that tracks employees' data and recently I started working with the insurance data I have gathering for sometime.
It goes something like
Field Name Data Type
SS# Number (primary key) Insurance $ Number PPO YES/NO HMO YES/NO Self YES/NO Spouse YESNO Children YES/NO Family YES/NO Dental Self YES/NO Dental Spouse.......etc.
What I'm trying to do is turn the yes/no's into total dollars for each person. I have done this in Excel but the fields were differnet and I had to use a many, many if.'s or a lookup table(data array)
PPO doesn't = a dollar amount by itself, but PPO + Self = $19.03 or PPO + Self + Dental Self = $23.00. Or the person can just have Dental Spouse = 8.86. Pretty much the people can take a piece and leave another.
"Insurance $" is another field name and is where I am trying to get the total to go.
I was thinking of a lookup table but I don't know how to do that in outside of Excel.
I am thankful for any help or even a nudge in the right direction.
Hello folks first post here. I tried reading through the boards to find someone with a similiar question, but not luck yet. I have been working with a basics of Access for about a year. I did take take a bit of programming in college but I don't remember much of it, so I looking for a little help. I can usually Google for answers but I cannot seem to find the write key words this time.
A while back I created a table that tracks employees' data and recently I started working with the insurance data I have gathering for sometime.
It goes something like
Field Name Data Type
SS# Number (primary key) Insurance $ Number PPO YES/NO HMO YES/NO Self YES/NO Spouse YESNO Children YES/NO Family YES/NO Dental Self YES/NO Dental Spouse.......etc.
What I'm trying to do is turn the yes/no's into total dollars for each person. I have done this in Excel but the fields were differnet and I had to use a many, many if.'s or a lookup table(data array)
PPO doesn't = a dollar amount by itself, but PPO + Self = $19.03 or PPO + Self + Dental Self = $23.00. Or the person can just have Dental Spouse = 8.86. Pretty much the people can take a piece and leave another.
"Insurance $" is another field name and is where I am trying to get the total to go.
I was thinking of a lookup table but I don't know how to do that in outside of Excel.
I am thankful for any help or even a nudge in the right direction.
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.
My form is based on a select query. There are 2 combos that filter the data displayed in the form by status and property. All of the data is contained in the same table and the query is looking at just the one table. Clicking a button opens a listbox that contains all of the filtered data and clicking a record in the listbox populates the form with that record and closes the listbox. It works good like this.
When I try to add one field (labor) from a second table to the query with that same field added to my form, as long as there is a value in the labor field in the table then it works. If there is no value for labor then the form returns no records. There are records there but they do not have labor assigned yet. I set a default value for the field on my form - (not assigned yet). The listbox displays the records even though there is nothing in the labor field, but clicking a record in the listbox returns an error of 'no current record'.
Actually I dont even have to add the labor field to the form, just having it in the query mucks up the form. I must need an isnotnull statement in the criteria or something.......
Hello all, i am making a form to process ordering within my membership system, i would like to add what i believe is called a subform or table of somesort, which is all connected to the same table i am using, but i would like it to appear in hte table format so that i can add more than one product that my client is buying.
the attachment i have shows what i have so far, the product information on the left is what i would like to be in the table area.
This is going to sound like a bizarre question, but, here it goes. Is there a way to add more than 255 fields to a table? Or is 255 the cutoff? Also, if you have two table with different information on one client, is there a way to add a client to one table and have it automatically add it to the second? Sorry about all of the wierd questions. Let me know if you know anything.
I have created an DB which we report the errors on a system we use. We would like to add images into the table where we record the problem so the programmers can see whats going wrong.
I have created a field within the table called images with a OLE Object selected. When i go to add an image i get an error message "OLE Server was lost, or the OLE server encountered an error"
Is this a problem with Access or would this be a problem with our network?
hi I had a form with TO,DATE,BODY and a button . on click of a button this fileds should get added to a table. I have created a table with same fields. Thanxx
Assignment : Add two new columns to a copy of the departments table, sec0611_departments. One new column, a text column, will be the name of the manager department. The other new column, a numeric column, is for the annual budget of the department.
As of right now I know how to start the code, which (if not mistaken) should look like;
My question is, what value do I insert in place of "name of the manager" (ex. manager_id, first_name?) and same goes for the numeric column. Price? credit_limit? budget?
I have created a database and I have the same primary key(SITE) for more than one table. I can not seem to figure out how to create a query to add an entry to all the tables that have site as the primary key. Also, I am running Access 2003
When I add something in a table using a form, I always want an automatic date, namely today's date. So I set this in the form with NOW (). The only thing is, when I view my records a day later, it automatically adjusts the dates. I don't want to type in the date every time, because it's always todays date. But I don't want the table to adjust the data-capturing dates automatically.Any suggestions to get around this?
