I have a table (which has a few relationships) that contains 10.000 records up-to-date, but I've been making major changes to my database working on an old version from early June, when it had +9000 records.
What I'm planning to do to update the table:
export those 10.000 (up to date) records to a Excel file
delete those outdated +9000 records manually (select all + supr)
import the data from the Excel file so my new database is up-to-date again .
Is this acceptable or could lead to weird errors in the future?
My table (excel spreadsheet) sits on another directory from the DB. If I delete the table on this directory and immediately replace it with a table with the same filename, format, etc (only the data has changed) will each database user have to relink the table on their desktop DB? Or will the forms/subforms/reports still maintain their relationships/functions and just display the new data?
I'm building a database to calculate yearly fees for customers. I have a list coming from our accountancy-database that shows a field with the clients name & surname combined and I have a table named 'customers' that contains the clients names & surnames as separate values.
The first table (let's name it "accountancy") thus contains a field "Customer". Example data in this field:
The second table ("customers") contains the fields "ID, name, surname". Example data in this table:
1;"John";"Doe" 2;"Marcy";"Free" 3;"John";"McLane"
Now, what I'm looking for is a way to replace the 'Customer'-field in the first table by a lookup field that contains the correct ID for that customer in the 'Customers'-table.
Lucky thing: there are no doubles in the customer's table, so no two customers have the same name AND surname.
I have a main table which contains data on Oilfields. This is set up with an auto number for ID and a name of each oil field. I have other tables which contain things such as Pipelines, Wells and Platforms, which are linked back the the main table.
The problem is that the database was given to me with the links being between the Oilfield_Name, and not using the Oilfield_ID (which is not present in the 'satellite' tables). How can i add the oilfield_ID column to the 'satellite' tables so i can link them properly?! (i can obvioulsy physically add an 'Oilfield_ID' column to the 'satellite field', but how do i populate it with all the correct IDs from the main table, to suit what is in the 'Name' column of the 'satellite' field- does that make sense?). I suppose I need to replace all of the Names with the IDs?
This has been giving me such a headache. The Oilfields table has 2500ish records, with as many as 1200 in the other tables linked to it.
how many elements matching to my primary elements from any records of my query and count match, if some element doesn't match then I need to add it to my primary elements, then at the end (rst.eof) count how many primary elements I have.
E.G
id colours 1 blue;red 2 purple;blue;green 3 red;violet;purple;blue
dim matching_elements as long dim primary_elements as string dim TheNumberOfPrimaryElements as long
First of all, if I open recordset primary_elements is empty so I need to assign a value form first record.
Using Access 2010, I have a table the contains a field, MembereMail, that is defined as a hyperlink. I imported data from an Excel spreadsheet which looked to be consistent in format and did not realise that anything was wrong until I wrote some VB code to generate personalised emails with personalised attachments. I then found that some of the hyperlinks worked and some didn't. Those that were essentially text worked, those that were genuine hyperlinks tried and failed to go to addresses prefixed "Mailto:".
I have finally managed to convert all email hyperlinks to text content (although still defined as hyperlinks) and my code works. However when I add a new record to the table through a form, the field still being defined as Hyperlink, it goes in as pure text. I therefore have the need to use the address in both text form and as hyperlink so that the user can click on the field and start an Outlook email as well as my code running correctly for mass mailings.
So a number of questions:
-Why does Access not store the email address as a clickable Hyperlink? -How can I satisfy my two requirements from one field? -Can Access 2010 check the input text to ensure that it is a correctly formatted email address?
OK, here is my situation. I have an access database which is updated automatically every night with new data. My companies solution to this is each morning, the entire database is downloaded to my computer to be used by another application.
In one of the tables, there is a field that doesn't work for my purposes, so each day I have to manually open the table, and change the contents of that field for every record. For example, lets say I have the following table:
I have info in Excel workbooks coming from various individuals. I take bits and pieces of data from each and the update an Access DB. Is there a way to simply replace the table in Access without affecting the relationships that the current version of the table has?
I am trying to take the content of two fields from one table and dump them into the other table, I created an Append Query pulling up just the main table and appending to the destination table but it doesn't copy the info when I run it. I also pulled in both the tables (and yes they are joined) doing the same as above and still it doesn't do anything when I run it.
I have a simple database that a user records the work they have done for the day. They are required to fill out the form with the item number, date, qty etc... the problem is some people are fat fingering things and i am not getting the right item numbers... I have a table called dbo_item with all the possible item numbers in it, is it possible that after they hit enter or click off the item number box it will tell them they put in an invalid item if it doesn't match one of the items from that table?
I have a database in access that i have shared it and 5 person write in it. I want to show the datasheet in a monitor but my problem is hat I cant use auto refresh the Table datasheet. How can I auto refresh the datasheet of table when every client change it?
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.
As I began thinking about the data that I need to include in one of my reports I relized that I need to gather some extra data.Each design change has a lifecycle with 7 basic states from not started through to closed. States 4, 5, and 6 have two posible sub-states that I need to capture and report. It is almost like having options.
My data entry form already records the 7 basic states. What I would like to do is have another field that records the sub-states if the design change is in one of those three states.
Will a ComboBox do this?
Do I need a test routine for the After Update event in the first text box? Something that will check for states 1 - 3 and 7 move on to the next field and if states 4 -6 require users to enter the sub-state.
I have a module which generates an autonumber based on a table content (Tbl:Numbervalues) and a prefix (prefix held in Tbl: TextValues)
the module itself works fine and after every getprefix & getnextnumber it updates the number within numbervalues table to the next unique figure.
However what i want it to do is within my database i want to loop through all the records and update a column based on the getprefix() & getnextnumber()
so at the moment lets say column A has values of SHA000001 (in all 100 records) i want it to loop through those 100 records and update to be SHA(from the prefix table) and 000001 all the way to 000100.
Code: Public Function GetPrefix() As String On Error GoTo GetPrefix_ErrorHandler GetPrefix = DLookup("Value", "TextValues", "Description = ""InvoicePrefix""") If Len(GetPrefix) <> 2 Then msgbox "The Invoice Prefix Value in the Text Values Table is not 2 characters long!", vbCritical, "Critical Warning" End If
I have an access form (Customer) along with a sub-form (Work_done). On sub-form I have Running sum of Amount in Text box-1. I want that Total of Running sum be replaced with Main form's tables field total_amount. Is it possible that we can replace an amount of control of sub-form into main form's field?
in my form I created an unbound field with a requested combination, now how can I put those in a table field. in other words, how can I send the content of a form's unbound field to a field in the table?
I have a customer who made a query with a field that looks like that:
NewField: iif(Field1=True and Field2>100 and isnull(Field3);"Blue";iif(Field1=True and Field2>100 and not isnull(Field3);"Black";iif(Field1=True and Field2=100 and isnull(Field3);"White";... And so on, for miles and miles.
Although it works, and fast enough, I don't like the way it is displayed and I am sure that it could be even faster without all these iif.
Any idea about the best way for me to handle this kind of query? Thanks!
hi. i are replacing queries in my tables to get the database ready to convert to SQLServer. Am having a problem replacing a query that is brings in a table for a drop down and inserting the selection in the record. during creation, the record shows a date, id, task, hours. the datatable needs date, id, task_code, task_name, task_job, hours. On "task" the drop-down allows for selection of several choices that are 3 columns wide. i can get the columns to show but only the first column currently goes in my new table. the table's columns that the selection comes from is "task_code,task_name, task_job". All columns are needed as different areas use the same task code but use different jobs or names.
I was told to use a insert into statement, however am having problems with it.
have as follows: SQLStatement = "SELECT [Task_code], [Job_name], [Task_name] FROM tablea; " rec.Open SQLStatement, , adOpenDynamic
Hi, Im building a DB that basically tracks the hours people have worked, its all going fine apart from one major bug which i cant get my head around, I am trying to work out a way so that if someone decides to change the hours they have worked it replaces the existing record and does not create a new record.
Each record has a unique user ID attached and each day is dated, do you think this is a VBA solution or a change in the relationships perhaps?
Sorry fi there is already a similar answer to this question on the forum, but i couldnt find one!:confused:
Hi, I have 2 tables with similar data. However I need to compare this particular field called CompanyName in both tables. Reason being human error/exposition data errors. For example, Chef Kitchen Holdings Limited, in one table it is Chef Kitchen Holdings Ltd while the other table is Chef Kitchen Holdings Pte Limited, they both are the same but Access recognise them as 2 distinct datas.
Ｔｈｅｒｅｆｏｒｅ Ｉ need help in comparing datas between these 2 tables for this particular field. So long as there is 75% similarities, one of the 2 tables will have the data replaced.
Is that possible using Access? if not, how about excel? please suggest.
I am having a query, where a few date records are null because of no entry or record, what a want that the field with no date entry to be come up with zero "0" .
I created a database that has a field called "Corrective Actions." I want the user to be able to type in a series of numbers, in the following format: 001, 002, 051, 123. Each number has a certain meaning. For example, 001 means "Upgrade to current revision," 002 means "Replace Unit," and so on. Here is what I would like to happen: The user will type in the numbers in the appropriate format. When the user presses the tab key to go to the next field, I want the numbers to be replaced with the actual meaning. My problem is that I can not figure out how to accomplish this. Can anyone help me?
