Queries :: Data Comparing And Updating Incorrectly

Apr 17, 2013

I have 2 tables one Access, one FoxPro both containing similar information. The idea of my query is to compare both tables and update the Access table if the value in the FoxPro table has changed. This works fine for a number of similar queries that I have. However, intermittently, this query will see two values as different when they are exactly the same and replace the value in the Access table with a seemingly random value. I know the "Random" values are from other records within the table but cannot see any link between the values.

View Replies


ADVERTISEMENT

Queries :: Comparing Data From One Field To Several Others

Aug 27, 2013

I have one table A that has a Name field that I would like to find if it matches any of the cases in table B with 2 other fields: Field 1 or Field 2

There is no way of establishing referential integrity because the Name Field can be found in either Field 1 or Field 2.

How would I go about searching so I can pull in the rest of the data that I need with when the Name is matched either with Field 1 or 2?

View 2 Replies View Related

Queries :: Comparing Data From Current To Previous Years

Oct 9, 2013

I have a table which is used to record electric billing period dates, total kWh and costs for a list of locations each month. I would like to compare the quarter total kWh and costs for this year "2013" against same quarter last year "2012". I have created two queries, the 1st is capturing data from 2013 and the 2nd is capturing data from 2012. I created a 3rd query which joins the 1st and 2nd together by month where fields match. Both tables have the same fields, however there is more records in 2012 than 2013. How can I join the tables together so months that have no data are left blank?

View 14 Replies View Related

Queries :: Comparing Data Differences Between Two Identical Access Tables?

Dec 10, 2013

I have two identical Access tables, for example tblDataYesterday and tblDataToday. The data from tblDataToday has updated data changes in it that are different from tblDataYesterday. I have a maximum of 100 fields to review. I want to compare the two tables and display in a query the differences between the data from both tables.

View 1 Replies View Related

Export To Excel Seems To Format Data Incorrectly

Aug 29, 2006

I have a standard form with header, detail, and footer sections.
The detail section has a range of calculated boxes and the footer section also has a range of calculated control boxes.

There is a button on the form, which runs the following code:
DoCmd.OutputTo acOutputForm, "Divisions", acFormatXLS, "C: ester.xls", False

This works as far as exporting the Detail section of data. However, as soon as it trys to export the footer section, it just appends them onto the end of the rows.
For example, the report lists:
ABCD 10 20
DEFG 20 30
Totals 30 50

The excel spreadsheet after export lists
Controlbox name Controlbox name Controlbox name cbn cbn
ABCD 10 20 30 50
DEFG 20 30 30 50

Is there a way to:
(a) correct this in the export;
(b) format the spreadsheet;
(c) alternative, e.g. drag the data in from a sppreadsheet ?

View 1 Replies View Related

Updating Data In Queries

Jan 31, 2005

Each month I get 20 new tables to query data from. The field layout for my query is the same each time, with about 40 fields in total. Because this is the same query each time (and only the data in each table changes), how can I best set this up. I.E. each time I run the query, I have to go back in to the design view and select the new table for each field (in this case, update all 40 fields with the new table name). Surely there is a quicker way to do this.

Thanks,

View 3 Replies View Related

Queries :: Importing Excel File With Incorrectly Formatted Date Field - CVDate Partially Functional

Jul 22, 2015

I need to import an excel file with incorrectly formatted date field and it worked only to import them as text:

1 jan 2015
1 feb 2015
1 mar 2015
etc.

Using CVdate converts jan, feb, nov and dec to correct date, but gives an error message with mar to oct.

View 1 Replies View Related

Queries :: Copying / Updating Data From A Database Table To Another

Jul 28, 2014

I have a MS Access table containing Dependent social security numbers but some of the dependents social security numbers are blank.

I have a MS Sql Database that contains a table with most of the missing MS Access table dependents Social Security numbers.

How can I take the Dependents social security number from MS Sql Database and copy/update the MS Access table.

I tried the sql code below created from MS Access and it matches 453 records out of 460 dependent social security numbers but how do I update qryGHIAccuracyFile_MissingDepSSN.SSN with the social security number from dbo_depfile1.dep_depend_ssn

Code:

SELECT dbo_depfile1.dep_depend_ssn, qryGHIAccuracyFile_MissingDepSSN.SSN
FROM qryGHIAccuracyFile_MissingDepSSN LEFT JOIN dbo_depfile1 ON qryGHIAccuracyFile_MissingDepSSN.MemberSSN = dbo_depfile1.dep_ss_nbr
WHERE (((dbo_depfile1.dep_first)=[FirstName]) AND ((dbo_depfile1.dep_last)=[LastName]));

Updating MS Access table dependent blank SSNs with SSN in MS Sql Database.

View 2 Replies View Related

Comparing Data

Jan 31, 2005

Hi all.
I have a Quotation (Table), Quotation Details (Table), with One to many Relationship. Quotation Details(Table)
selects info from Products(Table). Then I have an Order (Table) connected with Quotation (Table) and Order Details
(Table) for ordering products.
What I want to do is to compare, when i make an order, the Quotation Details with the Order Details.
For example in a a quotation the customer is quoted with 4 piesces of modem. Then the next day he comes back with the
order but now he wants 3 modems. I would like to display a warning to the user or smt like that, inform him
that the quotation details are different from order details.
Is it possible??
Thank you in advance.

View 1 Replies View Related

Comparing Data

Dec 31, 2007

I have a database where I need to take the sum of all income and categorize it to find the commission rate according to what the sum is. For example, if the sum is between $0 and $7,500, the level is "A" and commission rate is 5%. If it's between $7500.01 and $15000, the level is "B" and commission rate is 4.5% and so on. I have this working with a switch() statement, and it finds the correct level exactly as I want it to.

Here's where I'm having a problem: if the sum of all income is $9,000, $7500 of that needs to be level "A" at 5% and the other $1500 needs to be "B" at 4.5%. How do I get that to split up?

Any suggestions would be appreciated.

Thanks.

View 1 Replies View Related

Comparing And Replacing Data

Oct 17, 2006

Hi, I have 2 tables with similar data. However I need to compare this particular field called CompanyName in both tables. Reason being human error/exposition data errors. For example, Chef Kitchen Holdings Limited, in one table it is Chef Kitchen Holdings Ltd while the other table is Chef Kitchen Holdings Pte Limited, they both are the same but Access recognise them as 2 distinct datas.

Therefore I need help in comparing datas between these 2 tables for this particular field. So long as there is 75% similarities, one of the 2 tables will have the data replaced.

Is that possible using Access? if not, how about excel? please suggest.

thanks.

View 4 Replies View Related

Comparing 2 Tables Of Data

Feb 28, 2007

Hi guys, i'll try and explain this as best i can....

I have 2 tables:

CurrentMonth
PreviousMonth

Both tables hold financial information by policy number. What i am doing is working out financial movement on a month on month basis. For example, if in previous month the financial position was £100 but in current month the finances had moved to £150 then the movement is £50 for the month. This i can do no problem by linking the tables by policy number and extracting the financial position and subtracting one from the other to give me a movement. However, in some instances there will be some policies that appeared in PreviousMonth but have since been cancelled back to policy inception so is not appearing in CurrentMonth.

Problem: I need to be able to bring in the policies on the previous month that are not appearing on the current month (due to cancellation).

Can anyone help me?

Cheers

View 1 Replies View Related

Query Comparing Two Other Queries?

Mar 24, 2006

I want to compare two queries as follows: Select all the records in query1 where the value in fieldA does NOT also appear in any record in fieldA of query2. Is there an easy way to do this using the query tools or will this require a vba function?

View 3 Replies View Related

Comparing 2 Tables Or Queries

Nov 8, 2004

I have 2 tables. Quotation Details and Order Details. As you fill in the Order details Form I wish to compare fields from the
Quotation Details Table. Is this possible? Please advise.
Thank you.

View 1 Replies View Related

Comparing Data On Record At A Time

May 3, 2006

Hi - I'm working on a query, and can't seem to get it to work correctly. I have a table which contains 2 social security number fields - one for a dependent/employee with benefits, and one for the originally insured (for example, if an employee passed away, the dependent would be on the file, and the originally insured data would be populated with the original employees data). There are also cases where the employee and dependent exist in the table at the same time. I need to check the table, record by record, to identify any cases where the two SSN's are the same in any given record. The query is currently finding an employee's SSN on their record, and also finding it in the originally insured field of the dependent's record and showing it as an error. I only need to know if they occur for the same record, not for another record in the table. (I hope this makes sense!!!)

Is there any way that I can do this??? Any help would be greatly appreciated!!!!

View 4 Replies View Related

Modules & VBA :: Comparing Data In Two Tables

Jul 20, 2015

I have got 2 access tables named Table1 and Table2. There is one field common in both the tables and that is "Reference" . So I need to match the data in Reference field in both the tables using VBA.

steps to perform:

1. Check each Reference in Table1 against all the values in "Reference" field of Table2.

2. if match occurs then store all the details of that Reference in Table3.

There could be more than 1 match of that Reference in Table 2 and we need to check the whole Table2 for each "reference" value in "table1".

View 12 Replies View Related

Queries :: Comparing Two Tables Across Months

Sep 16, 2013

As a Access newbie I am looking into a problem that I hope to solve structurally and "transparently" via Access in stead of Excel.

I would like to compare two similar tables for different months. I am looking for:

+ loan id's that are new in table(t), i.e. not exist in table(t-1)
+ loan id's that exited: that is exist in table(t-1) and not in table(t)
+ loan id's that stayed in both

How do I solve this in the easiest way?

View 4 Replies View Related

Queries :: Equation For Comparing Three Quantities

Aug 6, 2014

I have three sources of data which all equate to a reconciliation of the same data e.g. the same record of a stock holding is held on three separate systems, which must all be the same.

I am trying to build a an equation in a query field, which compares each quantity:

Qty Diff: [Dealer Qty]-[Pulse Qty]-[SWFAL Qty].

If the quantities are all the same, then I want to return a Zero. If one or more of them are different, I want that difference to show. Variance of the above equation that would do it?

View 5 Replies View Related

Queries :: Comparing 2 Fields Query?

Mar 11, 2014

Trying to Compare 2 fields Status with answers Yes or No, from 2 Tables PipeLine and Pipe, and get the fields that don't match, what am i doing wrong?

SELECT PipeLine.[Project Name], PipeLine.[Capacity DC], PipeLine.[Project Num], PipeLine.[Status]
FROM PipeLine LEFT JOIN Pipe ON PipeLine.[Project Name] = Pipe.[Project Name]
WHERE (((Pipe.[Status]) ="Yes");

View 1 Replies View Related

Comparing Data In Text Box To A Prescribed Pattern

Jan 24, 2006

Hi all!

Got yet another question.

I have a text box field (txtLoanSN) for users to input serial numbers, but they can be of different formats depending on the type of service.

Examples are:
Type A - 020/2005, 001/2006
Type B - C2001/2006, C2006/2006
Type C - B1001/2005, B1021/2006

I would like to perform a series of actions based on the type of serial no. e.g. disabling another textbox (txtPNo) if txtLoanSN is like Type B or Type C.

How should the syntax for the If Statements be?

Would appreciate any help I can get!

Thanks!

View 9 Replies View Related

General :: Comparing Data From Multiple Tables

Nov 5, 2013

We have an "item" table that contains information specific to an item (the item number is unique); and multiple "production line" tables. There is a relationship between the Items table and the Production Line tables (all 8 of them). Each item can be entered on 1 or more production line tables because one item may be produced on multiple lines and the settings vary based on the line they're produced. When entering an item in the Production table we MUST make a selection from the Item table.

Now, what I want to do is identify which item number(s) from the Items table does not have a specific field entry in ANY of the Production Line tables.

View 9 Replies View Related

Comparing Data From Two Tables And Appending Into 3rd Table

Dec 8, 2012

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

View 5 Replies View Related

Queries :: Comparing Two Lists Based On Criteria

Nov 23, 2013

I have run into some problems by comparing two lists. My project is to compare two lists and on the below listed criteria's and with the wanted results.

Criteria's:
1. Part
2. Colour
3. pcs.

Wanted results:
1. Equal match (same part, Colour and pcs (pcs difference above and equal 0)
2. Equal match (same part, colour but missing pcs. (pcs difference below 0)
3. Missing parts (difference in org. list - 1 and 2 query)

But already in the first part I run into problems. I do a query as below. (picture attached)

SQL code:
SELECT Parts_All.[Lego part], Parts_All.farve, Parts_All.Antal, [Set 7897-1].[Lego part], [Set 7897-1].farve, [Set 7897-1].Antal, [Parts_All]![Antal]-[Set 7897-1]![Antal] AS Part_Diff
FROM Parts_All RIGHT JOIN [Set 7897-1] ON Parts_All.[Lego part] = [Set 7897-1].[Lego part]
GROUP BY Parts_All.[Lego part], Parts_All.farve, Parts_All.Antal, [Set 7897-1].[Lego part], [Set 7897-1].farve, [Set 7897-1].Antal
HAVING (((Parts_All.[Lego part]) Like [All_parts]![Lego part]) AND ((Parts_All.farve) Like [Set 7897-1]![farve]) AND (([Parts_All]![Antal]-[Set 7897-1]![Antal])>=0));

This query is returning 2 of the same lines with part and colour but with different pcs. Why.?

View 7 Replies View Related

Queries :: Comparing Pairs Of Records In A Query

Aug 9, 2013

I need to identify pairs of records by their ID and date fields, then compare their number values to get a text result.

I have a query that has the following fields

AuthInstanceID, ConsumerID, AuthNumberID, AuthStart, AuthEnd, PSRUnits, BSTUnits.

Sample data is below (PSRChange and BSTChange are fields I want to self populate based on the comparison I'm trying to do)

AuthInstanceID....ConsumerID....AuthNumberID....Au thStart....AuthEnd....PSRUnits....BSTUnits - PSRChange -BSTChange
1374006036.........356679..........20255102....... ....4/22/13.......7/21/13....0.............416~~~~-Loss~~~~~-None
-432536491.........356679...........20255102....... ...1/21/13.......4/21/13....104..........416~~~~-Loss~~~~~-Loss
-124970517.........356679...........20147863....... ...10/23/12.....1/20/13....208..........520~~~~-None~~~~~-Null
504564357...........469432..........20254788...... ....4/22/13.......7/21/13....0.............520~~~~-None~~~~~-None
282523535...........469432..........20254788...... ....1/21/13.......4/21/13....0.............520~~~~-None~~~~~-Gain
356661326...........469432..........20254788...... ....11/20/12......1/20/13....0.............416~~~~-None~~~~~-Null

What I need to do is compare these records in the following fashion:

Identify pairs of records where ConsumerID is the same AND AuthStart dates are successive. Then compare PSRUnits of more recent record (by AuthStart) with the previous record (by AuthStart) and determine the change of PSRUnits between them. If change is positive, "Gain" in field PSRChange, "Loss" for a loss, "None" for no change, and "Null" if there is an error due to no previous record to compare with. Do the same with BSTUnits/BSTChange

I've tried adding the following fields, but this did not work

PrevEnd: (Select Max(AuthEnd) from tblAuthorizations Where AuthEnd < Auths.[AuthStart])
PrevBST: DLookUp("[BSTUnits]","tblAuthorizations","AuthEnd=" & [PrevEnd] And "ConsumerID=" & [ConsumerID])

View 14 Replies View Related

Queries :: Comparing Two Values From Rows In Same Field

Jul 10, 2013

I have a table [VL] with four fields, [vl_id], [product], [vl_date], [valeur_liquidative].

The idea of the table is that you can input and update the value of each product on a given day.

I need to find the difference in days between successive dates (vl_date) each time that a product value (valeur_liquidative) is updated. Values aren't updated everyday as it is not updated during the weekends. I have had a go at this but have struggled..

A picture of what the table looks like is attached for reference.

View 9 Replies View Related

Queries :: Comparing Text Within A String To Keep Only Unique Values

May 15, 2013

I have one field where string contains several words separated by semicolon and my goal is to be able to remove duplicates within the string and keep only unique values. Here is an example:

initial field:
xxx;yyy;ppp;yyy

targeted result:
xxx;yyy;ppp

How this could be achieved ?

View 5 Replies View Related







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