Tables :: How To Not Allow A New Record To Have Similar Values As Existing One
Jul 29, 2015
I am having an issue with duplicates in my table. I have a table, called "Part Mods", that has about 12 fields in it. the first 2 of them are "Mod" and "Part Number". I have a form where someone can insert data into this table and what i want is for them to NOT be able to insert a record with the same Mod/Part Number combination of an already existing record. I have "indexed" turned on but I am pretty sure that is for every field.
View Replies
ADVERTISEMENT
Apr 5, 2006
Can someone please help with the following:
I am trying to join two tables in a query. One of the tables has only the fullname while another has only the surname (this table is imported from an external database). I need to be able to match the two tables up. Can anyone please suggest how to do this or even if this is possible.
Thanks
View 6 Replies
View Related
Jun 9, 2014
Is there a way to merge duplicate/similar Access 2010 records into one record?
I have an Access table with 1,000 duplicate records, although they are similar and not exact duplicates. As you can see below, some records contain information that other records do not. Yet, the primary key is the same for all duplicate records. I want to find a way to merge data from filled cells of duplicate records into empty cells for each duplicate record. I do not want to concatenate the data (i.e. combine last and first name, etc.). I only want to fill empty cells if there is a match for it in a duplicate record. I will delete the newly exact duplicate records later. Short of correcting the records by hand.
Example
Code:
LastName FirstName SSN Address Phone Email
Doe John 123-45-7891 123 Anywhere St. NULL john(at)gmail.com
Doe John 123-45-7891 NULL (123)456-7890 NULL
Desired Result
Code:
LastName FirstName SSN Address Phone Email
Doe John 123-45-7891 123 Anywhere St. (123)456-7890 john(at)gmail.com
Doe John 123-45-7891 123 Anywhere St. (123)456-7890 john(at)gmail.com
View 2 Replies
View Related
Mar 8, 2015
Exporting all the OLE files in a 2003 database.
Any way to automatically connect these file to an attachment field in the existing record.
View 7 Replies
View Related
Jan 31, 2008
This may be a simplistic question but I'm having some diffuculty figuring it out. I have multiple rows of data where I need to use the "Unique Values" property in my query to limit only what I need. However, I have a "time" field and their are similar times on different records. So say I have a 100 records but 8 of them have the same "time" value, how can I use someting like the "Unique Values" property to get my rows down from several hundred to 100, but not lose the 8 similar rows and end up with 92 rows, when I have to turn around and sum these times and need all the times to be there?
Maybe I'm approaching it the wrong way and should be doing a SUM of "times" to begin with for that field, but I'm not being able to get that to work either. Any suggestions? /Thanks
View 6 Replies
View Related
Oct 24, 2011
I am looking for the way to represent data with the similar column value to one row.A query retrieves 3 records AS IS, but I need one record.
Table:
Name / 1 year / 2 year /3 year
AS IS
Name 1 year 2 year 3 year
1 John Smith A
2 John Smith B
3 John Smith A+
=================================
TO BE
Name 1 year 2 year 3 year
1 John Smith A A+ B
View 4 Replies
View Related
Jul 19, 2014
I have being playing with ms access but I really don't know much about it or databases in general.I have created a very simple database to gather twitter following/followers data for research purposes.One table (table01) has a field for the "boss" user (=the user who I gather data for), another field for "client" (=bosses followers or friends).Both fields are numeric and contain the users id's.In order to distinguish if the link is follower or friend there is a third field, called type which can be either 1 (=follower) or 2 (=friend).So the data would look like this:
boss - client - type
12345, 67890, 1
12345, 54321, 2
If user with user id 12345 had a follower (type 1) with user id 67890 and a friend with user id 54321...In order to avoid getting duplicate rows I also added a unique identifier which is of the form boss_id-user_id-type.So the above row looks like this:
12345-67890-1, 12345, 67890, 1
12345-54321-2, 12345, 54321, 2
That works just fine.For several reasons I also needed data of the form source - target.So I also made another table (table02) of this form.
67890, 12345
12345, 54321
...
In table 2 you don't need the "type" field since the position of the user id shows the type of relationship.Still, you need a unique identifier in order to avoid duplicates, so I added on with the form: source_id-client_id..So table02 lookes like this
67890-12345, 67890, 12345
12345-54321, 12345, 54321
...
Both tables also have a date/time stamp for each line.As you can see, table01, having also a type field is bigger than table02.The problem is when I try to append data, exactly the same data in both tables.Appending data to table01 is ok, while appending data to table02 (which is smaller, having one less field) takes a really long time, maybe 10 times as long as appending data to table01.To make sure that no query's are causing the problem I have tried first creating temporary tables with the data to be appended, no duplicates, nothing that would cause the database to make extra calculations and used a simple update query with no filters to append data.Still I get the same result, table02 takes a very long time to finish while table01 finishes in no time.
View 2 Replies
View Related
Apr 29, 2015
How to fill values in a new record with data from previous record?
I've total 11 columns in a table and values in 3 4 columns are repeating for few rows before it needs to be changed eventually. I want to fill these rows with values from previous record.
View 10 Replies
View Related
Apr 2, 2008
I have 5 tables which comes from different departments in our company.
All of them have the same key "project no" but hold different information in other colums.
As a starting point I have secured that they all have same no. of records meaning all "project no" are in all tables but some table might not contain other information in that record than the project no.
I have put them into a quirie and on a form I am able to look at all data from all 5 tables.
I have linked them one to one and it works fine untill I add a record to one of the tables. Afterwards I am not able to write into fields from other tables because there is no automatic creation of the record in the other tables.
Please How would this be best/easiest to make for me. ?
The reason why I have to keep the 5 tables is that regular updates are avaiable form any of them.
Appricate any comments which can make me any progress.
View 3 Replies
View Related
Jun 5, 2007
Hi there, I'm quite new to Access.I have two tables which have very similar Data, but laid out differently. The main similarities are things like FirstName, LastName, Company. What I need to be able to do is merge the two tables together into one super table. I've tried linking FirstName with FirstName and LastName with LastName on both tables, and running a query, so I can check for duplicate names. But all that brings up is a list like this:Steve Smith Steve AbrahamsSteve Smith Steve McDonaldSteve Smith Steve PerrySteve Smith Steve Vere(those columns would be Old FirstName, Old LastName, New FirstName, New LastName)There are fields in the old table that are not in the new, and vice versa. But the core fields are labeled the same. What's the best way to proceed here?Cheers,Hob
View 5 Replies
View Related
Mar 29, 2008
I have 3 tables, with the same field in each of the three tables. I need to find numbers (within those fields) that are similar in all 3 tables. If a number appears in all 3 tables, then all the data for that number need to be pulled from each table and placed in one row, all corresponding to that one number.
I can get this to work for 2 tables, using a query, but not three.
View 7 Replies
View Related
Sep 18, 2015
How would I go about joining tables with similar fields? I currently have three tables that show standings of teams (East, Central, and West). They all have the same fields:
East
Team Name
Wins
Losses
Ties
Points For
Points Against
Central
Team Name
Wins
Losses
Ties
Points For
Points Against
West
Team Name
Wins
Losses
Ties
Points For
Points Against
How would I combine them in a query so that all of the data from the tables are compiled into one table:
Overall Standings (East/Central/West)
Team Name
Wins
Losses
Ties
Points For
Points Against
View 1 Replies
View Related
Jun 1, 2007
Hi Everyone,
I am trying to compare/relate two columns in two tables to each other in access, where the results shoud return similarities among both colums from both tables.Even if one column has some parts of it.
Example;
T1 T2
Name Members
John Johnson.kay
mike mike
Daniel Danielson.mic
Richard Richardson
I tried; "like[T1.Name]*" in the criteria section of Members.Need Help pls.
View 13 Replies
View Related
Oct 18, 2007
I have a database of Assessments, each record having multiple subforms.
I am working on a button, which creates a "similar" (same) assessment, copying over all the subform records/selections.
So, after I actually insert a new assessment, pasting all the values from the original Assessment:
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdSaveRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPasteAppend
I need to copy over subform contents, for which I use an Append Query (actually, I use its SQL in VBA code to pass it the right parameters).
Now, naturally, I ran into Key Violations, because taking 50 sub-records from the original they would have their own AutoNumber Keys (ID's) and I can't append them into the same table.
My question: how do I copy over the same records (appending FROM and TO the same table) but cause the table to insert AutoNumbers for the records being appended?
Thanks!
View 1 Replies
View Related
Jul 17, 2014
I have a database which is importing several Excel workbooks, each with multiple worksheets. Every workbook has 20 worksheets, with the same 20 worksheet names. When they are imported they come in as one table for each worksheet, named tblWorkSheetName_X with X starting at 1 and increasing for each worksheet brought in with the same name. So if the worksheet names are A-T I have tblA_1 through tblA_6 and likewise for B - T.
I would like to combine all of the tables which come from similar worksheets into one table (one table per name).
I.e. I want to combine the data in tblA_1 through tblA_6 into a singular tbl_A and likewise for tables B through T. So in the end I will have one table for each worksheet name A-T. t how to code this successfully?
View 14 Replies
View Related
Apr 16, 2013
Is there another field that can be used beside the lookup field in an Access table? I am currently using the lookup field as a multivalued lookup field and I am limited to the the things I can do with it when creating a report or a query on that field. Is there a similar field in Access 2010 that has the same look as a lookup field and allows for multiple values to be selected?
View 4 Replies
View Related
Apr 21, 2014
I have been looking around for a while now to learn how to show in a subform similar records from the same table, and a way to link them together.
I work for a mental health organization and we have a call log database that we create a new record every time a person calls us - this allows us to track the outcome of these calls. Over the last 3 years we have about 10% repeat callers. I am trying to find a way, when creating a new entry, to see if this person has called before, and if they have, link their past contact (record) to the new contact (new record).
For example.
Caller: John Smith (555) 555-5555 (this is the new record)
in the subform, a list would populate all the 'john smith' records with an option to link or attach them to the new record.
View 8 Replies
View Related
Jan 30, 2006
I am pretty new to access, I will get right to it.
I am scoring rating scales. I want to be able to enter just the responses from a person, say on 80 questions (preferably into a form), where all the answers are 0 to 3 and have Access add the questions into the right groups to calculate all the sub-scores of the rating scale.
For example, Scale A may consist of Questions 1, 6, 15, 29, 35, and 70
I need access to add those up and save them so I can use them in a report.
I also need access to add the frequency of certain questions that were responded to with the answer 3.
I have gotten Access to calculate the scores in a form by adding extra variables to the form, erasing the variable name out of the white box and putting in the syntax =[Q1]+[Q6]+[Q15], etc. in place of a variable name but it just sits there on the form and doesn't store it anywhere.
Thanks
View 2 Replies
View Related
Dec 13, 2007
I've been racking my brain the last couple of days trying to figure out how to solve this problem, and I believe I've reached the end of my rope. I have a feeling that this isn't very difficult to a more savvy Access person, but I am at a loss.
Here's the situation. I have received a fairly large DB containing CCTV data for sanitary sewers. There are primarily two tables I'm dealing with, one lists a number (auto-number) for each pipe that was televised. Simple enough. The other uses this legacy number to show all deformities or service leads within a particular length of pipe. For example, for run X, there may be 7 rows in the table with X as the ID, one for each service lead along that length of pipe (I hope this is making sense :()
Ultimately, we need to tie this database into our GIS theme. To do this, I will need to add to the PipeID number from our GIS theme to the access table. What I've done so far is to create a new table in the DB with the number for each pipe televised, and I've manually added the corresponding PipeID number from the GIS in the second column. What I'm hoping to do is add a new column to my occurrence table so that for each occurrence X, I can add the GIS PipeID number. Perhaps this would make more sense:
Run # PipeID
1 S143
1 S143
1 S143
1 S143
2 S231
2 S231
2 S231
2 S231
3 S543
3 S543
3 S543
I've gone ahead and created the relationship between the newly created table and the existing table based on that auto-number field, and I've made the new PipeID column a combo box. This shows all of my PipeID numbers, which is a good thing. I'm hoping there's a way for it to automatically recognize the auto-number field and populate the PipeID field accordingly.
Have you ever known what you want to say, but not quite understood how to say it? That's kind of how I feel about this question, and I do apologize if I've made no sense. But if I have, and anyone has any suggestions for me, they would be greatly appreciated.
Cheers,
Azimuth
View 3 Replies
View Related
Nov 28, 2013
I had a text box in a contacts DB form for the State field (Named txtState). It worked fine. I decided to replace it with a combo box. Now not only will it not display the existing values, but to rub salt in the wound it won't allow me to select from the drop down list. The error message that says that the field is too small for the value. I tried changing each of the yes/no properties one at a time but none of them change the outcome.
I have attached a couple of screenshots:
ErrorMessage.jpg
Properties.jpg
View 3 Replies
View Related
Nov 24, 2014
I've been asked to get some information from my database and I'm a bit stuck.
I have a list of refunds in tbl_main and each one includes a dateReceived. I make a record in either tlk_located, tlk_unableToLocate or tlk_bulk depending on the outcome when we're trying to send the money back to whoever it belongs to. Each table has a time stamp (named locatedTime, unableTime and timestamp respectively) field
My manager wants me to report how many entries were unworked on each day in the year, and what the value of them was. An entry is unworked if there is no entry in either of the 3 tables.
So I need a query that lists a range of dates, and for each date counts the number of entries where tbl_main.dateReceived is <= to that date and either has no record in located,unable or bulk or has a record with a timestamp > than the date. (It has been processed now, but hadn't been on the date we are looking at)
I can manage a query that looks at a certain date that it prompts for on each run:
Code:
SELECT Count(tbl_main.trust2PK) AS CountOftrust2PK, Sum(tbl_main.amountRefunded) AS SumOfamountRefunded
FROM ((tbl_main LEFT JOIN tlk_located ON tbl_main.trust2PK = tlk_located.trust2FK) LEFT JOIN tlk_unableToLocate ON tbl_main.trust2PK = tlk_unableToLocate.trust2FK) LEFT JOIN tlk_bulk ON tbl_main.trust2PK = tlk_bulk.trust2FK
WHERE (((tbl_main.dateReceived)<=[cutoffDate]) AND ((tlk_located.locatedTime) Is Null Or (tlk_located.locatedTime)>[cutOffDate]) AND ((tlk_unableToLocate.unableTime) Is Null Or (tlk_unableToLocate.unableTime)>[cutOffDate]) AND ((tlk_bulk.timeStamp) Is Null Or (tlk_bulk.timeStamp)>[cutOffDate]));
I would like a query that lists all dates in a range, and shows the same information for each day listed.
View 9 Replies
View Related
Nov 26, 2013
I have a query in design view that consists of two calculated fields at the moment. One of the calculated fields is supposed to add one year onto a value from another date/time field called "Last_Date."
The expression I've written in the "Field:" box in Query design view is
Expr 2: DateAdd('yyyy',1,[Last_Date])
When I try to run the Query it doesn't work! Access just prompts me to enter a parameter value for Last_Date.
View 1 Replies
View Related
Oct 23, 2013
I have a database used to manage teaching assignments (which kid is assigned to which teacher so to speak). I have this relationship defined through three tables, a teacher table, a student table, both with unique ID's. The third table is used to define the assignment. Also, the kid table has an extra GroupID. The group ID is what is used to define. So in the definition table, Teacher 1 is assigned to Group 1, and so on (though their may be 20 kids in group 1). When a new teacher is added to the teacher table, I need it to add it to the corresponding field in the definition table. The groupID is in the table as an Autonumber so that will populate automatically.
I need to appendTeacherID to tblassignment (TeacherassignmentID) and have only one occurrence of the TeacherID. So, if I have teachers 1-8 listed, each assigned to a group# in the tblassignment, and I add Teacher 9, I need it to add Teacher 9's unique ID to the TeacherassignmentID field without adding 1-8 again. I can't figure out how to "check" for ID's 1-9 and add only those I've added to the teacher list that aren't already assigned to a group.
I've tried a few different SQL queries append/select queries but nothing seems to do what I need it to do....
View 1 Replies
View Related
Jul 18, 2013
I have written the following code. I want to change the status of my order when reminaing products shipped is equal to zero. When I run this code I get error " Cannot update. Database or object is read only". I think that this error is because that I am using the front end of the database.
Code:
Private Sub Form_AfterUpdate()
Dim dbs As DAO.Database
Dim rstTest As DAO.Recordse
Dim strQuery As Strin
strQuery = "SELECT OrderingT.Order_ID, OrderingT.UnitsRequested,OrderingT.OrderStatus, ([UnitsRequested])-Count([Product_ID]) AS [The Remaining
[code]....
View 12 Replies
View Related
Dec 24, 2013
I have the employee data record... I want copy of the same record to insert in table but with different employee id and Site through forms. StaffNo is the primary key.
For example
existing record
StaffNo, Site, Name, hiring date, etc
AKA-111, 10, John, 25-10-13
new record
StaffNo, Site, Name, hiring date, etc
ALM-123, 14, John, 25-10-13
View 13 Replies
View Related
Nov 10, 2014
I am very new to Access and Visual Basics. I am trying to write a VBA code to do the following."If any table(s) exists in the current db, drop all the table(s)."
Basically, I am trying to drop all existing tables.
View 3 Replies
View Related
