Only Updating New Rows In Duplicate Table
Jun 5, 2007
Good morning to you all.
I have a problem that I thought would be quite simple......
But 3 days later I am still trying.
I have a form which populates a table by one set of users (Simple enough) they can only add records.
I also have another set of users who want to access that table through a similar form but with a couple of added fields.
They will not be able to change anything only add their comments to what they see in the new fields supplied. Once this has been done it cannot be edited ever.
So I made a "Make Table Query" and added 2 new fields to that, so when I clicked on the query it came me the last update from the previous table. Problem was everything that was added in the extra fields were deleted!!!!
I would like to update the 2nd table with items that have been newly added to the first table, leaving all the items on the second table the way they were!!!!!!!
Anyone got any ideas please
View Replies
ADVERTISEMENT
Oct 11, 2007
I'm a bit in over my head. Unfamiliar with Access macros, I need to write a bunch of them for work, and soon. Unixen I can deal with, largely undocumented convoluted Access macros are something else....
My current problem is: I have a table. The first column has a value in it for almost every row. However, there are six other columns after it. I need to write a macro to automatically delete all of the rows that don't have data in the last six columns. Microsoft Help is, as always, of zero use. The FindRecord feature allows me to use expressions to search, but of course, the help fails to tell me what syntax Access uses.
Oh, and it asks me to select a table from the drop-down list. Can I use wildcards here? Is there a way to get it to automatically open the newest table, or will we have to change the macro accordingly each month?
Most tutorials/guides I'm Googling rather brilliantly repeat the same things the help does, AKA, are useless. (Why do they bother writing them if you're not giving new information...?)
I'd ask about the other various access questions I have, but I can pick them up as I go, this is the most pressing question.
View 2 Replies
View Related
Oct 22, 2006
I need to append time log to time_log table. I could append table without errors.
But if I append the second time it duplicates. I cannot index Employee ID & Date coz there will be multiple entries.
Employee_ID, Date, IN, OUT
I need an append query which doesnt duplicate rows.
View 3 Replies
View Related
Aug 7, 2007
I have a list of projects and am creating a query that tells me their duration based on beginning and ending dates. These beginning and ending dates can be edited by the user, and frequently, there are multiple beginning dates. When I query the projects, instead of getting unique project numbers on the left hand side to create unique rows, I get many of the same projects with different start dates. I would like to use the FIRST start date only for every project and not have multiple rows with the same projects being counted multiple times.
Is there any way to delete the excess rows and keep the original row? I've read a lot about using DISTINCT and UNIQUE INDEX with SQL but it seems as though these are only available for tables and not queries. Bright ideas, anyone?
View 6 Replies
View Related
Jun 17, 2013
I am working with an Excel file of raw data aggregated from an annual customer relationship survey that has been sent out since 2010. The file has approximately 20,000 rows, meaning the survey has been taken around 20,000 times. Unfortunately, each time a customer takes the survey, it is included in the raw data as a separate entry. Therefore the file has numerous duplicate email addresses, corresponding with unique data for each time the survey was taken. Another issue regarding the data, is that in the first year the survey was sent out, the distribution mechanism "broke" and the survey was sent out multiple times (and completed multiple times) in the same year by the same customer, so the surveys are not necessarily uniformly distributed, if that makes sense.
I have been interested in isolating the common respondents (those who have taken the survey across multiple years, albeit not necessarily consecutive years). Up to this point, these respondents have been isolated manually using a pivot table, however I am now looking to enter the raw data into SPSS (a statistical analysis package), in order to view the drivers of these common respondents.
Therefore, I would like to be able to isolate these common respondents and the data corresponding with their surveys from the raw data in a separate worksheet or file. I have tried various formulas to do so in Excel to no avail. Is there anyway to accomplish this in Access or would a more complex database be needed?
Ideally the final product would have the common respondents' information from multiple surveys in one row and would be able to be filtered by feedback date (found in the raw data), so that hypothetically one could select a month and be able to tell how many customers considered to be common respondents completed a survey within that month.
View 8 Replies
View Related
Jul 24, 2013
I am trying to connect individual responses to a survey to a series of variables describing each environment where the survey was conducted. I have about 1,400 individual survey responses (of about 34 questions each that have been separated into columns) and about 42 environments that are described with 30 different criteria each. They are matched by the environment description
- A line for each respondent and their survey answers
- On that same line would be the criteria of each environment.
Thus, the environment criteria should be repeated several times..However, when I join the fields in a query and require both fields to be equal, When I prioritize the environment panel, I only receive 42 records and the survey responses are blank. Prioritizing the survey panel gives 1,400 rows, but omits the environment info.
how do I populate the environment information variables on each row of the survey responses, even if that information duplicates the same variables on other rows?
View 8 Replies
View Related
Feb 6, 2013
I have a competition ranking contestants. A contestant can compete multiple times, each time with a unique registration number.
I am trying to sort by score to rank the contestants, however the contestants can only receive one rank - their highest score (not a sum of their scores, only one score). ? ?
Example:
Registration Name Score
2345 Sally 247
3456 George 230
4672 Sally 255
What I want to see:
4672 Sally 255
3456 George 230
View 5 Replies
View Related
Mar 7, 2008
I have one table, I want to update multiple fields in that Table with values.Currently I have on update query for each update.So for exampleRows: Commercial Quotes, Cover Notes, Personal QuotesWill have the values: Yes, No, Yes in them.I need to update those fields, if they say Yes to Commercial Quotes, Cover Notes or Personal Quotes depending on the field obviously. And blank them out if it says No.Currently I have 30+ different queries I have to click individually one after the other to update each row. I was wondering if theres a quicker way of doing this all in one hit.I have a tiny bit of SQL knowledge.So one of the queries I've figured out I've converted to SQL but again it only updates one instance...UPDATE Enabler_Data SET Enabler_Data.[Commercial Quotes] = "Commercial Quotes" WHERE (((Enabler_Data.[Commercial Quotes]) = "YES"));I tried whacking an OR replacing the ; and repeating that statment replacing Commercial Quotes with Cover Notes but it errored out on me.This is where I get completely stuck and my meagre knowledge fails me so any help would be useful.Thanks in advanceMatt
View 14 Replies
View Related
Apr 22, 2014
I have a query based on 2 tables, joined on Memberid, the result showing :
Table1 Table1 Table1 Table2 Table 2 Table2
Category Association Memberid CustomerName E-mailAddress MemberID
Board Member(Lookup,integer) SAMGA(Lookup,integer) 44 Smith smith@abc.co.za 44
Board Member ADHTY 44 Smith smith@abc.co.za 44
Grower SAMGA 44 Smith smith@abc.co.za 44
I only want to show 1 row, based on the duplication of E-mail address. I know i should be using the row_number function, but cannot get to the result I want.
View 4 Replies
View Related
Jan 30, 2014
I have two tables -say Table A and Table B.
Table A Table B
ID userno ticketno Status userno ticketno Status
1 1211 YAB 1211 YAB LIVE
2 1211 YAB 1232 ACE LIVE
3 1232 ACE 1232 AAT INVALID
4 1232 ACE
5 1232 ACE
6 1232 AAT
I am trying to write an update query in MS Access that will update only the first record of any custno with the status so that my final table A looks like this:
Table A
ID userno ticketno Status
1 1211 YAB LIVE
2 1211 YAB
3 1232 ACE LIVE
4 1232 ACE
5 1232 ACE
6 1232 AAT INVALID
How to write the query.
View 3 Replies
View Related
Nov 1, 2012
I'm running an update query that's based on a select query (that runs some calculations). The update query is updating ALL the rows that should be updated with the information in the select query with data from the final row in the select query, and not on a per ID basis as I think I have it set-up to do. The data looks correct in the update query, but again it's not coming out right.
Here's the SQL for what I've written so far:
UPDATE [Customer_Data Query], Customer_Data INNER JOIN Baseline ON Customer_Data.ID = Baseline.ID SET Baseline.[Unit Hours] = [Customer_Data]![Dur_Days]*[Customer_Data]![Dur_Hours]*[Customer_Data]![Number_Units], Baseline.Availability = [Customer_Data]![Perceived_Avail], Baseline.[Hours Available] = [Customer_Data]![Dur_Days]*[Customer_Data]![Dur_Hours]*[Customer_Data]![Number_Units]*[Customer_Data]![Perceived_Avail],
[Code] ....
I've also tried to force the update to the proper row by adding a criteria based on ID.
Select query, here it is:
SELECT Customer_Data.ID, Customer_Data.Data_Set_Version, Customer_Data.Number_Units, Customer_Data.Perceived_Avail, Customer_Data.MTTR_MTBF, Customer_Data.MT_TR_OR_BF_Hours, Customer_Data.Utilization, Customer_Data.Percent_Scheduled, Customer_Data.Sched_Percent_of_PM, Customer_Data.Sched_PM_Duration, Customer_Data.Sched_CBM_Duration, Customer_Data.Sched_CBM_From_PM, Customer_Data.React_Detect, Customer_Data.React_Rework, Customer_Data.React_False_Alarms,
[Code] ....
View 3 Replies
View Related
Dec 16, 2014
I have a form that has combo boxes and text fields (as well as sub forms). There is also a button linked to some code that says'
Private Sub cmdQuote_Click()
'Creates quote date and prints quote
Me.QuoteDate = Now()
Me.cbAgentID.Requery
DoCmd.OpenReport "Quote", acViewPreview, , "BookingID = " & Me.BookingID
End Sub
When the button is pressed the QuoteDate field (it is bound) should be be populated, but unfortunately it is not. I have played with refresh and requery but cannot derive a solution.
View 1 Replies
View Related
Nov 10, 2014
In a situation where I imported an excel file with so many columns and split them into two temp tables and they are linked using a key.
the data has a fixed part lets say
Field1....Field2.....Filed3.....Field4...then Field5.....Field6.....Field7....Field8 is the same data range as Field9...Field10...Field11...Field12. I would want to split this data into multiple rows like this
Field 1 Field2 Field3 Field4 Field5 Field6 Field7 Field8
Field 1 Field2 Field3 Field4 Field9 field10 field11 field12 and so own...
What is the best approach?
View 3 Replies
View Related
Mar 10, 2015
I am trying to assign teams to players. I have an import table with all of the players information listed. What I want to do is determine the count of players in a given city. For every 9 players I want to add a new record to the Team table and assign the team number (auto incremented for each team created). Then I want to add the players to the Players table with the Team Number that was created.
View 8 Replies
View Related
Jan 13, 2006
A SELECT works:
SELECT *
from mastertable
INNER JOIN secondarytable
ON mastertable.Identicalfield = secondarytable.Identicalfield
But none of these do:
DELETE
FROM mastertable
INNER JOIN secondarytable
ON mastertable.Identicalfield = secondarytable.Identicalfield
**Incorrect syntax near the keyword 'INNER'.**
DELETE
FROM mastertable JOIN secondarytable
ON (mastertable.Identicalfield = secondarytable.Identicalfield)
**Incorrect syntax near the keyword 'JOIN'.**
DELETE
FROM mastertable
WHERE (mastertable.Identicalfield = secondarytable.Identicalfield)
**The column prefix 'secondarytable' does not match with a table name or alias name used in the query.**
DELETE
FROM mastertable
WHERE IN (SELECT *
FROM secondarytable)
**Incorrect syntax near the keyword 'IN'.**
Can anyone help? This should be easy, shouldn't it?!
Thank you.
View 2 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
May 1, 2012
Here is what I am trying to do: I have form, and a table which is created from this form. The data is entered a second time using the form to make sure there are no discrepancies.
Any easiest way to implement this? (create a new table (which is duplicate), and then compare them?
View 5 Replies
View Related
Jun 1, 2007
The following is for a successful pet grooming business.
I have a Customer Table with a Customer ID as primary key. I have a Pet Name Table with a Pet ID as primary key. One customer can have many pets. The Pet Table is linked to the Customer Table via the Cust ID. I have a Visit Table with an Auto Number for each visit. The Visit Table is linked via the Pet ID number. Each pet can have many visits. That's the problem. We have exceeded the 64,000 rows in the Visit Table. All three linked tables support all the queries, forms and reports. How can I continue beyond 64,000 visits.
Thanks, Jed
View 10 Replies
View Related
Jun 14, 2007
I tried to search for the answer but not luck. I know there is a max size around 2gb for a DB but is there a row limit for a table or is it based upon size?
View 4 Replies
View Related
Mar 4, 2013
I have this database that do contains duplicate record (not duplicate field).What I'm trying to do here is to extract record that is unique to a new table And those record that were duplicate into a new table.the *Distinct that we used in sql will not work because it is not what I wanted. using distinc will give me unique record of the whole database.say i have this database that contain an ID:
2525
2658
2658
2658
2525
3678
so what i want is to extract unique record to a unique table and those that contain duplicate in another table.so the 3678 will be put in a new table and the 2525 and 2658 in a new table,,That way I will see which file will have duplicate
View 1 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 7, 2006
Hi.
How I can delete all rows from my table whit on query? Also I want that when I delete rows from my table index and other settings will stay in table.
View 1 Replies
View Related
Dec 17, 2004
Is there any way to get the last 4 rows which have been entered into the table?
Thank You.
View 2 Replies
View Related
Nov 14, 2005
Hi,
I have a table with four columns - A, B, C, D
I want to compare the values in columns A, B and C in row 1, with the values in row 2 and if they are the same, I want to copy the value that is in column D in row 1 into column D in row 2. I then want to move to the next row in the table and compare the values in row 2 with the values in row 3 in the same way. This will continue for all rows in the table.
Any help would be greatly appreciated as my VB is not up to much.
Thanks in advance
View 2 Replies
View Related
Apr 5, 2012
I am working on a project that requires to calculate interest on the amount. There are 2 tables, 1 with Interest rate for a product for different and periods and another is product table with amounts in different period.
Table 1 (Rates table)
Product From To Rate
1111 1/1/2012 1/20/2012 .75
1111 1/20/2012 1/28/2012 .50
1111 1/28/2012 4/6/2012 .40
Table 2 (Product table)
Product From To Amount
1111 1/1/2012 1/17/2012 10000
1111 1/17/2012 1/24/2012 15000
1111 1/24/2012 2/25/2012 20000
What i need to do is to calculate interset based on the above 2 table and insert into another table (Interest). However, the problem is with identifying correct rates for the periods in Table 2.
Eg.
for period 1/1 to 1/17: applicable rate would be .75
for period 1/17 to 1/24: From 1/17 to 1/20 the rate would be .75 and from 1/20 to 1/24 it would be .50
this is what i want to achieve, basically to split the period between 1/17 to 1/24 into 2 so that appropriate rate can be applied.
View 1 Replies
View Related
May 2, 2012
My work has a sql server db, and all the tables in our Access db is linked to them for reporting reasons for the secretaries. Why all the rows for one table are not showing up in Access?
I go into SQL Server Management Studio and I can see all the rows, then when I try to go into Access 2010, I cannot see all the rows. I have already updated the table in Access through the Linked Table Manager. However, nothing changes.
View 3 Replies
View Related
