Adding A Subset Table
Nov 4, 2004
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?
As usual any suggestions will be most welcome.
View Replies
ADVERTISEMENT
Aug 2, 2007
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.
Basically I have these:
Table 1
PIN attribute1 attribute2 .....attribute6
1
2
3
.
.
.
52,000
Table 2
PIN
5
8
9
12
18
335
789
.
.
.
N
Table 3
PIN
41
51
66
3
78
903
1245
.
.
N
Note that tables 2 and 3 only contain PINs
Does anyone have a script for this problem?
I would appreciate your help on this. Thank you.
Jun
View 7 Replies
View Related
Nov 24, 2014
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;
View 8 Replies
View Related
Apr 21, 2014
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 & "*')"
[Code] ....
View 5 Replies
View Related
Feb 3, 2015
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.
View 1 Replies
View Related
Sep 25, 2013
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?
View 14 Replies
View Related
Aug 19, 2015
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.
View 3 Replies
View Related
Sep 19, 2013
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.
View 7 Replies
View Related
Jun 22, 2012
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?
View 5 Replies
View Related
Oct 2, 2005
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.
View 1 Replies
View Related
Jun 26, 2006
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.
View 4 Replies
View Related
Feb 9, 2005
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!
View 4 Replies
View Related
Nov 23, 2005
Hi All,
I am trying to add the infomation from a query and place it in a table so that I can use that data to build a Union Query that plots onto a chart.
Heres what I have:
Dim qDef As DAO.QueryDef
Dim SQL As String
Set qDef = CurrentDb.QueryDefs("QryBaseline")
SELECT tblAudio.AudioID, tblAudio.PatientID, tblAudio.AudioDate, tblAudio.Type, tblAudio.L500, tblAudio.L1k, tblAudio.L2K, tblAudio.L3K, tblAudio.L4K, tblAudio.L6K, tblAudio.L8K, tblAudio.R500, tblAudio.R1k, tblAudio.R2K, tblAudio.R3K, tblAudio.R4K, tblAudio.R6K, tblAudio.R8K
FROM tblAudio
GROUP BY tblAudio.AudioID, tblAudio.PatientID, tblAudio.AudioDate, tblAudio.Type, tblAudio.L500, tblAudio.L1k, tblAudio.L2K, tblAudio.L3K, tblAudio.L4K, tblAudio.L6K, tblAudio.L8K, tblAudio.R500, tblAudio.R1k, tblAudio.R2K, tblAudio.R3K, tblAudio.R4K, tblAudio.R6K, tblAudio.R8K
HAVING (((tblAudio.Type)="BL")) AND (((tblAudio.PatientID)=[Me]![PaiteintID]));
I have a "tblEvaluate" that I want to store the above selected record.
I am not sure whether to add and Delete, OR Edit and Update?
How do I get the above information into the table - "tblEvaluate"?
I know it's simple, I just have a mental block!
Thanks for you help....ENVIVA
View 3 Replies
View Related
Feb 6, 2006
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.
Thanks
View 14 Replies
View Related
Mar 15, 2006
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.
~Profector
View 4 Replies
View Related
Mar 15, 2006
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.
~Profector
View 6 Replies
View Related
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
Apr 4, 2005
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.......
Any ideas what I may be doing wrong?
Thanks!
View 6 Replies
View Related
Jan 17, 2006
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.
View 2 Replies
View Related
Mar 20, 2008
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.
View 3 Replies
View Related
Dec 21, 2005
Hello
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?
View 2 Replies
View Related
Sep 18, 2006
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
View 1 Replies
View Related
Oct 6, 2013
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;
alter table sec0611_departments
add column ' ______ '
add column ' ______ ';
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?
View 5 Replies
View Related
Oct 15, 2015
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
View 1 Replies
View Related
Jun 7, 2011
I have a form that has a textbox and a button.
I want to add whatever the textbox's value is into a table.
How do I go about doing this?
View 2 Replies
View Related
Jan 8, 2006
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?
View 3 Replies
View Related
