Queries :: Update A Table / Run Queries Without MS Access?

Nov 18, 2014

I'm using Microsoft Access 2010. I want to create a database that people without Microsoft Access can use. If I create a database is it possible to use VBA in Excel to update a table in Access and then run/export a query? I know you can use Excel to communicate with MS Access but can you do it when you don't have MS Access installed on your computer?

I have daily sales data that I want someone without MS Access to be able to load into the database and then export a query from.

View Replies


ADVERTISEMENT

Queries :: Create Update Query To Budget Table Using Access Design View?

Feb 17, 2015

trying to create an update query to Budget table using the Access Design View:

Field: PctSls (in tblBudget) Update to: [Expense] / [Sales]. The update query always returns 0. However, if I create a Select query using the same calculation, the correct results is displayed. PctSls is defined in the Budget table.

I haven't worked with Access (2003) for several years but this seems too simple to be causing me such frustration. (Was only a casual user even then).

View 6 Replies View Related

Queries :: Access 2007 - Update A Field In A Table With A Random Number / Long Integer

May 27, 2014

Is it possible to run a SQL command to update a field within a table with random numbers?

More specifically - random long integers linking back to an ID (autonumber) field in another table?

Background to this is, I have multiple static data tables related to each other by long integer identifiers (autonumbers)

The structure is fine but I haven't been provided with the actual data yet - but for development purposes, I need to work on other functionality which requires that this data be present.

So I want to fill my table with dummy data such that I can go off and work on the remaining functionality, but then just go back and clear it all out once I get the actual data.

I have one 'main' static table, which links back to other tables, which I have already populated with dummy static (i.e. company names, locations etc) Now I want to go into my main table and populate those fields in each record with a random ID. I don't mind doing this field-by-field (there's only a handful) but I've a lot of records in there (~1000) so I'd rather not do this record-by-record.

View 6 Replies View Related

Queries :: Update In Table From Same Table Based On Criteria Column In Same Table

Sep 29, 2013

How can I update (some columns) in a table from the same table based on a Criteria column in the same table.

View 2 Replies View Related

Queries :: DSum - Update A Field In Specific Table With Info Form Another Table

Aug 5, 2014

I have a table products with a field "id_product" and "total" (Total items in stock)

I have a query with the fields "id_product" and also the field "total in stock"

I want an update query to update the field 'total' in table 'products' with infos from that query

For each id-product in table products, replace the field total with the field 'total in stock' from the query

So I want to update a filed in a specific table with infos form another table.

View 2 Replies View Related

Queries :: Update Query (table To Table) Not Updating All Records

Nov 26, 2013

I'm using an UPDATE query to update records in one table (tblMain) from another table (tblTemp)

Here is my SQL :

Quote:

UPDATE [tblMain]
INNER JOIN [tblTemp] ON [tblMain].[MainField1] = [tblTemp].[TempField1]
SET [tblMain].[MainField2] = [tblTemp].[TempField2];

I only want to update the records in tblMain which have a corresponding record in tblTemp (linked by MainField1 / TempField1)

If any record doesn't appear in tblTemp, I want tblMain to retain the existing value for that record.

However, it appears that in such situations, the record in tblMain has it's MainField2 value set to null / ZLS.

I've tried using LEFT JOIN and RIGHT JOIN and also tried WHERE clauses but the result is the same every time.

View 3 Replies View Related

Queries :: Update Master Table Based On Slave Table

Sep 20, 2014

I'm Access 2010 newbie. I need to transfer Excel program into Access.I have a .csv file (data extracted from emails) and a Master Excel sheet. Master Excel fields are updated from the .csv - if the primary keys match, else the new records are added. Also, the .csv contains colour names, which must be translated into corresponding peoples' names.

View 3 Replies View Related

Queries :: Find Data In One Table And Put In Field Of Another Table Then Update

Nov 7, 2013

I have an "order details" table that needs to populate a field called "Voucher" with data from another table called "codes". The "codes" table also has a true/false field called "allocated" because once allocated the code cannot be re-used.

I am trying to work out how to automatically allocate the next unallocated code in the "codes" table to each record in the "order details" table when that order details record has a DiscountID of "92".

Order Details Table Fields and conditions/criteria:
ID - primary key
DiscountID - only when the DiscountID = 92
Voucher - only populated when Discount ID = 92

Codes table Fields and conditions/criteria:
ID - primary key
code = text field with a code like "einstein01", "einstein02"
Allocated = False

Is there a way to put the next available code into the order details record then mark that code as allocated in the codes table. Then, move on to the next order details record that has a discountID = 92, input the next unallocated code and mark that code etc. etc.

Ideally, I would like to do this to happen via an event when the Order forms button "Close" is clicked.

View 1 Replies View Related

Queries :: Count From A Table And Update Counted Numbers To Another Table

Sep 13, 2013

I have a query it correctly displays output as i require. i want to update/store the output query to a table named ustate. my query is as under

Code:
SELECT Auth.nit, UC+UL+UC AS Aut, (select count(NO) from Pers where nit=Auth.nit and rOrd>4) AS Present, (SELECT COUNT(no) FROM pers WHERE nitFrom = auth.nit) AS DIn, (SELECT COUNT(no) FROM pers WHERE nitTo = auth.nit) AS DOut, Present-Dout+Din AS Held
FROM Auth
WHERE (((Auth.Type)='tata'))
ORDER BY Auth.nit;

View 2 Replies View Related

Queries :: Update Query From One Table To Another

Jul 2, 2014

I have 2 tables that hold similar data. In one table tblMultiSchedule I have a few blank columns. I want to update each column(ActualCost*) with the correct data which is stored in tblOrdersItems. the corresponding fields in each tbl are

tblMultiSchedule.OrderItemID1
tblOrdersItems.OrdersItemsID

I've been trying all day to have an update query to input the actual cost data into the tblMultiSchedule.

I know the rules regarding duplicate data but this is the only way i can do this.

View 4 Replies View Related

Queries :: Update Only Last Column From Other Table

Sep 18, 2014

I have a table "Service_Information" with 6 Columns like ID, Part_No, Part_Name, Description, Morning_Records, Evening_Records.

Up to Morning_Records, the User will populate the Data with User Form.

I would like to populate the Column "Evening_Records" from Other Table "Evening_Variables" where the Data Exists. like Vlookup in Excel. Is it possible to do with Query?

View 1 Replies View Related

Queries :: Cannot Update A Table Via A Query

Aug 14, 2014

I have created the tables I need for my Access Web App.I would like to create queries.I cannot update fields that show in the query (fields have a grey frame and cannot be edited) . I have even created a completely new app with only 1 table (no relationships whatsoever in case this may influence). It is still not possible to edit any field in a query (that would update the underlying table). Are there any general settings in Access I need to enable? Is there a setting in Sharepoint I need to enable?

View 1 Replies View Related

Queries :: Update Table 1 Certain Fields Based On Table 2

Mar 9, 2015

I would like to achieve the following task described below:

Just for an example i have two tables = Table1 and Table2

Table1 contains following fields: ID , CusName, Price , Date_
Table2 Contains following fields: ID, CusName, Price, Date_

I would like to update Price and Date Field of Table 1 where Table 1 ID matching with Table 2 and Table 2 Date is maximum(most recent date).

View 4 Replies View Related

Update A Table In All Related Forms And Queries

Feb 15, 2007

I have created a database for storing, query and deleting data. Now the user wants to delete a row from a table and create two new rows. In my opinion this will need lots of work in order to replace all related queries forms and reports in the form. Is there any whay to do my job more easy in modifing a table and all related items be modified by themself? I still do not have any data in the table but if I did will I loose those data?
Thanks.

View 2 Replies View Related

Pivot Table Won't Update From Union Queries

Jan 3, 2008

I searched the internet and this forum for the answer to this. I did find the exact question posted in this forum way back in 2002 but there was no reponse...

When updating an Excel Pivot table using an Access Union Query as it's source, I receive the message "[Microsoft][ODBC Microsoft Access Driver] Too Few Parameters".

I found a rather elegant solution here (http://groups.google.co.uk/group/microsoft.public.excel.querydao/browse_thread/thread/1ca76034adc10c1a/204261bda38c118c) Unfortunately, this appears only to work for Access 2003. Does anyone have a solution for Access 2000?

Perhaps I should insist that our IT department upgrade. :p

Thanks in advance for any assistance.

- Matt

View 5 Replies View Related

Queries :: Update Join Set From Table To Query

Aug 29, 2013

I was working on an update query while joined to another table - and the error I was receiving was the query was not updatable. Er... The table that was being updated sure seemed able to be updated...

Then I wondered if the reason this didn't work was because the other table I was updating from was a query whose records were sum'd and group'd by..I ended up testing the idea by inserting the query's records into a temp table and then did the update to the target table from the temp table... which worked fine.

View 1 Replies View Related

Queries :: Append And Update Simultaneously Within Same Table

Jul 25, 2015

I am using a DB to, among other things, calculate the monthly salary of my service users. Obviously, I have to do it every moth. Most of the data from the previous month will be the same in the next month, except date. Even if some of it is different, having the previous data on sight calculate the new salaries.

Every month I have to select all records with the month date (say 07/2015) I want to copy and

(1) copy paste the records into the salaries table, and
(2) search and replace the new records' dates.

For example, records with 07/2015 with say 08/2015 (I have to replace downwards to ensure I don't change the record of the month I need to keep).

It can't be that difficult, but I have tried append and update queries to make this automatically (say by clicking a button) but so far no luck.

View 10 Replies View Related

Queries :: Update Table Based On Condition?

Aug 7, 2015

I want to update a table called tblFinalOrder, that looks like this:

In particular, I want to update each column separately with the number 1 taken from table tblSAP_XWP_SW:

My problem is that Access doesn't properly update the table how I want. I join both tables using an INNER JOIN on the SAP number. In the column AEMenge you see some 1's. So what he should do is writing these 1's into the appropriate column in my tblFinalOrder table. The condition is that the SAP number must already be in my tblSAP_XWP_SW table. If he doesn't find a 1, then skip it.

Here is my code so far. This one should update column "DynaCT". The funny thing is that DynaCT isn't available in my columnlabel column but he writes a 1 anyways (or in this case anohter number, I was playing around with it). This is what I don't understand.

Code:
UPDATE tblFinalOrder AS a
INNER JOIN tblSAP_XWP_SW AS b
ON a.SAPNr = b.sapxwpsw_sapnr
SET a.DynaCT = 1
WHERE a.SAPNR IN (SELECT sapxwpsw_sapnr FROM tblSAP_XWP_SW);

I mean, he should only write the 1 into the specific column of table tblFinalOrder, if he finds the SAP number in tblSAP_XWP_SW and if there is a 1 in this line in column AEMenge.

View 3 Replies View Related

Queries :: Update 3 Fields In Master Table

Feb 19, 2014

I'm having problems with an Update query. I have data coming in on one file that I am loading to a temp table that I want to update on another table in my database. The problem is that when I run my update query, it is wiping out everything that DOESN'T match. I have 5 fields that MUST be equal before the update can occur. I'm trying to update 3 fields in my "master" table.

Here is my SQL:

UPDATE NPPDcopy LEFT JOIN tempNPPD ON (NPPDcopy.Hour_Ending = tempNPPD.Hour_Ending) AND (NPPDcopy.TieLineName = tempNPPD.TieLineName) AND (NPPDcopy.AdjBalancingAuthority = tempNPPD.AdjBalancingAuthority) AND (NPPDcopy.BalancingAuthority = tempNPPD.BalancingAuthority) AND (NPPDcopy.OperatingDay = tempNPPD.OperatingDay) SET NPPDcopy.Import = [tempNPPD].[Import], NPPDcopy.Export = [tempNPPD].[Export], NPPDcopy.Net = [tempNPPD].[Net];

View 2 Replies View Related

Queries :: Compare A Field In Another Table And Update Accordingly

Jun 25, 2015

I am programming an Acess Database, the primary data come from an excel sheet. Since Access one to many relationship only works for primary key and another field in another table as foreign key, I can't have normalized database.

I will explain what I mean with a hypothetical situation.

So, let's say I have two tables: WindowT1, CustomerT2.

WindowT1 has fields: WinID (the pk), WindowName.
CustomerT2 fields include CustID (pk), CustomerName, WindowName, WinID.

So, in table "CustomerT2', WinID is the foreign key and I need that field filled in to make one-to-many relationship between these two tables to work. CustomerName and WindowName will be imported and prefilled already from an excel file with append query.

My question is can I make a update query (after the initial append query that brings in the data into Access) such that the criteria is matching WindowName (from CustomerT2) and the update would be the corresponding WinID (the pk number from WindowT1) that would be filled in the field WinID (in CustomerT2)? I know for this to work tables must have relationship and I did that.

Is there a better way to accomplish this task? i.e. through a form (remember info will be prefilled, so can't use combo box to fill in another form field) or maybe can I do this using the first append query (that brings in data)?

I am not good at Macro or VBA. The only thing I have done in code builder is After Update event in forms while using combo boxes. So, if there is no easier solution, I will try to deal with codes.

View 11 Replies View Related

Queries :: Update Table With The Value Of Union Query

Sep 25, 2013

I have a table with a field I want to update with the value of a Union Query

The field I want to update is Yes/No format and I can update using 0 or -1 OK.

However, when I link in the union query (and check that I only have 1 update result which is either 0 or -1) I get an error message saying that Operation must use an updatable query.

I guess this is referring to the Union Query (although I am trying to update the table and not the query.

In short - I want to update a table based on the value of a union query.

Code:
UPDATE tbl_StaMe_NGR_Subscription INNER JOIN qry_QDF_QRYDEF1 ON (tbl_StaMe_NGR_Subscription.EmailType = qry_QDF_QRYDEF1.Type) AND (tbl_StaMe_NGR_Subscription.AgentEmail = qry_QDF_QRYDEF1.Email)

Code:
SET tbl_StaMe_NGR_Subscription.Subscribed = [Subscription];

View 1 Replies View Related

Queries :: Update Column Fields In Table

Jul 15, 2015

I have a table and I write a query to update some the coloum field.

The Scenario is as follows.

I want to compare two columns in an Access

UseCase UseCaseN
n/a n/a
UC UC1
migrated no value needed

That means the value in UseCase has to be copied to UsecaseN in all cases expect when the value in UseCase is Migrated ...

View 6 Replies View Related

Queries :: Update Date Entry Using SQL In Access 2010

Sep 20, 2013

I have a date field with dates stored as MM/DD/YY, ie.10/13/09 and I want to use an UPDATE statement to change the year from 09 to 2009 but keep the 10/13/. The issue I'm encountering is when I use a wildcard in the SET portion it takes that as literal and updates with the wildcard. ie. Set Date = '*2009' WHERE Date LIKE '*09' updates the column with *2009 as the entry and not 10/13/2009. How can I update my field so I end up with 10/13/2009, not *2009?

View 4 Replies View Related

Queries :: Update Table Where Column Cell Is Empty?

Aug 6, 2015

I have a small problem: I have a table with over 20 columns. In every column there are some 1's, but the other cells or tuples are empty. In these empty cells I want to write a 0. So I need an UPDATE query.

But what should I write for the WHERE condition? So he should check every cell and if there's no 1, write a 0.

View 7 Replies View Related

Queries :: Update Fields Of One Table With Multiple Criteria

Aug 12, 2015

How can i update fields of one table with update query where creteria are more than one 1

Example:

DoCmd.RunSQL "UPDATE Salary2009toUpdate SET " & Me.Combo2 & "='" & Me.Text4 & "'
Where Empid='" & Me.Combo0 & "'" and where bank='" & Bankname & "'"

What is the syntax error ...

View 5 Replies View Related

Queries :: Update Table With Relationship To Totals Query

Oct 21, 2013

I have the following tables:

tblInvoice
tblInvoicePrePayments

They are related with a one-to-many relationship. The related field is "InvoiceId"

I want to create a query that shows selected fields from tblInvoice and the sum of field "PrepaymentValue" from table tblInvoicePrePayments.

I want to be able to edit the fields from tblInvoice in the query.

If I create a totals query (qryInvoicePrePaymentsSum) on tblInvoicePrePayments, using the fields InvoiceId and PrepaymentValue, and then create another query with tblInvoice & qryInvoicePrePaymentsSum, the fields in tblInvoice cannot be updated!

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved