we have a calculated field in an append query Line_Cost which eventually creates a purchase order.
We have to have a minimum order value of £1500.00, so, we need to total the values before running the append query.
I am wondering if it is possible to use a wildcard in an update query. I would like to add text (the same word) to the end of the line item description where the starting text values are different.
example:
want to add Quantum
beginning value is:Trendsetter II -> new output value would be "Trendsetter II Quantum"
Lotem 800 -> Lotem 800 Quantum
8up -> 8up Quantum
HELP! I’m working for a charity in a small town Tanzania and there is not a soul for miles who knows anything about access.
I have created a database to track requisitions of Office Supplies. I have table full of the requisitions which contains multiple rows for each item we have in the store and the quantity requisition on each occasion.
What I want is a query that will calculate the total quantity of each item requisitioned in a month. Is there an easy way to do this??
I am working on setting up a Document Control System and have a table, called List_of_SOP where I have, amongst others, the following fields:
- Document Title
- Document Number (unique identifier)
- Responsible
- DL (where this is a multiline lookup column where one ticks the names of the people to whom this document needs to be distributed)
I want to be able to fill an array with the checked values of the DL field, given a particular Document Number. So far, the code is somewhat on these lines:
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim rs3 As DAO.Recordset
Dim db As DAO.Database
Dim strSQL1 As String
Dim strSQL2 As String
[code]....
I have a main form with 3 sub forms. The main form is tied to a table called QUOTES_MASTER. The first sub form is tied to a table called QUOTE_ LINE_ ITEMS_DIRTGLUE. It calculates the subtotal when selecting items. The relationship is one-to-many linked on QUOTE_ID.
The second sub form adds up total of all line items and is not tied to a table.The third sub form adds ESTIMATED FREIGHT to the PRODUCT TOTAL and is not tied to a table. how to get the values from the line items form inserted into the QUOTE_LINE_ITEMS_DIRTGLUE table as they are added.
I also want to insert the total value from ESTIMATED DELIVERED into the LINE_TOTALS field in the table QUOTES_MASTER.I tried this code on the product total sub form but it doesn't do anything and there are no errors:
Private Sub PROD_SUB_AfterUpdate()
DoCmd.RunSQL "UPDATE QUOTE_LINE_ITEMS_DIRTGLUE SET QUOTE_LINE_ITEMS_DIRTGLUE.SUBTOTAL = Me.PROD_SUB WHERE QUOTES_MASTER.QUOTE_ID = " & Me.QUOTE_ID
I have to import a file from an external website into a table (tableA) that I have established. The issue is that I don't need all that data, just specific ones (the website does not allow me to select the specific data to export). I created another table (tableB) to capture the data that I require for my report.
How do I take raw data from tableA to append to tableB the required fields that I want?
I am stuck on a problem!
I have a table containing line items which are appended to another table using Append Query. But in the table being appended to I have a field 'QuoteRef' and i need to append a value from a form into this field but am stuck how to do this.
I am working with some temperature data and trying to extrapolate information for a summary. I have tables that include the location of where temperature is taken, and measurements taken each hour for each station everyday for a year. I would like to extract daily maximum and minimum temperature from these data.
I tried to group by min and max temp by the date given and that didn't work. Is there an efficient way for access to calculate the max and min values from such a dataset?
I'm working on a freebie for a day care center.
I can not seem to work this out in a single query.
I have:
tblChildren
tblParents
tblGuardians
Because so many familes that use day care are fractured, it is possible for 1 parent to have multiple kids but some of them to have a different second parent.
Therefore each parent and each guardian has thier own line and own unique ID in their respective tables.
each child in tblChildren can have up to two Parent IDs (Parent_ID_1 & Parent_ID_2) and up to two Guardian IDs (Guardian_ID_1 & Guardian_ID_2)
I've set the data entry form to require at least one parent for each child, but there may not be any 2nd parent or any guardians.
So what I now need to do is create a query, or series of queries, that results with each child on it's own line, with the name of each parent, and each guardian in thier respective fields.
Has anybody got any guidance for me please?
I have no idea if this is possible or not.
I have a requirement to pull a list of Doctor's, their address, and which counties they serve. Sounds fine, right? Well, they want all the counties on one line... One record for each unique address.
So, my query would return this
Dr Address County
Dr Smith Main Street Plymouth County
Dr Smith Main Street Sarasota County
But they want it to look like this
Dr Address County
Dr Smith Main Street Plymouth County Sarasota County
Any idea on how to pull that together for them and put it in an Excel spreadsheet?
Many many thanks.
Tess
How do you import data from excel using fields that already exist? In other words, I need Access to append the data to the records rather than try and add it to the recordset and throw a tizzy when duplicates are created.
Thanks....
:( my database is a basic Customer Product Orders model.
i am have trouble when i try to append orders to my archiveOrders table. this error appears.
Microsoft Access can't append all the records in the append query.
Aicrosoft Access set 0 fields to Null due to type conversion failuer, and it didn't add 1 records to the table due to key violation, 0 records due to lock violation, and 0 records due to validation rule violation.
im very new to Access and there other thread i kinda couldn't understand :P.
i have added my database and the current append query i im designing is called qappOrders.
i really do need help, don't no where to start:(
Any help will be greatly appreciated.
I have a table that has records of accounts, actions taken, and operator ids. Unfortunately, the records are incomplete. The report was written poorly so that there is a row that contains data in all three fields for all subsequent rows there is only the action taken, no account or operator. So my table looks like that the records below. As you can imagine this means that I cannot write any useful queries on what happened on various accounts. How I can append the account number to every row that pertains to that account number?
Account
Action Taken
Operator ID
1234
Act1
1
Act2
Act3
Act4
1235
Act1
2
Act2
1236
Act1
3
Act2
Act3
Hello All,
I am exporting data from Access (2000) to Excel using a criteria form. I would like to append data to the Excel sheet without clearing the current data. How can I perform this function?
Here is part of my code:
<BEGIN CODE>
Set myXL = CreateObject("Excel.Application")
Set myWB = myXL.Workbooks.Open("C:WO_Activity_Report.xls")
myWB.Sheets ("WO_Activity_Report")
myWB.Save
myXL.Quit
Set myXL = Nothing
<END CODE>
Your assistance is greatly appreciated.
I want to import an Excel spreadsheet into a table in my Access DB.
I am using following: DoCmd.TransferSpreadsheet acImport, 0, "tblSchedule", "C:mailinimport.xls", false
This code works but it is appending to the table.
What is the best way to update the table - or would it be best to delete the old table and then to create a new one to import tha data into?
If the latter would be most elegant solution, can anyone help with some code that would do this ?
Thanks
Mat
I have a linked ODBC table in my database that contains data like this -
Code:
Task Serial CrewAmount
00_INSTALL ENGINE INTO PEDESTAL STAND 707308AS30.2
00_INSTALL ENGINE INTO PEDESTAL STAND 707308OMB0.2
01_BORESCOPE INSPECTION 706496AS114.24
01_BORESCOPE INSPECTION 706496AS223.24
[Code] .....
What I would like to do is remove the duplicate values for the Task field, but show how much time each Crew has spent on each Task per Serial. The end result would look like this -
Code:
Task SerialAS1AS2AS3OMAOMB
01_BORESCOPE INSPECTION 70649614.2423.2428.78
00_INSTALL ENGINE INTO PEDESTAL STAND 7073080.20.2
01_INSTALL OIL TANK 7073085.67
[Code] ....
What is the best way to achieve this result? I've played around with Append queries, union queries etc, but nothing seems to give me the result I'm after.
I use Cognos to pull raw data from Oracle. I take this data and put it into Access 2010 tables to then run queries. Because the people creating the data in Oracle are often slow, I run back data to catch missed information, typically two months. For October, for example, I'll run October and also September and August.While I have new data, I also have a great deal of duplicate data. How can I ensure that I append only the new data and exclude the duplicate data?
View 6 Replies View RelatedIn Access 2013 I have a query that join two tables, Conduminium Owners to their possetions (fractions of conduminium).
Owners.OwnOwners.Vote SumOfTblFracPerc
O; P................................No............... .9,54
F....................................Yes.......... ......5,48
L; Q................................Yes.............. ..7,13
M; U...............................Yes............... .7,86
H...................................Yes........... .....5,71
D; R...............................Abstention......7, 78
etc...
This query is grouping and summing, and I want put the results in a form and see it grouping the fractions in lines like this:
"These were the votes of the owners of fractions:
O;P, voted No = 9,54
F, L;Q, M;U, H, G;W, C, N, I, voted Yes = 37,72
D; R - voted Abstention = 7,78
How can I do it?
I'm fairly new to Access, but have set up a database containing 8 normalized tables. I would like to know if it's possible to update all those tables by importing an Excel file into each table, or importing the Excel file into one Access table and then appending that table to those 8 tables. AND STILL MAINTAIN THE RELATIONSHIPS. The Help directory only talks about importing or appending into one table.
View 7 Replies View RelatedI have a problem, so I am trying to append data from excel which works fine but I want to append it to the next empty available row in the datasheet view. At the moment it is just appending it to the top, first line. Im using MS Access 2007-2010.
how to do this?
I have a table in MS Access 2010 that is a link to an external data file in .csv format. I assumed that it was not possible to append data to a linked table until recently. I appended 3 records to the linked table and discovered that the 3 records were appended to the table and the external data file it was linked to.
What am I missing ?
I have 2 tables as below
Table 1
ID RID StartM EndM
1 xyz 1100 040.0935 040.1254
2 xyz 1100 029.0110 038.1003
3 xyz 1100 029.0110 038.1010
Table 2RID
StartM
EndM
[code]...
Now I need to create 3rd table based on above two tables
for table1.XYZ 1100 table1.StartM>Table2.StartM then table1.ID=2or 3 and
for table1.xyz 1100 table1.EndM< Table2.EndM then table1.ID=1
Hello all, Im trying to append data from one table in one database to another table in another database, my problem is... I have a field in the first database which has a value say 5 or 123 etc... but I need it to go into the other database as "5" or "123" , is there a way I can do this?
Any help would be awesome.
Cheers Ezy
After appending data in a table, I open a particular form. I want to display only the last record. I've added the code (DoCmd.GoToRecord , , acLast) to the On Load and On Open properties without success (opens first record).
View 14 Replies View RelatedAccess 2010 - I would like to use DLookup to show results values from a table and display in a unbound textbox on a form. the results from each column in the table need to be on seperate lines, a break if you may. Here is the code I have so far.
Code: txtKeywords = DLookup("colKeyword", "tblKEYWORDS", "cboCategory = '" & txtcategories & "'")
In colKeyword Column In tblKEYWORDS table match what i select in cboCategory Combo drop down box and populate txtcategories textbox on form
What I would like to do is show All colKeyword results in textbox [txtcategories] and display each result on its own line!
example
entry1
entry2
entry3
entry4
I would like to append a text item to each value in a calculated field. consider the field name is "Division" and I want to append the word "Division" to the values put out by the field "Division"
My attempt was: Division & " " & "Division". This produced an error in the report.