Queries :: Update Query Using Matching Criteria From 2 Tables

Oct 18, 2013

I have 2 tables that must be involved in this query. Both have the same fields; one is a temporary table created from an Excel import which will be deleted later, the other is the permanent table.

tblStoreProducts is the permanent table. It has a 2-field Unique index; there can only be 1 of any ProductKey for a StoreKey (there can be multiple ProductKeys within a StoreKey, and multiple StoreKeys for each product, but there can only be 1 entry for each unique combination of ProductKey/StoreKey).

tblImportToStoreProducts is the temporary table. It holds all of the ProductKeys for one StoreKey.

My query needs to identify any ProductKey in the tblStoreProducts that does not exist in the tblImportToStoreProducts for that StoreKey, and then it must set the MaxUnits field to 0.

Basically, the temporary table is this years' inventory for a particular store. If a product was in that store last year but it isn't there this year, the quantity must be set to 0 because the ultimate aim is reporting hazardous materials.

View Replies


ADVERTISEMENT

Queries :: Calculate Different Timestamp For Matching Criteria

Feb 17, 2015

I have a table that records log entries for equipment and I'm trying to compose a query that calculates the time difference between consecutive timestamps for individual operators but can't quite get it to work out

The table headings are

Fatigue Log ID <----- this is the primary key for the table and is unique

Timestamp

Operator ID

View 4 Replies View Related

Tables :: Update A Specific Record Based On Matching Primary Key ID?

May 22, 2014

My problem is that I am trying to update a field (called 'Sold' which is a yes/no checkbox column) for a specific record whenever an event is triggered. I have two forms (derived from two tables), one is called frmInventory and the other is called frmSales. In frmSales, I made a combo box called 'cboItemID' that allows the user to select from a list of items from my inventory table. Each selection from the list has 4 properties, the first of which is the 'Item ID' from the inventory table. Lastly, I have a field in both frmSales and frmInventory called 'Sold' as mentioned above. What I want to do is that whenever I check/uncheck the box in the 'Sold' field in frmSales, I want the 'Sold' field in frmInventory to check/uncheck as well, but only in the record with an 'Item ID' that matches the 'Item ID' from the combo box selection. In other words, I want to match the 'Sold' field in frmInventory with the 'Sold' field in frmSales, but for only the record that has the same 'Item ID' primary key as the one I picked from my selection in the combo box from frmSales.

how to reference another table and check whether or not it's 'Item ID' primary key is identical to the one I specified from the combo box, and then take action to update the 'Sold' field if the IDs match.

View 1 Replies View Related

Queries :: Update Query Same Field Multiple Criteria?

Apr 21, 2013

I need to update the periodtype field in my table depending on different values in the field Formtype- I am looking to do this without having to use VBA. I have the following fields in a table - I want to update the value of the field Period_type as follows -

When Formtype is 10-Q, update Periodtype to "Quarterly"

When Formtype is 10-K, update Periodtype to "Annual"

The current value of Periodtype for both formtypes is "Semiannual"

Can I do this using only update query?

View 2 Replies View Related

Queries :: Matching Two Tables With Count

Jan 20, 2014

trying to create a query in which I can select a count across two tables with matching postcodes.

Table 1 : Address database with column called 'STRIPCODE' (e.g. ME8, ME9, ME10)
Table 2 : Area Code table; each column has a list of area codes to match to the STRIPCODE column. (e.g. MEDWAY column will have all of the relevant area codes)

I would pretty much want a count of each match as below :

STRIPCODE
MEDWAY 650
BLACKPOOL 563
HULL 352

Have a few hundred areas to match and tired of going through these manually and entering counts!

View 3 Replies View Related

Queries :: Count Matching Postcode Between Two Tables

Sep 1, 2014

I have two tables, one being an 'Address' file and the other an 'Area' file which is a list columns ranging from 1-400 with groups of district level postcodes. For Example; column 1 will have postcodes such as BN1, BN2, BN3, BN4, BN5 etc. Each column has a separate group of postcodes.

Now, my 'Address' file also has a column within this labelled 'District' in order for me to create a straight join (between column number and District) in a query to count each group individually, but of course, do to this 400 times becomes quite tedious! (to say the least)

I would require the outcome to be as follows :

Area file - Count
1 - 469
2 - 201
3 - 0
4 - 3598
5 - 268
. - 101
. - 8936
. - 563
399 - 287
400 - 41

View 5 Replies View Related

Queries :: Balance Update Query - Data Type Mismatch In Criteria Expression

Jul 25, 2013

I have an update query for tGLCashAccount where it adds a value from another table with the BeginningBalance to arrive at CurrentBalance.

Here's what it looks like in design view:

Field: CurrentBalance
Table: tGLCashAcct
Update to: [tMakeNewCashBal].[TotalPrice]+[tGLCashAcct].[BeginningBalance]

Here is SQL code:
UPDATE tGLCashAcct, tMakeNewCashBal SET tGLCashAcct.CurrentBalance = [tMakeNewCashBal].[TotalPrice]+[tGLCashAcct].[BeginningBalance]
WHERE (((tGLCashAcct.GLCashAcctID)="102"));

I get the error: data type mismatch in criteria expression when I run it.

View 3 Replies View Related

Queries :: How To Join Tables With No Matching - Unique Fields

Jun 16, 2013

I am undertaking an analysis of corporate mobile phone data based on data from the phone provider. The base data consists of a number of monthly text files at transaction level for voice calls, data usage, billing/tariff information, international calls/transfers etc. I have set up VBA code to import the text file data into a number of Access tables.I would like to have a way of joining these tables but the only common field is the phone number (a text field with the groups of numbers separated by hyphens).

I confirmed that joining the tables on the phone number text field does not work.My idea was to create an additional table with just one row for each phone number and link that to the other tables by the ID in the new table. I was able to create this table [PhoneNumbers] (by creating a totals query of the phone numbers from the main call transactions table, I then dumped it into Excel and then imported it into a new Access table with an auto-generated ID column).My problem / challenge is how to get the ID column from my [PhoneNumbers] table to appear in each of the other tables so that I can join them effectively. In the Excel-world, I would have used a vlookup function.I even thought of performing this as an interim step in Excel but there are too many records / rows in some of my tables. It seems that the dlookup function is not what Im looking for and even if the IIF function is suitable, I cannot get the syntax to work for me.

View 8 Replies View Related

Queries :: Update Query From 3 Tables

Oct 14, 2014

one of my tables.

tbl_type1
-type1_id
-PONumber

tbl_type2
-type2_id
-PONumber

tbl_bill
-billID
-PONumber
-bill_type

Now I have huge data from tbl_bill which contains mix PONumber depends on type. If bill_type is type1 then it will get data from tbl_type1 table and vise versa. the problem is tbl_type1 and tbl_type2 is changing, when someone update this table then the data in tbl_bill will not match to 2 tables.

Now, i need an update query where I can update all records of tbl_bill to match with tbl_type1 and tbl_type2 PONumber.

View 3 Replies View Related

Queries :: Update Query Using 3 Tables?

Sep 1, 2014

Can't figure the SQL to UPDATE a table (tblAccomResv) with columns: AccomID and DtBooked. It will be an empty table.

I need to populate it with one record for each record in tblAccomodations (ID) by each record in tblEventDts (EventDts).

Like:
Room1 Dt 1
Room1 Dt 2
Room2 Dt 1
Room2 Dt 2
etc.

View 5 Replies View Related

Queries :: Query To Select The Date From Two Tables On The Criteria Basis?

May 21, 2013

get the data from two tables on the basis of criteria...

I want to select the whole table1 which has 6 fields including Emp_id...

I want to select the single field from table2. field name is "Username" from second table2. will select the username on the basis of Emp_ID becaue both tables has same emp_ID.

It should be in order like. Emp id, Username, Startdate, Enddate, Hours, trainingNames.....

View 1 Replies View Related

Queries :: Query Data Not Matching?

Apr 19, 2013

I have a table that holds course information for students. I have a parameter query linked to this prompting the user to enter a course code thus displaying all students who have that course. However, I would like to do the opposite i.e prompt the user to enter a course code and then the result to be a list of students who do not have that course.

View 8 Replies View Related

Queries :: Matching Users And Status In Query

Jun 19, 2015

I have a database where there are 3 tables. table "d_cases", table "d_user" and table "d_status"

The table "d_cases" has the users and statuses only with a number. Peter has ID 2, John ID 1. There is status ID 1 that is New, ID 2 Started and so on.

How can I run a query that will show me the names and status instead of the numbers?

I have attached the database (2003 format) ....

View 2 Replies View Related

Queries :: Matching Year Field In 1 Query

Feb 5, 2015

Using Access 07

This is my query

SELECT QrySiteAlloc.Site, QrySiteAlloc.AllocYr, QrySiteAlloc.SiteAllocation, Sum(QryReserveTotals.SumOfAddtlAllocationAmt) AS SumOfSumOfAddtlAllocationAmt
FROM QrySiteAlloc LEFT JOIN QryReserveTotals ON QrySiteAlloc.Site = QryReserveTotals.Site
GROUP BY QrySiteAlloc.Site, QrySiteAlloc.AllocYr, QrySiteAlloc.SiteAllocation;

In the QryReserveTotals is a datereceived field.

I need the year of the date received field to match the QrySiteAlloc.AllocYr that will be selected by a parameter..

what would be the criteria to achieve this?

View 3 Replies View Related

Queries :: Query That Returns No-matching Records

Jul 15, 2013

Suppose I have two tables with fields as follows:

Assigned Courses (Courses that employees should do)Employee number
Assigned Course Name

Fulfilled Course (Courses that employees have done)Employee number
Fulfilled Course

Now I need to do a query that tells me which employees haven't done. Is there anyway to do that that is not an exclude query?

View 4 Replies View Related

Queries :: Create A Query For Non-matching Records

Jul 24, 2014

I am trying to create a query in Access 2010 for records that don't match based on the following criteria. I have two tables with identical ID's and I need to do a comparison on the amount field between both tables and only return the records from one table displaying all fields plus the amount fields that do not equal. I tried this in the wizard unsuccessfully.

View 13 Replies View Related

Queries :: Append Query - Automatically Update Tables Depending On Value Of Yes / No Field

Feb 25, 2014

I am giving two tables and I need to create a macro that automatically updates these tables depending on the value of a Yes/No field. If it's No, it's in the 1st table TableOne, if it's Yes it automatically updates to TableTwo.

So, the best way I saw to go about is to set up an append query and then create a macro that runs it

So my tables have the values FirstName, LastName and isValid (more but keeping it short)

So for my append query, I put TableTwo in the pop up I get. Then, where it asks for the field I put it

Field:FirstName
Table:TableOne
AppendTo:[TableTwo].[FirstName]
Criteria:[TableOne].[isValid] = 1

I do this for all (it was autocompleted except the Criteria field). I tried to keep Criteria with data only for isValid but that didn't work. I wrote it for all the field names, still didn't work. Whenever I click run it says it'll append 0 rows.

View 5 Replies View Related

Queries :: Query Based On Table Matching Another Query?

Jan 13, 2014

Been working on this for a while now and can't get it to work how I'd like after trying a few things.

I have a form ("Production Form") from where users input start and end times for various processes against a particular product. Currently, I have (or had) a query (and linked report) "ProductionDurations" where durations for each of the processes were calculated for different product runs.

I have since tried to adapt this query to include reference values contained in another query ("ProductionDurationsPerBulkLitre", based on a "Products" table) for how long each process should take for each production, by multiplying by the volume processed. Here's what I have at the moment in the "ProductionDurations" query:

Quote:

SELECT DateDiff("n",[BlendlineCIPStartTime],[BlendlineCIPEndTime]) AS BlendlineCIPDuration,
DateDiff("n",[FlavourMixStartTime],[FlavourMixEndTime]) AS FlavourMixDuration,
DateDiff("n",[BlendlineStartTime],[BlendlineEndTime]) AS BlendlineDuration,

[Code] ....

Rather than returning what I require, the above bolded part is returning a calculated value for each production against each of the products. What I require is a calculated value for each production against the product that has been selected on the form.

View 14 Replies View Related

Using Criteria To Update Fields In A Update Query

Nov 29, 2006

Ok, i have a question about update queries.I have two tables (I'll call table 1 and table two for simplicity) and an update query. I want to get some data from table one to table two (via an update query). But in table two there is a field that isn't in table one but i want to add a value to that field via the query.My question is, can i manually put into the query what data to add to a field instead of/aswell as using data from other tables.I hope you understood my questions.Cheers

View 3 Replies View Related

Number Of Values Matching Criteria Across A Row

Feb 1, 2007

Dear all:

I can't seem to figure this one out on my own; I'm trying to create a query that counts the number of values in fields across a row that match certain criteria. For example, the data I'm querying looks like:


ID 01-1 01-2 02-1 02-2 03-1 03-2 04-1 04-2
1 85 76 68 72 67 63 74 73
2 32 34 70 65 67 80 68 70


and I need to find out, for example, how many numbers for each id are greater than or equal to 70 but less than 80. I've tried using DCount, but it gives me the number overall that match the criteria instead of the number per row (so I'm sure I'm doing it wrong). I've tried using a crosstab query, but I can't suss out how they actually work when you've got more than one column of data that you need to work with unless I put ID as column headers and the other fields as row headers... that would make my query about 1500 columns wide and 10 rows long, though, which just seems wrong... ?

In a slightly different approach, I've done another query on the data that makes it look like:


ID Number First Second
1 01 85 76
1 02 68 72
1 03 67 63
1 04 74 73
2 01 32 34
2 02 70 65
2 03 67 80
2 04 68 70


But again, I keep ending up with silly answers (like the count for the entire set of data, rather than just per ID). I'm *sure* I'm just not quite getting how DCount works... and it's possible I'm trying to use it for something for which it wasn't designed. Any alternative examples would be most helpful.

I'm not sure how to compress the data any further. I could, technically, get it down to two columns (ID and <number to query>), but the only way I can see of doing it is to create a temporary table for the first column and append the second column's data to it, which I'd have to do every time any of the numbers changed.

I would prefer to use the first approach, as that query is useful in other calculations and the second is a bit of a faff (though not too bad, just a union query). Anyway, I'm just stuck and instead of continually banging my head against the wall, I thought I'd ask you kind folk for help. :)

-Jen

View 7 Replies View Related

Forms :: Return All Records Matching A Set Criteria

Dec 3, 2014

I have a form that is bound to a query that returns all records matching a set criteria (namely, the field ApprovalDate is null); the idea is that the user clicks on a command button which gives information, and if the user chooses to continue (vbYes), then the field ApprovalDate has its value set as the current date, and the record is saved.

Next, I have put in DoCmd.Requery to requery the form and effectively go to the next record wanting approval - this appears to work fine, however I need to add something to the code that handles a null return from the requery - preferably a message box and then have the form close.

View 5 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

Update Query Criteria

Jan 28, 2008

All -

I have an update query built that checks to see if a date is entered for when a document is mailed as well as a date for when that document was received.

I'm trying to setup this query to check and ensure that if multiple documents (Form8) were mailed that each one was marked as having a received date before the Update query add's a "Graduated" mark to the student. Screenshots are attached.

Anyone have any idea on how I can set this up?

Basically the tblFormEight can have multiple entries assocaited with each EnrollmentID (aka Student enrolled in a class) which represents multiple Form's set out in the mail. I don't want this Update query to run unless all entries for each EnrollmentID have Received Dates.

Thanks!

View 11 Replies View Related

Date Criteria In Update Query

Mar 31, 2006

What I want to do is from several records find the Max() date and update that one record with my stuff. However, I get an error message cannot use aggregate in update query or something to do that effect. Is there a way to get around this?

View 1 Replies View Related

Update Query To Choose Criteria

Mar 2, 2007

Hello, i was wondering if you could help.
I want to run an update query, taht updates prices in a table. But i would like to be able to choose the criteria when the query runs, for example a message box appears, that will allow me to enter a certain amount for the prices to change by. Could someone guide me on how to do this? thankyou, its much appreciated

View 3 Replies View Related

Ask - Update Query With Criteria Numeric

Apr 15, 2008

hi all,
I'm newbie here. please forgive me if I make mistake / wrong "room"

here what I need. I want to make update query in access 2000/2003. let say I want to update field "COST" to "0", which have condition field "ID" is 070.... (070. and something). the matter is type of field ID is numeric not string, so I can't use LIKE function. any suggest what I can do without change data type for field "ID" for permanent (I mean, if we can change data type for temporary with code, and turn it back with code too, that's no problem). I don't care, either use macro or update query (if update query can't do this task).
thanks before

View 12 Replies View Related







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