I have one table which holds the answers to a medical questionaire. 300 questions to be exact. This table has a Primary Key which is just an autonumber.
The problem I have, is that the questionaire results will be updated from time to time. Not the whole questionaire but maybe a couple of fields. Is there a way that the user can go back to the questionaire make the changes needed and then save, but at the same time still keep the original results in the table, but assign a new autonumber? The reason is that they do not want to fill out the whole questionaire again just to make a small change, but I need to keep all the results going back in history!!!!!
Sorry if I havent explained this perfectly. I have looked at the appen query and the update query but not sure if this would be correct procedure.
Thanks in advance. You dont have to tell me exactly how to do it but a hint in the right direction would be nice.:)
I have one DB that is used for creating/storing customer ID's, and another DB that is used for creating/storing job information for customers.I have linked the table from the customer DB to the job DB.
There is a table in the job database that holds customer name and ID, and some VBA that generates unique job codes.
Is it possible to have the data from the linked table automatically update into the existing table?
i'm a Access novice. I have tables with existing data in numerical form, and would like to know how I can use Queries or VBA code to update these values into a new format in a new table. for example the original data might be of race type:
1 - caucasian 2 - african american 3 - hispanic 4 - other
how can i achieve this in Access? i know how i can do this conceptually with "if" and "case" statements, but I have no idea how i can do this in Access. I don't want to mess with the original data, so please help with CODE or QUERY examples.
Can I use the append query to update a table by replacing all existing information with updated information while at the same time adding all data that isn't currently in the table?
Right now every time I run the Append Query it just adds the same information as a new row instead of replacing the existing row with the updated information.
I am trying to use SQL to run queries in our access database in order to (hopefully) speed things up. I'm trying to create code that basically takes data from one table and inserts it into another whilst doing calculations on the data.
However I can't get past this:
Code: Private Sub Test_Click() Dim strSQL As String
I'm trying to setup a process of opening a form, saving to a location, emailing it as a PDF, killing the PDF, move onto the next form - rinse wash repeat until done.The below works fine on a single click, however if I try to set it as an event timer of loop, I get an error saying: This action cannot be carried out whilst processing a form or report event.
Do Dim outputFileName, Ref As String Ref = Vici_URN outputFileName = CurrentProject.Path & "Claim PackagesCICA" & Ref & " - " & Format(Date, "yyyyMMdd") & ".pdf"
As you can see in the year 2010 items 2 and 3 go down from qty 2 to 1. What I am trying to do is to keep track of everything that was ever shipped to the customer. So with that in mind the above table is showing that Qty-2 was ordered in 2009 and Qty-1 was ordered in 2010. I want to add these as I go along. So my desired table would look like the following
in this table 2010 shows Qty-3 which means 2 was present on site in year 2009 and 1 more was added in 2010 to make the qty 3. I want to write a storedProcedure or something similar to convert the first table into the second table. I said storedProcedure because I am used to doing this in SQL Server.
I have a situation where i need to import a table in Access which is in Excel, After importing i need to know how can i break it up differently keeping relation of FK and PK intact: E.g.
Excel Sheet:
Name Biz ID Biz Name Address1 Address 2 Address 3 OrderNo Order Date
Person Record ID(PK) Name Biz ID Biz Name Address 1 ....
-Existing Access Database contains tables with 1-2 million records
I would like to add a field[dol] to an existing table[rei]. I need this new field[rei].[dol] to be populated with existing data from another table[main] based on the associated field[main].[account1] or [main].[account2] or [main].[account3].
In the [main] table. There is always data in [dol]. But there is NOT always data in the [account] fields. Sometimes there is multiple account numbers per [dol] but not always.
Customers will ALWAYS have at least 1 [account] number and [dol]. Some will have multiple [account] numbers and [dol]. Sometimes these [account] numbers are the same in multiple fields[account1] [account2] [account3].I just need to do a lookup or something to find the [account#] and pull in its [dol] from the [main] table and populate it in [rei].[dol].
I want to export details from a table in access to a word document or any other document that will keep the current design and let me make changes afterwards.
I have a field called uniqueID which I would like to update based on 2 other fields that are already populated in my recordset (from running previous queries). I heard it is not possible to do an Update Select like shown below.
select max(uniqueID) from myTable where a = "value of field a from first record in record set" and b = "value of field b from first record in record set"
If it is true that I cannot do an Update Select then I am trying to do something in VBA. How can I Loop each row in the record set and store the values from fields a and b. I would obviously then need to pass those values into the sql above and store the result in a variable. I would then do a straight update to put the value of uniqueID into myTable.
I have an existing table that has calculations. When I added a new field with an default value of zero, it did not populate the existing records. Now my tables are not calculating. How can I update the existing records with the new default of zero.
Ive tried making command buttons for each of the three tables that when you click on it, it automatically updates the tables, but it doesnt seem to be updating them. Is this the right code for that to happen?
Private Sub Command0_Click() Dim strFile As String DoCmd.SetWarnings False ' Set file directory for files to be imported strPath = "C:SharesPublicStaff Public FilesBrandon PenlandUS Food Product Prices Newest" ' Tell it to import all Excel files from the file directory strFile = Dir(strPath & "*.xls*")
Hoping someone can help me with this DELETE query. I have a Main table that's being updated by a Temp table that's an exact copy of the Main table but with a subset of records.
1) Insert records from Temp table NOT found in the Main table - this query I have worked out below - not tested, but the results look correct.
Need Help Here... 2) Delete Records from the Main that are not found in Temp table with an exception...only DELETE records where certain key fields are matching. i.e. If S.CAD_NAME, lngStoreNumber are a match to what's in the Main table. While Temp table: lngStoreNumber - CAD_NAME - lngcomponentSerial 1 - "CHK" - a 1 - "STK" - a 2 - "CHK" - a
Main table lngStoreNumber - CAD_NAME - lngcomponentSerial 1 - "CHK" - a - LEAVE (EXISTS In Both Tables) 1 - "CHK" - b - DELETE (lngStoreNumber & CAD_NAME composite Found /lngcomponentSerial NOT Found in Temp) 1 - "STK" - a - LEAVE (EXISTS In Both Tables) 1 - "RMM" - a - LEAVE (lngStoreNumber & CAD_NAME NOT Found in Temp) 2 - "STK" - a - LEAVE (lngStoreNumber & CAD_NAME NOT Found in Temp) 2 - "CHK" - b - DELETE (lngStoreNumber & CAD_NAME composite Found/lngcomponentSerial NOT Found in Temp) 3 - "CHK" - a - LEAVE (lngStoreNumber = 3 Not in Temp table Subset)
Rule: Only delete the records for a particular CAD_NAME and lngStoreNumber from the Main table leaving all other CAD_NAME/lngStoreNumbers.
I'm running these updates in batches of lngStoreNumber. So the Temp table will only contain subsets of what's to be deleted from the Main table thus the need to link on the key fields only NOT to delete a Subset of lngStoreNumber/CAD_NAME. I think I've tried every possible query that doesn't work.
Here is query #1 to insert records missing from the Main table that exist in the Temp table. I think what I need is a variation of this??? SELECT D.* FROM Main AS S RIGHT JOIN Temp AS D ON (S.CAD_NAME=D.CAD_NAME) AND (S.lngcomponentSerial=D.lngcomponentSerial) AND (S.lngStoreNumber=D.lngStoreNumber) WHERE S.lngcomponentSerial is null AND S.CAD_NAME is null AND S.lngStoreNumber is null;
I am working from an existing database which is just two table. The main table has a massive amount of redundancy and duplication and needs splitting into, at first glance, 5 tables.
After I have run my various make table queries and added a Primary Key and FK field to the new tables how do I populate the FK with the Parent PK.
I thought I could simply add all the fields from the new table and then create an adhoc join in an update query to populate the PK to the FK. When I do this however I get "You are about to update 0 records"
I have tried the table analyzer but it doesn't give the correct options to split the table the way I need.
I have a table with employees’ information. I want to copy all records in this table to another existing table “WorkTimes”. I do not want to edit my employee table, so therefore I want to copy all records to another table before editing/adding information in other fields. How I can do that with a macro or module?
I have an inventory table and a repair table for computer equipment. The inventory table is already set and working with all the data I need.
In the repair table, I would like to just enter the ID number and have some of the information already stored in the inventory table automatically entered into the repair table as needed.
For example, computer 138 has repairs done to it. I would like to then go into the repair table to add a new entry, type in 138 and have the some of the data from inventory table (model, serial number, etc) transfer into the repair table.
Is this possible with Access?
I have only been able to do something with the lookup wizard, but that doesnt seem to work well.
I have a table which contains the following fields: WORKER,SUB SYS,JAN-08,FEB-08,MAR-08 I want to insert into this table each month the data from the second table for that month can someone tell me how to do this...each time I try the secnd table data TOTAL strarts a new row below the ones that are already there...hard to explain. I tried this and it didn't work:
INSERT INTO [ORACLE SORA ALERTS BY WORKER] ( [JAN-08] ) SELECT A.TOTAL FROM A INNER JOIN [ORACLE SORA ALERTS BY WORKER] ON (A.SYS = [ORACLE SORA ALERTS BY WORKER].[SUB SYS]) AND (A.WORKER = [ORACLE SORA ALERTS BY WORKER].WORKER);
WORKER SUB SYS JAN-08 FEB-08 MAR-08 0000A CRT 0000A ENF 0000A FIN 0000A RAP 000AT CRT 000AT ENF 000AT FIN 000AT RAP 000AX CRT 000AX ENF 000AX FIN 000AX RAP I have a second table which contains the following fields: WORKER,SYS,TOTAL
WORKER SYS TOTAL 0000A CRT 1 0000A FIN 21 000AX CRT 1 000AX ENF 2 000AX FIN 11
What I want is to move the numbers in the TOTAL COLUMN INTO THE MONTH/YEAR COLUMS IN THE FIRST TABLE. Would look something like this:
WORKER SUB SYS JAN-08 FEB-08 MAR-08 0000A CRT 1 0000A ENF 0000A FIN 21 0000A RAP 000AX CRT 1 000AX ENF 2 000AX FIN 11 000AX RAP
When I run this query I want the data inserted into an existing table in the correct place. It is inserting the data in the correct column but not the correct rows. Please see attached screenshot. Can you please show me what I'm doing wrong.
INSERT INTO UNDISTRIBUTED ( Jun2003 ) SELECT Sum(CCur(a.AMT_TO_DISTRIBUTE)) AS AMOUNT FROM NOLDBA_RECEIPT AS a, NOLDBA_INT_CASE_STATUS AS b WHERE (((Trim([a].[id_case])) Is Not Null) AND ((a.ID_CASE)=[b].[case_id]) AND ((a.CD_STATUS_RECEIPT)='H') AND ((a.DT_END_VALIDITY)=#12/31/9999#) AND ((Exists (select 1 from NOLDBA_RECEIPT k where a.dt_batch=k.dt_batch and a.no_batch=k.no_batch and a.cd_source_batch=k.cd_source_batch and a.seq_receipt=k.seq_receipt and k.ind_backout ='Y' and k.dt_end_validity =#12/31/9999#))=False)) OR (((Trim([a].[id_case])) Is Null) AND ((a.CD_STATUS_RECEIPT)='H') AND ((a.DT_END_VALIDITY)=#12/31/9999#) AND ((Exists (select 1 from NOLDBA_RECEIPT k where a.dt_batch=k.dt_batch and a.no_batch=k.no_batch and a.cd_source_batch=k.cd_source_batch and a.seq_receipt=k.seq_receipt and k.ind_backout ='Y' and k.dt_end_validity =#12/31/9999#))=False) AND ((b.CASE_ID)=(select min (y.case_id) from NOLDBA_INT_CASE_STATUS Y, NOLDBA_INT_CASE_MEMBER Z WHERE A.ID_PAYOR=Z.MEMBER_ID AND Z.RELATION_CODE IN ('A','P') AND Z.CASE_ID=Y.CASE_ID))) GROUP BY b.IV_D_DO_CODE ORDER BY b.IV_D_DO_CODE;
This has to be an easy one but I can't seem to figure it out. I created a table by importing the structure from a .dbf file. Now I simply want to import other .dbf records from a bunch of .dbf files that have the exact same structure. How do Import these records into my existing table?
This should be a no brainer but I have spent an hour or so searching the help file and the internet.:mad:
Please bear with me, first post, trying to get to grips with Access for work!
We (a primary school) had a "bespoke" database set up using MS Access by someone who has long left the area and now we need to make changes and I, as the most computer literate person on site, have been volunteered to make these changes.
My level is beginner (for now) and my first job is to add some new options to a "Titles" table, four columns, six records. Column1 = ID#, Column2 = Male Titles, Column3 = Female Titles, whilst Column4 seems to hold items from Columns 2 & 3.
If I add a futher option, I get the error "Can't perform cascading operation: Entry must be one of the Titles as featured in the drop-down box or in the Titles table.
Now, as far as I can tell, I HAVE added the new option to the Titles table, and they DO show in the drop down box in the main spreadsheet display, BUT the error comes back as soon as I try to confirm the new title.
I am trying to add an existing field from one table into another table but the table Im currently in shows that option to be greyed out. What would cause that option to be greyed out?
I feel like this is one of the standard uses for Access but for the life of me can't get it to work with an existing Table.
Essentially a table with a ton of records is imported via VBA. One of the fields is ObscureCode, for argument sake there are three codes: 001, 002, 003.
Is there any way to say when the ObscureCode cell value = 001, display Code Red. When it's 002, display Code Yellow.
I have tried setting up a relationship but that just clears all the fields in ObscureCode column. Is there a way to do this through VBA? I ask because I imagine when someone imports new data (the table is deleted and re-created with new data) the relationship will break. SO I will need to put in the VBA a way to link them back up.
I have a table that contains a notes field. What i would like to do is to append to this field in table with some more data (notes), however i dont want to over write the existing data. I would just like to add to it.
the current notes are added through the access front end, but in this instance i have a particular note that needs to be added to various enteries without overwriting existing data. I know this is to be done via an append query, any help would be great
Please help.... I have combined 2 Tables using a select * query. I have to do it this way as the data changes. The issue I have is that in both tables there is a common field called "Amps" Hence my resultant table has 2 columns, Table1.Amps and Table2.Amps. Every time this occurs I want to delete Table2 column entirely as I use this table to plot a graph.
In short- I need to know the code to delete a column from an existing Table.
