Hi
I need to know if it is possible to create a delete query which will target particular fields in a row based on certain criteria in a seperate combo box(on same table).
The few ways that I have tried to do this have been unsuccesful so if anyone can shed some light on the subject it would be greatly appreciated.
I am using Access 2007. I have an ODBC source feeding data into a table, to which I made a crosstab query that displays the useful data from that source. I need a way to add data(new field), using a form, that is a associated with the lines in the query. I've tried creating another table that uses the sample ID as the primary key, but I need it to automatically create a new row for each query line. Is there a way to do this, or better way to add data to a query. Examples are below.
The ODBC data looks something like this
SampleID PeakName Conc
sample1 THF 50
sample1 MEOH 10
sample1 propanol 25
sample1 butanol 15
sample2 THF 21
sample2 MEOH 15
sample2 propanol 17
sample2 butanol 23
I have the query look like:
SampleID THF MEOH propanol butanol (I need to be able to add a field here)
sample1 50 10 25 15
sample2 21 15 17 23
Linked table I created:
SampleID LIMS #
sample1 1234567
sample2 4567891
The problem is, for this table I have to manually select each sample ID. I want it to automatically add the sample IDs as new ones are querried.
Hello,
I have a big table like this:
ID Name Race1 Race2 Race3
-- ---- ----- ----- -----
1 a Asian russian
How do I create a query to be like this (either in query or report)?
I like to combine three fields (race1, race2, race3) in one field (Race), and break any value of races to the 2nd line, 3rd line, if there is a value...
ID Name Race
-- --- ----
1 a Asian
Russian
In Oracle and SQL Server, we can combine a few rows to be columns-like, but in Access, is there any way to do that? and how?
Thanks so much!
Jenny.
I need to append some rows into a table, before I add these rows i need to delete all of the old rows.
I can't use a Make Table query because I am updating a linked table in another database.
So how can I (automatically) delete all rows in that table before or during the append process.
Thanks
Steve
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.
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?
Is there a way to select several rows of records in a datasheet view of a table and delete all of the selected records?
Every time I try to delete more than one record at a time, I get the following message: "The Micorsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time."
Then it will let me delete one record out of the selection.
I am the only one in that particular table at the time I am trying to delete the record.
Thanks in advance for your help...
Hi
I have to make a query which deletes duplicate rows from my table. How I should start to make it? Can I do it whit one query or does this operation needs more queries? I know that DISTINCT works but how I can set it to DELETE operation?
I have try to use command DELETE DUPLICATES FROM Table; but this command deletes all rows from table why?
Trying to make a shortcut to join the workgroup and open a Db.
I made a shortcut for a Db, the shortcut defalt target was:
X:RC-OfficeRC_OFFICdatabaseProjects.mdb
I edited the target line to:
/wrkgrp ”X:RC-OfficeRC_OFFICdatabaseMS Access Workgroup.mdw” “X:RC-OfficeRC_OFFICdatabaseProjects.mdb”
I get wrkgrp not vaid target. I put the mdb file path first, I get not vaid target for the path to the Db.
I would rather not put in the whole MS Access program file. This is on a server that I don't know much about. Not sure I would get the program file right.
Help?
sql = "SELECT Table1.* INTO Table1 IN '" & sExpFileName & "' FROM Table1"
CustDB.Execute sql
where the sExpFileName contains the path of The Target MS Access DB
The Query works fine. But recently I discovered that if my target File name contains apostrophe It gives error as Query Input must contain at least one table or query
Is there any way to solve this problem. Please suggest me some way
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.
I have to make a query using the following case, I have two tables, in the first table i have the materials and its received date and in my second table i have the month the target date for every month to receive the material. Now linking this two tables, i want to see the from the first table on every month hom many materails i received after the target date how to do this.
Table 1
Field1 Date
Field2 Month
Field3 Material
Field4 received_date
Table 2
Field1 Month
Field2 Target_date
I want to ensure that employees submitted expenses that were actually valid. In table 'Payments', I have the employee ID and amount paid to that employee's account. In table 'Expenses', I have the employee ID and then the individual expenses the employee submitted. How can I create some VBA to find combinations of the employee's records in 'Expenses' that equal the total amount paid for that same Emp ID in table 'Payments'? My ideal end-result would be a report showing each employee IDs with the expenses and then the amount paid beside it. The below is a good start but I need to make it loop through every unique Employee ID.
remove spaces from URL
[URL]
I have a query based on two tables, joined on one field (this field is the primary key in Table A. Table B doesn't have a primary key). When working inside this query, it gives me the option to delete records. However, when I try, the row disappears from the query but then if I save and close and either re-open table A or the resulting query, that record is back. It doesn't actually delete. If I go to table A directly and delete it, it's gone for good.
How can I make it so that when I delete the record inside the query it actually deletes? And if that's not an option, can I make it so that it doesn't seem like I can delete records inside the query so that people don't make the mistake?
I have two forms. In form 1, there is a combo box that I have set on click to open form 2 in this manner:
Code:
Private Sub boxEditEntry_Click()
DoCmd.OpenForm "frmSRTEdit", , , "[ControlNumber]=" & Me.boxEditEntry
End Sub
The thing is, this has only worked when Form 2 is already open. I can't figure out why. If Form 2 is not open or has not been opened, the on click will still open Form 2, but not to the value in the combo box, or any value for that matter, it's blank. I really need it open if has not yet been opened or if it has been closed before. Also, I'm fairly new to access and I have no clue what I'm doing in vb.
Does anyone know if you can replicate the field values in rows automatically?
e.g.
NetworkHandsetTariff
3K800iVTT700
to become:
NetworkHandsetTariff
3K800iVTT700
3K800iVTT700
Any ideas?
Rows to columns;
I have a table with three fields
ID,Desc,Code
1,aaa,code1
2,bbb,code2
3. aaa,code3
4, bbb,code4
I need a query creating these two columns:
Desc,Code
aaa,code1;code3
bbb,code2;code4
how can I do this?
Hello All,
I am trying to figure out the best was to combine fields from multiple rows into one row & field.
Example: I have a table that contains footnotes and products. With a simple query I would get the following 3 rows:
Product........................................... .......Footnote
V.I. Capital Appreciation Fund.................3
V.I. Capital Appreciation Fund.................5
V.I. Capital Appreciation Fund.................1
What I want is one row and the 3 footnotes combines into one field:
Product........................................... .......Footnote
V.I. Capital Appreciation Fund.................3, 5 ,1
Any help would be greatly appreciated.
i'm using Access 2010 and i created a main form , from which i want to let users to open new table with fixed fileds and rows, also the name of the table will be as per user choice. so if a user want to open new table from the main menu, he will have the choice to press on a command button and a dialoge box will ask to name the table. the new table will then be open with the same fileds and rows names( name only) . the new table will be edited using a form.
View 3 Replies View RelatedI have two simple tables. I want to delete the records from Table1 that are on Table2. I've created a select query that gives me what I want but when I change to a delete query, I get this message: "Unable to delete from specified tables"
I think my problem has something to do with security but I can't figure out what to change.
I have a big Excel file with payroll information about employees. Per employee per date, there are a couple of rows with mostly identical data (such as the employee's home address) but 2 differences: one will have paycode A with amount B, another will have paycode C with amount D, etc. I want to simplify the file to have 1 row per employee per date: in the example given that row would have paycode A with amount B as well as paycode C with amount D, in 4 separate columns.
I think I can technically solve this by creating Excel files per pay code/amount, and then linking them together by person and date, 1 by 1, through Access. This is feasible because there aren't that many different pay codes. But still, I was wondering if there was anyway to solve my problem in Access itself.
I'm trying to clean up a database and make sure that I don't make an error in future entrys.
Is it possible to make a "key" that highlights the rows with matching keys. The thing is that a the "key" is Switchname + Switchport, data placed in 2 different columns in the same row.
How do I do this? Is it possible?
Hi
Is there a automatic way i can delete Fields from a Table? This table gets input into the database very frequently as updates come in and i'd like something like a Query or Macro, where i can define which fields i dont want, to delete the 30+ columns of useless data.
Can't see how to do it with a Query or macro though
Anyone have any ideas?
Thanks
Hi
Does anyone know how to delete the extra blank fields you get at the end of a query. I am converting the query to a handheld database with a field limit and the extra fields push my field count over the limit. i have tried just 'deleting' them but they reapear when I reopen the query. they definitely count as fields because if I delete some real fields i can do the conversion. i dont want to alter the structure of the original database as it has been in use some time and works well. any ideas advice
Thanks
supateach
Hi again
I am still having problems with too many fields in my query so it will not convert to a handheld database. I have tried making a test query (with the wizard)from the same table and sure enough there are extra fields at the end, which do not come from the table, which reappear after they have been deleted, when you reopen the query. I have attatched the query to illustrate that I have deleted the fields and then they reappear. It does appear to be impossible to get rid of them, in which case I shall have to decrease the number of 'real' fields to get below the max the handheld can convert. Any advice gratefully received
Thanks
supateach
I have two tables. One table is a list of classes with the number of enrolled students:
Class............StEnrolled
English 1A........6
English 1B........12
English 1C........20
English 1D........25
Reading 1.........4
Reading 2.........15
And the other table is a list of "combined" classes (meaning they are, essentially, the same class and should be counted as such):
Class...........CombinedWith
English 1A.......English 1B
Reading 1........Reading 2
Using the first table, I currently generate a query (and build a report) that displays class enrollment levels. What I need is for this query to identify combined sections from the second table and display them as such:
Class........................StEnrolled
English 1A/English 1B........18
English 1C.......................20
English 1D.......................25
Reading 1/Reading 2..........19
OK, so I have a database with four tables (Well, more than that, but these are the relevant ones). It's to be used for recording the results of site inspections.
"Tbl_Typicals" is a list of products. We'll call its key "Typical_ID".
"Tbl_Actions" is a list of tests performed on each product. A given product may have many tests, but each test applies to only one product. Its key is "Action_ID". Each row contains a Typical_ID to link on.
"Tbl_PlantComponents" records which products are installed on which site. Its key is "Component_ID". Each row contains a Typical_ID to link on.
The fourth table ("Tbl_Results") contains the results of each test. As a result of the relationships above, each row is specific to a single Action which applies to a particular PlantComponent, which is to say, each row has both a Component_ID and an Action_ID to link on.
So, what I need is a query that pulls all of these together, such that I can use these details as the line items of a subform.
The main form displays the details of the PlantComponent, which is a simple query to relate line items in Tbl_PlantComponents with the data about that particular product in Tbl_Typicals. So far, so easy.
The subform shows the details of each test applicable to that product. It then has toggle buttons and a comment field to indicate the results of the test, the results of which should be stored as a line item in Tbl_Results.
The "easy" way is to use an append query to generate Tbl_Results in advance. This works, but it raises a variety of new issues.
The nice way would be to use a normal SELECT query and have Access fill in the necessary linking fields (the Action_ID and Component_ID) on each row automatically. Now for trivial examples, this is very easy - my main form query manages just that: I created a link between Tbl_PlantComponents and Tbl_Comments (which stores general comments about each PlantComponent which aren't related to a specific test) based on the Component_ID and that works fine - when I edit the Comments field, the row is automatically created and the linked ID field filled in for me.
However, when I need to do it with 2 links, it all falls apart. I've tried everything I can think of, including generating a single-column unique ID to use for the link, but Access just won't autofill for me. It just makes those fields on the form (or in the datasheet view of the query) non-editable because there's no associated row in Tbl_Results. If I create a matching row in Tbl_Results the query works fine, but that's not the point.
Implementation of the query is non-trivial because it requires two outer joins involving 3 tables - All from Tbl_Actions to matching in Tbl_Results, and All from Tbl_PlantComponents to matching in Tbl_Actions. This necessiates splitting the query into two - the first relates Tbl_PlantComponents, Tbl_Typicals and Tbl_Actions (returning one row for each Action for every Component), and the second performs a single outer join (using an AND) between the first query and Tbl_Results.