A database at our Food Pantry needs to be updated yearly on Jan. 1 by eliminating all data in two fields: "ID Check Date" (date field) and "Signature Obtained" (check box). There are hundreds of records. Is there a way to remove the data from the two fields (columns) while retaining the fields (and their formats) but with no data? We use Access 2010.
View RepliesI need to delete all the field names from my table so I can import a new excel file with different field name headings,
currently I run:
DoCmd.RunSQL "DELETE * from table1"
this deletes the data in the table, but not field names.
What Vba command will allow me to do this.
i have 4 supplier tables with identical field names but different databases in sql.I want to have them all in one table and only want the information for reference i do not want to edit any of the data.I need to create a new primary key number for the complete table. The data will need to be refreshed as the data comes from MMS Sage looking at company PLsuppliers.
I have tried a linked SQL union view- but this has no primary key.I need to keep the Primary key consistent as the data saved in other tables will use this field.The overall goal is creating a Purchase order system and this list will be my complete supplier list.
Currently trying to build a database for customer management and order placement/tracking. Want to set a couple of rules so that if I for instance click yes of billing and shipping address the same that the database will automatically fill the shipping address with the data I inputted for the billing address in the same table.
The other issue I can see I'll run into is, I want to be able to select one of the company ID's (made up of a three letter abbreviation of the full company name) in the product ordering table and it will automatically fill in the rest of the customer data (phone, email, address etc) data into that form.
I have a field in a table that is comprised of mostly numerical data but some records are text.
I want to convert this field to numerical only and make a new field to put the textual data in.
However converting the field will delete the textual data. What is the easiest way to convert the field but save the textual data AND append the textual data to the SAME record that they were in originally in the new field?
From a table I want a text field which has a path to a file to be copied automatically into a another field of the same table with a hyperlink text type...
View 5 Replies View RelatedI inherited a database that has two tables that are structured identical; one called tblcurrentdata and the other called tblpriordata. The user wants a report or export query that only shows records that has differences between prior weeks data and the current weeks data. The tables have 12 fields of data in a record per quote number. The user wants a query or report to identify the QuoteNum and any changes between the prior and current records. The table structures are as follows:
Code:
tblCurrentData/tblPriorData:
QuoteNum
CoName
State
ZipcodeNo
priorityColor
[code]...
QuoteNum 12345 field 5 (prioritycolor) changed from red in the prior week to green in the current week and field 7(POC) changed from Scott in the prior week to Jonson in the current week.QuoteNum 23451 did not have any changes therefore does not need to be listed in the query/report Unmatched query doesnt work because it does not compare multiple fields. I tried to structure a union query and use <> in each field but got too tedious and didnt give the expected results.
I'm a newb to Access and SQL and database management. I have a main table and a dependent table with a 1 to 1 relationship, relationship integrity and cascade update and delete. I used an append query after I had modified several records, and after the append deleted one of the records from the main table. It remained in the dependent table. Access didn't catch the disparity even after a restart. I broke the link and tried to reestablish it, and of course Access wouldn't let me. I deleted the record manually from the dependent table and all was well.
Why would referential integrity/cascade delete not be recognized after an append query?Access 2010.
This is the second time in two days that cascade delete has been broken in two days after using a query on the main table. Yesterday, after referential integrity was broken, deleting the records from the dependent table allowed me to restore Cascade Delete functionality and it worked for the rest of the day.
I am using access 2007 and cannot seem to find in the field properties.I need to input data in one field in a table and it appears in a field within a different table.
View 2 Replies View RelatedHow can i see the most frequently used data in a field?
For example if i have years put in a field, how can i see what is the most frequently used year and also how many times it's used? It will be a plus if i can see all the years in order.
Also if i have one field and in that field i have more than one name, can i find again which is the most frequently used name?
I have a table named days contains two columns (Day_Number, Day_Number_text)
and another table named information contains many column two of them is (Day_Number, Day_Number_Text)
so what i want to do when day_number = 1 for example so day_number_text = Frist automatically
I have a form which contains many text boxes. What I'm doing now is that any time I create a new record I save this record to the table the form was created from and I also save to a specific field of an irrelevant table(other table) the index of one of the text boxes of this form.
e.g
My form contains the text boxes: "Index", "Tabletype", "Component".
I save all of the above to the table "tblTransistor".
Then I take the "Component" only and I save it in the field "Test" of another table which I call "tblManComps".
My form is called: "Transistor".
Now I want to be able to delete at the same time the record from each table.
Is it possible to have some VBA which will do it?
I am using access 2010. I technically have an unbound form but I am changing the record source by command buttons. Switching between 3 command buttons. My problem is that I have a subform that I was linking to the form to an unbound search field. Now I want to switch the master and child links to a field on the form when I switch the record source which happen to be the same field as the unbound search field; however; it still pulls from the unbound search field rather than the field on the form. Do I need to delete the search field in order to get is to point to the appropriate field?
View 1 Replies View RelatedI have a database containing 3 tables: Book, Member, and Loan. A relationship exists between the 3 tables(the relationship has no problems, I have enabled referential integrity). I created a query named Loan Query which uses all the 3 tables.
In the loan Query, the Member ID field is in Loan Table and the Member Name field is in the Member Table. This allows the database to automatically type the Name when the Member ID is entered into the query retrieving the information from the Member Table. This also works fine. But the problem is that the Name field of the Loan TABLE (not query) remains blank. So i have to keep the required property of that field to no.
All I want to do is for the database to also write the Name in the Loan Table along with the Loan Query. I will also have to apply the same for other fields also.
I am a beginner in Microsoft Access and so whosoever replies, please elaborate the solution.
Is it possible to create a field in a table that stores only two possible data choices: a check or null value ?
View 6 Replies View RelatedI 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.
Is it possible to delete a table from a remote access db.
i.e
table "A" is in database "A"
from a form in database "B" i would like to run a routine that would
Delete table "A" from database "A"
i have tried using "DoCmd.DeleteObject acTable" but i think this only works for the currentdb
Can anybody help
Regards
Bjackson
I am using A2K
I have two different tables and I need to run a delete query to delete all information pertaining to an employee when he/she is expired threw a button on a continious form which uses one of the tables that needs deleted. How would I do this and please explain in newbie terms.....Thanks!
View 4 Replies View RelatedThis seems like it would be a common problem but I tried to search for solutions to this and I haven't been able to find a specific answer. The forum excludes too many of the words I'm trying to use in my search....
I have two joined tables and I want a delete query to delete the specified records in the primary table and all of its related records in the secondary table.
DELETE tb_AccountHeader.*, tb_AccountDetail.* FROM tb_AccountHeader INNER JOIN tb_AccountDetail ON tb_AccountHeader.Index = tb_AccountDetail.Index WHERE tb_AccountHeader.PeriodStart=#1/1/2008#
This gives me the error "Could not delete from specified tables". I tried SELECT DISTINCTROW but that doesn't make a difference. I do not believe that it is an issue with permissions because I can delete records from either of these tables if I remove the join.
Any help would be appreciated.....thanks.
I'm able to import new data from excel just fine, but I can't import updated data from excel due to duplicates not being allowed for a particular field. Is there a way to keep from importing duplicate records based on one field, but still import data from other fields where the information is different from the excel file?
View 5 Replies View RelatedWhen I tried paste some data using front end to my database, Access showed error (can't create record because data would be duplicated). I thought it's impossible because it is autonumber field. So I checked it (manually). I did copy of my database and then for testing, I created record. I was shocked. Next record should has a value of "160" but Access gave "130" then showed an error "Can't create record because data will be duplicated". Of course after compact and repair everything is fine.
View 5 Replies View RelatedI'm a bit new to Access but have managed to build a very simple database.
I have a main table which I need to add 4 columns onto the end of, the data for which comes from 4 tables linked to it. Below is how they are linked.
Main table------> Table 1
Main table------> Table 2
Main table------> Table 3
Main table------> Table 4
All 4 relationships have a join type of 2 (Include ALL records from 'Main table' and only those records from 'Table1/2/3/4' where the joined fields are equal)
However, 3 of them pull back the correct data when I refresh the Main table, but the 4th one doesn't - which appears to be to be set up in the exact same way. It does bring back data, but it's the wrong data or in the wrong order.
For example, if the related field in the Main table is "Sarah" - It needs to bring back "Programme", but some Sarah's pull through Programme, some are blank and some are another option altogether. Table 4 has no duplicates or typos etc and I've tried deleting it, loading it in again and creating a new relationship but nothing seems to work.
how to do a particular thing in Access 2010 (I don't even know if it is possible).
I have a table named PRODUCTS:
ID_PRODUCT (primary key, autonumber long integer)
ALLOWED_OPTIONS (multi value text lookup field: "Option 1";"Option 2";...;"Option 9")
So I can store, for each different product, none, one, or more options to let the customers choose from.
I have a table named ORDERS:
ID_ORDER (primary key, autonumber long integer)
FK_CUSTOMER (foreign key, linked to the primary key of a CUSTOMERS table; represents the customer that places the order.)
FK_PRODUCT (foreign key, linked to PRODUCTS.ID_PRODUCT; represents the product that the customer has choosen)
CHOOSEN_OPTION (lookup text field; the customer must choose ONE option among those allowed for the product he has ordered)
The problem is that I would like the CHOOSEN_OPTION field to show as a combobox, listing the values stored into PRODUCTS.ALLOWED_OPTIONS, so that when a customer buys a product, he can choose only among the options allowed by that particular product.How can I manage a multi value field to populate a combobox, in which every item stays on its line? If I use, as a query to populate the combobox:
select [PRODUCTS].[ALLOWED_OPTIONS]
from PRODUCTS
where [PRODUCTS].[ID_PRODUCT]=[FK_PRODUCT]
I obtain an empty combobox.If I refer to the last field as [ORDERS].[FK_PRODUCT], Access asks me to type a value for "[ORDERS].[FK_PRODUCT]", treating it as an unknown parameter.I think that the problem is that when the combobox expands, the record is not committed yet, so FK_PRODUCT is unknown (NULL?). But this happens even if I commit the record typing something in FK_PRODUCT and then I re-enter the record and I expand the CHOOSEN_OPTION combobox, that is still empy although FK_PRODUCT exists, now.Is there a particular syntax to refer to a field in a record not committed yet (something like "THIS." or "ME.")?
Hi,
I'm trying to delete a table once I've used it and always get the same error saying that the table is already in use, these are the commands I've tried:
DB.TableDefs.Delete ("Report")
AND
strSQL = "DROP TABLE Report"
DB.Execute (strSQL)
Can't figure out how to avoid this. Please help
Mike
Hi,
What's the SQL script for deleting all records in all tables, so without deleting the tables completely,
As I have over 50 tables I do not want to use:
"Delete from TableName"
Is there some wildcard I can use?
Thanks
I have a table with about 300,000 records. About ten fairly small fields per record. I am trying to change the length of a text field from 25 to 40 characters, and I get the error message, 'MS Access can't change the data type. There isn't enough disk space or memory'.
I have never seen this message before. I have about 64 Gig of free disk space. What can I do?