Updating Data In Table B Based Off Table A - Duplicates
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 Replies
ADVERTISEMENT
Aug 8, 2005
hi, I have 2 Tables: tablea & tableb. The field names in both tables are: user_id, firstname, lastname, from these i have 2 querys (querya & queryb) both of which just look at the fields from the repective tables, & from this i have 1 form (entry form) that pics up the fields from querya. I would like to be able to enter details (records) into the entry form which puts all the data into tablea then automatically copies all the information into tableb, then automatically checks to make sure there are no duplicate records in tableb and finally deletes all the records out of tablea (probably after the form closes). This might sound a bit strange why i want to do this but I hope if its possible it could be used in a larger database.
I think i need to do querys but not sure what I need to do, Any advise would be very helpful.
View 3 Replies
View Related
Sep 29, 2013
I have add my two tables
Main table calls Attack
Sub table calls Research
the main table attack is linked with sub table Research by Attack.ID and Research.attack_id from form automatically
The sub table Research have more than one record which is linked into the main table Example:
How can I get the data which is marked in blue into record 24 and the data marked in Red into row 23
using Unite_Equal column?
View 3 Replies
View Related
Mar 12, 2015
I have two tables, tblCandidate and tblNewHire. I am trying to transfer candidates to tblNewHire based on the date they are hired. However, I would like the database to skip any records that are already in tblNewHire, just in case someone inadvertently puts in the wrong date when running the query.
I created an append query (SQL below) but this enters the data regardless of duplication. I need to be able to check both SSN and LastName against the new table and can't set either one to be unique values as it's possible there could be two candidates with the same last name.
Any way to do this by creating a recordset and looping through to check for dupes.
INSERT INTO tblNewHire ( SSN, FirstName, MiddleName, LastName, Phone, Email, EOD, HiringMechanism )
SELECT tblCandidate.SSN, tblCandidate.FirstName, tblCandidate.MiddleName, tblCandidate.LastName, tblCandidate.Phone, tblCandidate.Email, tblCandidateTracking.ActionDate, tblCandidateTracking.HireMechanism
FROM tblCandidate INNER JOIN tblCandidateTracking ON tblCandidate.SSN = tblCandidateTracking.SSN
WHERE (((tblCandidateTracking.ActionDate)=[forms]![frmNewHireMain]![txtEODDate]) AND ((tblCandidateTracking.LastAction)="EOD"));
View 2 Replies
View Related
Sep 12, 2014
How to make a form open with data from a table based on a date and time in the/a table?
View 7 Replies
View Related
Oct 29, 2014
Anyway, I would call myself intermediate level at best with Access. I never expected to have to do so much with it, but when my bosses found out I could do Access basics, they began demanding more and more.
I manage an EMR from which a datapull occurs on discharge to various access databases.
They wanted me to add a triage patient data pull to track what procedures are being done to triage patients. So I built a database with the following 2 tables.
tblTriageVisits and tblTriageProcedures
tblTriageVisits has the following fields
-Patient ID
-Export Time
[Code].....
The tblTriageVisits stores all the patients triage visits. The other table stores what was done to each patient on those visits
It should also be noted that this EMR exports times in number of seconds since 12/31/1975, so TimeProcFMS is the number of seconds since that date for example.
The pulls work great and the duplicate record elimination method works great.
I have to design a couple of different reports based on this data and one in particular has pretty much flumoxxed me.
I need to make a report that lists each patient by date they were there and then shows what was done to them. I have tried several different variations of this as both queries and reports. The report breaks down when I try to get it to show just those procedures that would be for that triage visit. I got to the point where it would group by day and then sort the patients alphabetically. Then I added in the visit fields. And it would put all triage procedures for that patient from all their visits in each visit.
So, I was like great, let me filter out those that don't occur between the two dates, admission and discharge
I used the following two formulas (the first one on the procedure name field the second on the procedure date field)
=Switch(DateAdd("s",[TimeProcFMS],#12/31/1975#)>=[Admission DateTime OB DateTime] And DateAdd("s",[TimeProcFMS],#12/31/1975#)<=[LD Discharge Date/Time DateTime],[Procedures FMS])
=Switch(DateAdd("s",[TimeProcFMS],#12/31/1975#)>=[Admission DateTime OB DateTime] And DateAdd("s",[TimeProcFMS],#12/31/1975#)<=[LD Discharge Date/Time DateTime],DateAdd("s",[TimeProcFMS],#12/31/1975#))
And they work, sort of. They do eliminate those procedures that occur outside of the date range I am looking for. However, the access report still leave spaces in the report where the filtered out items would be.
Did I approach this problem correctly? And if so, how do I get rid of those spaces?
Note, I can read and mostly understand SQL, but am not comfortable working in it yet. And the Access SQL box gives me a headache, it is so jumbled up.
View 14 Replies
View Related
Dec 7, 2007
Hi All,
I have an MS Access table that currently links into word as a mail merge. Great!
Sample data as follows:
Tutor Student
Mr A Donald Duck
Mr A Mickey Mouse
Mr A Minnie Mouse
Mrs B Joe Bloggs
I want to produce a mail document that allows me to address each tutor separately, along with the respective student names in a grid.
Now I've setup the document, but when I view each document it produces the list of all students for each tutor.
e.g.
Dear Mr A
Your students are:
Donald Duck
Mickey Mouse
Minnie Mouse
-------------------
Dear Mrs B
Your Students are:
Joe Bloggs
Is this at all possible?
Thanks:)
View 1 Replies
View Related
Feb 12, 2008
Hi this is my first post... so hi all :)
ok what i have is a table with contact details 900k plus
there are about 90k of which are duplicates.
this is the basic feilds that are important in this case.
Id, data_source, data_recived, data_code,
what i want is to have a table with unique records (no dups in data_code)
this table will look like this...
Id, data_code, Num_dups, dup1_source, dup1_date, daysbtw_Dup1_dup2, dup2_source, dup2_date, daysbtw_Dup2_dup3 ,dup3_source, dup3_date, daysbtw_Dup3_dup4 ,dup4_source, dup4_date,
I know there is no more than 4 dups of each record.
what i want from this is a table that will give me a record of how many dups for each record then all the dates that they were added and the date between each record entry.
if anyone can help it would be great .
thanks in advance.
View 6 Replies
View Related
Jan 5, 2013
My question is this: I have a table where I'm entering employees' hours worked. Basically, it's something like this:
ID WorkerNumberDateworkedTimeStartTimeEnded
121/2/201310:00:00 AM3:00:00 PM
221/3/20132:00:00 AM11:00:00 AM
321/4/201312:15:00 AM11:30:00 AM
421/5/201310:25:00 PM11:00:00 AM
531/2/201311:00:00 AM3:30:00 PM
631/3/201312:00:00 PM10:00:00 PM
731/10/20137:00:00 AM4:00:00 PM
I have a query that (easily) determines how many hours an employee has worked on any given day. What I can't figure out at all, is how to write a query that can figure out how much time an employee had off in between shifts.
Thus far I'm able to run a query that separates this main table into individual workers by their id numbers, but can't figure out how to determine time off between shifts - as the last hour worked one day, and the first hour worked the next day are on two different lines (they are two different table entries).
View 4 Replies
View Related
Jun 17, 2007
Hello
I have a table with 3 fields:
- ID no (primany key), Time1 and Time2
I have 3 records with ID no 1 to 3.
So my table look like this
ID no: Time1: Time2:
1 [emty] [emty]
2 [emty] [emty]
3 [emty] [emty]
I have an Excel document with the data for field Time1 and another Excel dokument with the data for field Time2. Excel doc1 have 2 colums named ID no and Time1. And Excel doc2 have 2 colums named ID no and Time2.
How do I import/update these data into my table?
I have tried wiht a adding-quiry but it only works if the table is complety emty and only with one Excel doc. Trying the second Excel doc afterwards only makes an error and no fields are updated.:confused:
View 4 Replies
View Related
Sep 4, 2006
Hi all
I have been nutting out this problem but have been unable to find a solution, even my learned colleague is at a loss to help. This is an data update query using combo boxes and forms.
I have 2 databases, Data and App, I have linked 2 tables, Main and Supervisor from the Data.mdb to the App.mdb. Supervisor has 2 fields ID and Name. Main has multiple fields but is linked to Supervisor by the ID field. I have a query that gets details from the Main Table and this is entered into a form. I deleted the SupervisorID text box and inserted a combo box using the wizard, it gets its data from a query that gets details from the Supervisor table showing the Supervisor name, the ID field in the dropdown is hidden. The combo box selection is held in the SupervisorID of the Main table.
What I want to do is change the Supervisor name using the combo box however I am unable to select another name from the dropdown list.
I have tried changing the Data Entry property of the form to Yes, this did not work. Allow edits is set to yes. I have tried adding another combo box which gets the data straight from the Supervisor table but I have the same problem.
Can anyone help, we think it is a simple property setting but all we have tried has failed to date. Thanks in advance.
Craig
View 4 Replies
View Related
Aug 11, 2015
I have a VBA script that looks at a date on a form, adds a certain number of months (selected by the user). at the moment i can output the new data to a message box. I want to update a table with the new data value.
This is the code i have so far.
mySQL = "UPDATE job master file " & _
"SET [BBDate] = " & newdate & _
"WHERE [job name] = " & strname
View 10 Replies
View Related
Nov 14, 2007
Hi everyone,
I was wondering if this is possible to do:
I have a Form that reads from one Linked Table and Updates a Local Table. Is it possible to move the Local Table to the Back End and still be able to append fields and change field names etc?
If anyone can help, that would be greatly appreciated. Thank you in advance.
Bear
View 1 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
Dec 6, 2005
Hello,
Been wondering how I can update fields in my Access database table using data that lies in an excel spreadsheet.
They have a common row ie say account number and other common fields that need to be updated.
thanks
View 1 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
Jul 28, 2014
I have a MS Access table containing Dependent social security numbers but some of the dependents social security numbers are blank.
I have a MS Sql Database that contains a table with most of the missing MS Access table dependents Social Security numbers.
How can I take the Dependents social security number from MS Sql Database and copy/update the MS Access table.
I tried the sql code below created from MS Access and it matches 453 records out of 460 dependent social security numbers but how do I update qryGHIAccuracyFile_MissingDepSSN.SSN with the social security number from dbo_depfile1.dep_depend_ssn
Code:
SELECT dbo_depfile1.dep_depend_ssn, qryGHIAccuracyFile_MissingDepSSN.SSN
FROM qryGHIAccuracyFile_MissingDepSSN LEFT JOIN dbo_depfile1 ON qryGHIAccuracyFile_MissingDepSSN.MemberSSN = dbo_depfile1.dep_ss_nbr
WHERE (((dbo_depfile1.dep_first)=[FirstName]) AND ((dbo_depfile1.dep_last)=[LastName]));
Updating MS Access table dependent blank SSNs with SSN in MS Sql Database.
View 2 Replies
View Related
Feb 1, 2005
hi
i am getting stuck while updating the data in the database table using a command button in the MS Access2003 forms. when i click the command button in the form, a message "Run time 2185: you cant refer to a property or method for a control unless the control has a focus". the code is as follows.
rivate Sub Command10_Click()
Dim query As String
query = "select RESOURCEINFO from tbl_control where CONTROLNAME='" + Combo4.Text + "'"
If (cn.State <> 1) Then
cn.Open "dsn=ABC", "", ""
End If
rs.Open query, cn, adOpenKeyset, adLockOptimistic
RESOURCEINFO.SetFocus
rs.Fields(0) = RESOURCEINFO
rs.Update
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
On Error GoTo Err_Command10_Click
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Exit_Command10_Click:
Exit Sub
Err_Command10_Click:
MsgBox Err.Description
Resume Exit_Command10_Click
End Sub
i am a beginner. any help would be greatly appreciated.
View 2 Replies
View Related
Oct 31, 2013
Right now I have a subform with a combobox that pulls it's data from a table. I want the user to either select an existing item or type in a new item and have a macro create the new table row. What I have right now works in the sense that it prompts the user if they want to creat a new item and the new item is created (and I can see it in the combobox list), but I'm still getting an error saying that the item does not exist in the table forcing the user to manually select the newly created list item they just typed in.
Code:
Private Sub MaterialCostCode_NotInList(NewData As String, Response As Integer)
Dim rst As DAO.Recordset
'Update value list with user input.
On Error GoTo ErrHandler
Dim bytUpdate As Byte
[Code] ....
It appears that the new item doesn't always show up automatically and requires the form to be refreshed, so now I need to figure out how to get it to consistently appear right away without a refresh.
View 3 Replies
View Related
Sep 13, 2013
Using Access 2010. Fairly new to automation and macros.I have two tables (tblProductReceived and tblBins) and a form (frmProductReceived).A field in tblBins corresponds to a location the bin is at (named BinLocationID) and the tblProductReceived table tracks product that a specific bin has received.
What I need is for the tblProductReceived field PRLocationID ([tblProductReceived].[PRLocationID]) to be automatically populated with where the bin is at ([tblBins].[BinLocationID]) when selecting a specific bin in the form (frmProductReceived).
View 1 Replies
View Related
Sep 29, 2013
How can I update (some columns) in a table from the same table based on a Criteria column in the same table.
View 2 Replies
View Related
Apr 19, 2013
Here's a query that the bottom listview in the attached form i.e. a listview representing a table of calls(many) to fims (1 top listview)
Code:
SELECT calls.id, calls.firm_id, calls.called, calls.said, calls.spoke_to, calls.next
FROM calls
WHERE (((calls.firm_id)=[firms].[id]))
ORDER BY calls.called DESC , calls.next DESC;
When I run the thing...I get a dialog asking me for firm id.
I want to change this so when I move up and down the firms LV (top)... the bottom LV updates taking firm id from the top LV with focus.
Access 2003.
View 2 Replies
View Related
Nov 26, 2013
I'm using an UPDATE query to update records in one table (tblMain) from another table (tblTemp)
Here is my SQL :
Quote:
UPDATE [tblMain]
INNER JOIN [tblTemp] ON [tblMain].[MainField1] = [tblTemp].[TempField1]
SET [tblMain].[MainField2] = [tblTemp].[TempField2];
I only want to update the records in tblMain which have a corresponding record in tblTemp (linked by MainField1 / TempField1)
If any record doesn't appear in tblTemp, I want tblMain to retain the existing value for that record.
However, it appears that in such situations, the record in tblMain has it's MainField2 value set to null / ZLS.
I've tried using LEFT JOIN and RIGHT JOIN and also tried WHERE clauses but the result is the same every time.
View 3 Replies
View Related
Mar 29, 2013
I have a combo box (cboManifestNumber) that is based on the following table:
tblManifestData
ManifestDataIDPK (autonumber PK)
ManifestNumber
RemovedDate
ManifestComments
TsdfIDFK (FK frm tblTSDF)
This table is related to:
tblTSDF
TsdfIDPK (autonumber PK)
I need to be able to update tblManifestData with a new manifest number and manifest comments, along with assigning it a TSDF. how to be able to enter a new manifest number and the associated data without having it create two lines in tblManifestData. I thought that I could enter a new manifest number, then requery the table and form so it shows the complete list of manifest numbers (including the recently entered one) while staying on the newest entry.
View 2 Replies
View Related
Nov 9, 2006
I have a table in the Access DB which is structured this way:
ITEMID
FIELDNAME
FIELDVALUE
The Key is ITEMID,FIELDNAME
I need such a configuration because the items I need to store can have whatever fields the user could like. A simple example of the data contained could be:
ITEMID;FIELDNAME;FIELDVALUE
1;FIELDA;1111
1;FIELDB;2222
2;FIELDA;3333
2;FIELDB;4444
I need to load a datagrid in VB.NET displaying a table like this (according to the above example):
ITEM;FIELDA;FIELDB
1;1111;2222
2;3333;4444
I used to just load the data from Access and then build the grid directly from VB, but the records are becoming too many and this approach is just too slow. I was thinking about creating the table I need directly from a query, and then loading the result in a grid; however, this is something I'm having problems doing. Could anyone help me? Thanks.
Just a couple of notes:
- not every field is necessary present for each ITEM, so I need to handle the possibility of DBNull entries for some fields.
- If it can help, I know which fieldnames I can get before querying the DB
View 7 Replies
View Related
