Updating Data On 2 Tables Based On Criteria
Oct 8, 2004
Ok. Here's the situation. I have query setup. This query has 2 columns:
1. Agent Name
2. Sale Value
I have 2 tables.
Table1: Includes Sales for this pay period only
Table2: Includes all Sales From past to present.
We are setting up our system on Tiers and Each sale value is going to change. So, Here's what I need:
I need to run an update query that first Looks up all the records in Table1 based on Agent Name. The field that needs to be updated is called SaleValue. It needs to be updated with what's in my Query mentioned above.
Then, all the values in Table1, need to be copied (or updated) into Table2. This can be done based on the Primary Key (SaleID), which exists in both tables. So I guess we will need to run 2 separate update queries. Any ideas on how this can be done?
View Replies
ADVERTISEMENT
Dec 22, 2014
I have two tables linked by ID (Table1,Table2)
The Table1 holds data that is a request for a task.
ID, Request, Task_tms (the number of times the task is required to be done)
1, Make a cup of coffee,15
Table2 is my allocation of people to the tasks.
ID, Person, Task_done (the number of times this person has done the task)
1,Sally,10
1,Eddie,5
What I want to do is to limit the amounts of allocation in some way (ie in this case if I have already put 10 for Sally then I would be unable to put anymore than 5 when entering the amount for Eddie.
Additionally if I were to allocate all 15 to Sally then no additional people would be able to be allocated to this task (ID 1)
The two table are in datasheet format linked on a form.
View 1 Replies
View Related
Nov 6, 2006
Is there a way in a query (using SQL) to go to the first record to update a field based on a criteria ?
For example if my amount field shows two amounts for $300.00 I want to go to the first record that has $300.00 to update a field in that record.
Is that possible?
View 9 Replies
View Related
Jan 8, 2006
Hi,
I've spend ages reviewing the relationships between my tables and changing my query but cannot get this to work. I've read alot of the posts on the subject also but trying to apply the suggestions to my problem hasn't worked.
It is a database of engineering drawings. A drawing can have one or more revisions. I have written code to import the drawing and revision info into the datebase so when the drawings are actually received I need to be able to add the additional data in tblReceivedDrawings such as the tracking number, date received etc.
These are my tables:
tblDrawingRegister
DrawingNum (PK)
DrawingName
tblDrawingRevisions
DrawingNum (PK)
Rev(PK)
LatestIssueDate
ElectronicCopy
OrderNum
DrawingStatus
tblReceivedDrawings
DrawingID (PK) auto-num
DrawingNum
DrawingRev
DateReceived
ActionCode
TrackingNum
I have created a query called qSelDrawingsAndRevisions which combines tblDrawingRegister and tblDrawingRevisions.
The form is based on a query called qSelIncomingDrawings. This query joins qSelDrawingsAndRevisions and tblReceivedDrawings. It shows all the records from qSelDrawingsAndRevisions and related records from tblReceivedDrawings and is joined on the drawingNum and Rev.
Adding the extra info e.g. tracking num, date received etc about drawings that are in tblDrawingsRegister works fine. However if I want to add a completly new drawing I get an error message:
"The Microsoft Jet database engine cannot find a record in the table 'tblDrawingsRegister' with key matching field(s) 'qSelDrawingsAndRevisions.DrawingNum'
How can I get it to update the drawingNum in tblDrawingRevisions and tblDrawingsRegister at the same time?
Is it an issue with my query or relationships?
Or do I need to write an OnUpdate procedure for the DrawingNum textbox so that it created a new record in the DrawingsRegister table to correspond with the one in the DrawingsRevisions table?
I have a feeling that this might be overcomplicating it and that I just need to change the query?
Am very confused about how to approach this and would really appreciate a few pointers...
View 12 Replies
View Related
Dec 5, 2004
Hi,
This is my first question... please guide me
I have two tables say Daily and Master, and now I want to
update the Daily table and add two additional fields in this table
based on the data from the Master table.
Please suggest me..
View 10 Replies
View Related
Sep 26, 2005
Dear all,
I would like to seek your help on this problem.
I would like to do a search for my records based on 2 different criterias. If found, the function should just return a boolean value so that I can act further based on the boolean value.
The reason I'm doing this is that I have 2 similar tables that contains EventID and AttendeeID and these 2 tables cannot have the same EventID and AttendeeID. So before I can allow a record to be inserted in the 1st table, it must check that there is no record in the 2nd table that has the same EventID and AttendeID. If there is, then i cannot allow the record to be inserted in the 1st table.
The same thing applies when I want to insert a record in the 2nd table. It must now check the first table.
Recordset.find seems to be only catering to 1 criteria, not 2.
Pls help. Codes are most welcome too.
View 3 Replies
View Related
Jun 27, 2013
I have two tables that will contain the information that I need the third table that will be used for constant data entry to auto fill in two of the fields (Class, Rate)based on four criteria from the first two.
Table 1
EmpID Name
1 EMP1
2 EMP2
Table 2 (A Subtable of Table1 based on EmpID)
EmpID ACDate Class Rate MJob SJob
1 1/6/13 A 15 100 1A
1 2/6/13 B 20 100 1A
2 1/6/13 A 18 100 1B
Table 3
Name WDate MJob SJob Class Rate
EMP1 1/7/13 100 1A A 15
EMP1 2/5/13 100 1A A 15
EMP1 2/6/13 100 1A B 20
EMP2 1/6/13 100 1B A 18
View 2 Replies
View Related
Apr 18, 2007
I have created a Production Tracking database for my company using
Access 2003. Everyone here uses Access 2000, so I have saved the file
as an access 2000 database. I am importing three tables from an ODBC
connection. These tables are being update by another department
constantly. I am using a centralized form for users to view the
information.
If the users are viewing the information and we try to update the
tables they are viewing, we get an error. My question is: Is there a
way around this problem?
This what I have done:
In the module I wrote, I am importing the tables, then renaming them
to the tables the users are viewing, and then altering the structure
of one of the tables. We cannot use linked tables, the reason is we
have only three licenses to access the software through the ODBC
connection. So as soon as three people open the access database they
take those three connections.
Any Ideas?
Thanks,
Phil
View 4 Replies
View Related
Oct 12, 2004
This has to be a common problem.
I have an environment where I have two databases. One holding tables (The Data) and one holding everything else "The Programs" (Forms, Queries etc)
When the application gets enhanced I currently have no problem updating "The Programs" since I can just copy the new MDB on top of the old MDB.
This is not the case with "The Data", if I need to change any of the tables (since they contain live data, that is modified all the time) I have to physically connect to the machine holding the Data.MDB and modify the tables. The problem is there are getting to be many locations where these changes need to be made.
Is there any way to update the tables from a script (I used to use SQL to update remote tables in a mainframe world)
Thanks
View 3 Replies
View Related
Aug 31, 2013
I want the lookup to be performed with criteria based off of the same row that record being selected is on.
I wrote typed out the Columns and rows that my queries return, and attached a screenshot as well.
You enter your phone number which provides you with an indexed location. Based off of that location only certain products are available and I want them to be filtered accordingly. I have it working to some extent, here is an example below:
|Site Name|Phone Number| Location |Products Available|
| Store 1 | 5044645521| KNNRLABR
The lookup displays products available where the indexed products match the same location. Here is what the query returns:
| Location |Products Available|
| KNNRLABR| Product 21 |
| KNNRLABR| Product 9|
| KNNRLABR| Product 7 |
| KNNRLABR| Product 3 |
This is the affect that I want to achieve but, if I add another location like so:
|Site Name|Phone Number| Location |Products Available|
| Store 1 | 5044645521 | KNNRLABR |
| Store 2 | 3609061624 | VANCWA01|
The lookup returns all products available for all the records for each location entered rather than based off of the selected record.
| Location |Products Available|
| KNNRLABR | Product 21 |
| KNNRLABR | Product 9 |
| KNNRLABR | Product 7 |
| KNNRLABR | Product 3 |
| VANCWA 01 | Product 31 |
| VANCWA 01 | Product 8 |
| VANCWA 01 | Product 4 |
| VANCWA 01 | Product 3 |
| VANCWA 01 | Product 1 |
Is there a way I can make the lookup run off of the location specific to the current row that I am working on?
P.S. Here is a copy of the SQL statement I'm using right now.
SELECT DISTINCTROW [DSL Product CLLI's].CLLI, [DSL Product CLLI's].Speed, [DSL Product CLLI's].CLLI
FROM [DSL Product CLLI's], [Copy Of Copy Of Customer NPA-NXX's - ADSL - Data - Internet]
WHERE ((([DSL Product CLLI's].CLLI) In (SELECT DISTINCTROW [Location] FROM [Copy Of Copy Of Customer NPA-NXX's - ADSL - Data - Internet] WHERE [Copy Of Copy Of Customer NPA-NXX's - ADSL - Data - Internet].[Location]=[DSL Product CLLI's].[CLLI])));
Which Returns the Following in my lookup field for Products Available.
View 6 Replies
View Related
Dec 24, 2012
I have an Access db in a 3 person multi-user environment on a Windows network.
There is a "PRODUCTION" db and a "DEVELOPMENT" db.
Let's call them PROD and DEV.
PROD has the most current data, shipping records, item master, customer data, sales, etc. - but not the most current structure.
DEV has the most current struture - all the front end stuff - forms, functions, modules, etc. - but not the most current data.
The way I've handled this in the past (it seemed to work) was to take the PROD db and rename it to PRODX. Then take the DEV db and rename it PROD. Then open DEV (now called PROD) using the usual shortcut to PROD.
So now that opens fine, and I have to update all the tables from PRODX - and I mean ALL of them - since I don't really know what data has changed since the last update.If I try to delete records I get blocked by access because of all the related records (I don't have cascade delete set on every relationship). So I delete the whole table - ALL of them (this requires me to also delete the relationships). Then I IMPORT all the tables from PRODX (these have the current data). But now the relationships are all gone. It seemed at first that the relationships were back and intact - but when I last looked they were gone. So this is my problem.
HOW do I COMPLETELY empty ALL of the tables - or even delete them - and then restore them or repopulate them from an exact copy of the db but with current table data - and WITHOUT affecting the relationships?
Obviously any back end structural changes have to be handled differently. Usually by manually making the same change on the PROD db that I had in the DEV db - because deleting the table will cause the structural changes to be lost.
One last thing - I've been working this way on an un-split db, and now I'm in the process of splitting it - which SHOULD make updates much easier.
View 5 Replies
View Related
Jul 9, 2014
I have a database containing 10,000+ trouble tickets identified by a unique field called "Ticket ID". This is the primary key for my table. (It's on just one table.) These tickets came from an excel sheet that was exported out of a different database. (For various reasons, we've moved the tickets to access.)
Among many descriptor fields, I have a field called "SPR Type" that shows what type of ticket each entry is. (Bug, Defect, Enhancement, etc, etc) The problem we have here is... not all of the ticket entries have an SPR Type listed on my access table because not all of the tickets coming out of the original excel sheet had one.
I was given a new excel sheet containing 400 to 500 ish Ticket IDs and the SPR Type that applies to each of those tickets. How can I take that sheet and use it to update my access table? Ie. Use the excel sheet containing Ticket ID and SPR Type to populate the blank SPR Type fields for each of their respective Ticket IDs?
**Update: I'm trying something like this...
UPDATE [SPR Priority] inner join [EB Update of SPR] on [SPR Priority.Ticket ID] = [EB Update of SPR.Ticket ID]
SET [SPR Priority.SPR Type] = [EB Update of SPR.SPR Type]
But I am getting an error that says "Invalid Bracketing of name [SPR Priority.Ticket ID] and similar errors. What am I getting wrong here?
I thought about an Update query that would populate the SPR Type fields based on Ticket IDs. I could import the new excel sheet into a separate access table and update from there.
View 2 Replies
View Related
Apr 13, 2013
I am having a problem pulling some data from a query to populate text boxes in a form
Text27 = DLookup("'SumOfSumOfDocCount'", "SumTotalPerf", "DateReceived=" & Forms.Tracker.Text23.Value & "AND 'BookedInID'=" & Forms.Tracker.BookedInID.Value)
I am trying to pull the sum of document count from the SumTotalPerf query where the datereceived in the query matches the date on the form and the BookedInID in the query matches the BookedInID on the form, at the moment Text27 just displays as blank with no error messages displayed so I am lost as to what im doing wrong, Ive double checked all the spelling for my column names etc and all is correct.
View 8 Replies
View Related
Dec 4, 2012
I'm trying to create an archiving system, where i use a simple Append Query followed by a Delete Query.
A typical criteria for the Append Query is less than Date()-30...so any records older than 30 days can be appended to an archive table. This works fine when i enter it in the Query Design criteria row.
But, I would like to make this user-defined. I have set up an unbound form as shown in the first attachment...and made a global variable entitled 'ArchiveDays'. I am hoping to use the variable to act as the criteria for the append criteria. (Please note that in the screendump...they can select an option button if they just want to stick to 1 month old. I also show you my assignment operations there).
My question is... how do i get the variable 'ArchiveDays' value to be the criteria for my append query....
View 14 Replies
View Related
Mar 11, 2013
I am working with MS Access. The database has 2 tables.
-Parent and Student and ParentID is the Primary key as a parent may have multiple Students
-There is a form that lets me add students for a particular parent
One of the fields in the Parent Table is FeeDue. I added a field in the Parent Table called NumOfStudents..What I want to do is as follows: When a student is added on the Student Form, I want fee to be calculated automatically for display AND update the FeeDue field in Parent table. Event handler executed when a student is added (checkbox clicked)
OnClick()
{
Read NumOfStudents from Parent Table
if(student_added = true)
NumOfStudents++
else /* This is to cover student withdrawl*/
NumOfStudents--
if(NUmOfStudents = 1)
Fee= 400
else
Fee = 500
}
student_added check box is on the student form NumOfStudents and FeeDue are fields in Parent
View 1 Replies
View Related
Dec 1, 2013
I have a table in my DB called, devices. This contains all the information about various devices we have deployed in the field.
These devices are also contained in 2 other separate MySQL DB's.
What I need to do is add 2 additional field to my access table for the DeviceRecno and DeviceID of the same device from the MySQL DB's.
Adding the field is easy, but I cant think of a way to enter the recno and ID from the other DB's without typing them in manually for each one.
The common between them all is the serial number of the device, and I can get a list of serial numbers, recno's and ID in an excel sheet.
like a vlookup in excel to easily populate all the existing records with the recno and id's from the other db's?
When I created the access system there was no intention to link it to the other DB's for any reason, but that has now changed due to a lot of reasons.
View 4 Replies
View Related
Aug 24, 2015
I want to delete certain records based on the selected date. However, I come across with this is error - Run time error '3464' (Data type mismatch in criteria expression).This part is highlighted in yellow. I even used the debug.print to test out if the sql statement is executed properly.
Code:
DoCmd.RunSQL DelSummarySQL
Here is my full code
Code:
Private Sub cmd_Delete_Click()
Dim DelSummarySQL As String
Dim StartRange As Date
[code]...
View 2 Replies
View Related
Nov 2, 2004
I have imported several Excel files into Access to create tables in the database.
I teach online and basically I need to know how to extract certain bits of data from each table and put them together.
For example:
Table #1 is my student roster list and contains the fields: Firstname, LastName, SchoolName, and several other fields.
Table #2 is a list of schools throughout the state with fields such as: SchoolName, Registrar, ContactPerson, and so on.
There are several other tables involved but I’m trying to make this question as simple as possible and if I can get this question answered, I think I may be able to figure out the rest.
I would like to print a report out for each individual student that will include the school name from Table #1 and match it with the same school name in Table #2 and then extract the pertinent school information from Table #2 for that school.
I have more than one table with a list of schools. Should I name each field that pertains to the school name with a unique name?
To clarify….
Table#1 can have the same school name listed any number of times because some of the students attend the same school.
Table #2… Each unique school name will be listed only once.
These Excel files come to me regularly as they are updated and I am trying to find an easy way to extract the data that I need.
Can someone please tell me how to write this query?
Thanks so much for your help!
View 5 Replies
View Related
May 19, 2013
I have two tables, Table Products & Table Sold and I'm trying to aggregate the products table to reduce the total number of products and I want this update to happen with all tables that share a 1 to many relationship with the product table. (table sold is one of those).I have products apple, pear, bananna. I am now aggregating them to all just be called fruit. Problem is when I make this change in the product table I get this error:
"The changes you requested to the table were no successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."how to go about aggregating data in a table that has 1 to many relationships with many other tables. A
View 2 Replies
View Related
Jan 4, 2014
I need to count records based on multiple criteria from two different tables. I have two tables (i.e. "tblTasks" and "tblTaskHistory"). The tables have a one-to-many relationship based on the "TaskID" field. "tblTasks" has a field called "AssignedTo" and "tblTaskHistory" has a field called "TaskStatus". I need to know how many tasks have been "reopened", the "reopened" status is located in the "TaskStatus" field in "tblTaskHistory". I need this count against a unique listing of employees which can be found in the "AssignedTo" field in "tblTasks".
View 4 Replies
View Related
Dec 5, 2012
I have a main table which is autonumbered on unitid, I have another table which is linked via the unitid which holds items purchased for the main table, so the second table is a subform of the suppliers and will have multiple entries of the same unitid. What I would like to do is when an item is entered into the second table, check the main table to see if it exists, if it does populate the unitid field with the unitid from the main table, and if not exist create a new record in the main table with the next unitid number.
This in essence is what I want, bearing in mind that the tblinvoices is a subform and the tblmain is not open.
If ([tblinvoices]![Unit] = [tblmain]![Unit]) Then
[tblinvoices]![UnitID] = [tblinvoices]![UnitID]
End If
View 3 Replies
View Related
May 23, 2014
I have 2 tables and one query as below
1- Order Table
OrderID | CustomerID | CusromerName | OrdDate
2- Payment Table
OrderID | Amount | PYMDate
3- Order Deatails Query
OrderID | ProductName | Quantity | Price | Total ([Quantity]*[Price])
I want to group the orders based on the sum of total and sum of payments. I made another query as following :
OrderID (From order table) | Payment (from payments table) | Total (From Order Details Query )
At the Total field I set the above fields to
Group By, Sum
and Sum respectively
I got true payments sum, however, the sum total are always duplicated.
Note that the three objects (two tables and query ) have relation (type : one to many) by the order ID
View 10 Replies
View Related
Dec 13, 2007
I'm attempting to build a simple database for a martial arts tournament registration. The weight division (Fin, Fly, Heavy, etc.) depends on the age, the sex and the weight. I've tried this several different ways, most recently by using one table called "competitors" with all the entrant's information, and several other tables based on the age class/sex (i.e. I have a table called Age 8-9 Male with the weights and divisions in it, and other tables for the other ages/sex).
So, basically, what I'd like to have happen is a report that will lookup in the correct table the division, based on the weight entered. In other words, for a record where the age is 8-9 and the sex is Male, the report compares the weight to the values in the 8-9 Male table, or if the record is a 12-13 Female, it looks in the 12-13 Female table for the weight, and places the correct division in the Division field of the report.
Probably going about this wrong.....but any help would be appreciated.
View 5 Replies
View Related
Sep 25, 2014
I am trying to color code text boxes based on the data entered in. The column is "PHASE" and the items entered in are either "1" "2" or "3". 1 is supposed to be Red, 2 Yellow, 3 Green.
I have Microsoft Access XP (I'm sure). I've tried to do it on VB and had no luck. I couldn't figure out Macro and I don't have On Click or Code Builder.
I have tried a couple of different expressions and no luck. I'm definitely an amateur to Access. I'm only using it cause it's what we have in the office.
View 8 Replies
View Related
Sep 11, 2012
I'm able to import new data from excel just fine, but I can't import updated data from excel due to duplicates not being allowed for a particular field. Is there a way to keep from importing duplicate records based on one field, but still import data from other fields where the information is different from the excel file?
View 5 Replies
View Related
Mar 20, 2013
I have a report that gets its data from a query. I need the query to run before the report based on criteria based from two combo boxes on a form.
View 3 Replies
View Related
