Queries :: Set Of Data That Needs To Be Reconciled

Nov 26, 2014

I have a set of data that needs to be reconciled.

* 1 User can have multiple accounts
* there are some fields that does not contain data or have different format like for the date ex( mm/dd/yy and dd/mm/yy)
* I need to get the the duplicated values and fix them accordingly, but Im having trouble in getting it done.
* I want to know which fields are to be updated as well, is it possible to do it?

sample data
account_id|fname|lname|mname|bdate|address
181431|Marie|stephen|tan|5/25/1990|EunosGreen
181432|marie|stephen|||eunos

[code]....

View Replies


ADVERTISEMENT

Queries :: Filter Data From A Table Using Query (from Data Input Form)

Dec 30, 2014

I would like to filter data from a table using a query (from an data input form). The objective is to output all results if input form field is empty and to output results higher or equal to the type in the field if field is not Null. The query code is as follows:

IIf(IsNull([Forms]![Form2]![MaxDiffInput]);[Maximum operational pressure (bar)];[Maximum operational pressure (bar)]>=[Forms]![Form2]![MaxDiffInput])

However, is not providing any result when the input field (MaxDiffInput) as a value.

View 5 Replies View Related

Queries :: How To Retrieve Only Numeric Data From F1 And Display That Data In A Field

Oct 1, 2013

Background I have a query (Q1) that retrives data from a table (Table 1). One of the fields in Table (F1) contains both text and numeric data (ie: 24 eggs). I want to separate these values in Q1.

Questions
How can i in Q1 retrive only numeric data from F1 and display that data i a field?
How can i in Q1 retrive only text from F1 and display that data i a field?

View 3 Replies View Related

Queries :: Enter Data Not Less Than Certain Data In Another Table (field)

Apr 22, 2013

I have a query called "Stock" containing field like (Item, Description, product_qty)

Another is a table called "Sales" with fields like (Sales_ID, Item, Sales_description, sales_Qty, date_Of_Sale) Item field on sales is a foreign key.

Now what i want is how can i make when the user enters new record, in field "Sales_Qty" the data entered here to be less than product_Qty.

NB; this is because you can not sell more than what you have.

View 4 Replies View Related

Queries :: Only Identical Data Listed From Tables And Not All Data

Sep 7, 2013

Attached you will find an example with the problem.

The query is only searching identical data between both tables.

I want it to show results from all users, with all the devices each user has.

What am I doing wrong with the query?

View 4 Replies View Related

Queries :: Combining Data Results From Multiple Queries

Sep 30, 2014

I am working on a report that has some special characteristics.

Let's say I have a list of groups of Vendors in a table, complete with VendorID. I have 3 other tables that use the VendorID: Complaints, Complements, and Terminations.

Each of these tables has a date that the Complaint, Complement, and Termination notice was received.

Every Fiscal Quarter, a report is pulled that looks back over the 4 preceding quarters to determine if a 5% threshold has been crossed by any of the vendor-groups in regards to the amount of Complaints they received.

The equation used for that is : (complaints/vendors_in_group)*100

It is imperative that the information has the current fiscal year and fiscal month (which I am tracking with functions from MS website), and I need to be able to store the information attached to the fiscal year and month.So when a user goes to the form and inputs the desired Fiscal Year and Fiscal Month, the database can display the 4 previous quarters of information...split into Q1, Q2, Q3, and Q4.

What I would like to have happen is to be able to have one table where the information is stored, quarterly, so that it can be retrieved for the report.

Questions:
1. Is it possible to have one line, per VendorID, that has the total number of Complements, Complaints, and Terminations, as well as the threshold percent stored in a table? Right now, I am getting LOTS of duplicates and blank lines when I try to put them all together. It has the right data, but takes about 10 rows per VendorID.

2. It is very important that the total number of Vendors in a group be captured on that quarterly report, so maintaining that number, in the same table, is essential and must be tied to the VendorID.

3. I have looked at Union Queries and Crosstabs, but I just dont know enough about them to make it work.

View 4 Replies View Related

Queries :: How To Retrieve Column Data Other Than Specified In 2 Sql Queries

Apr 14, 2014

I have a table (tbl1) contains sample records in Basecode column like S2378797 , R1165778 , W1165778 , N1165778

Description EID Basecode
----------- ---- ---------
ssdad 1001 S2378797
gfd 1002 S1164478
gfdsffsdf 1003 R1165778
ssdad 1004 M0007867
gfd 1005 N7765111

[code]....

And retrieving all **Basecode** if column data length >6 and with numbers '96', '78','54','15' by this query

Code:
SELECT tbl1.EID,tbl1.Description,tabl1.Basecode FROM tbl1
WHERE (((Len([Basecode]))>6)AND ((Left([Basecode],2))='15')) OR
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='54')) OR
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='78')) OR
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='96'));

How do i get other data which won't retrieve based on above queries, other than data mentioned on these queries like this

Description EID Basecode
----------- ---- ---------
ssdad 1001 2378797
gfd 1002 1164478
ddd 1004 1040
d88jg 1004 14C676
fsa 6565 158
fdh 1004 2Khlm
ggdg 2009 967

Third query not working

Code:
SELECT tbl1.EID,tbl1.Description,tabl1.Basecode FROM tbl1
WHERE (IsNumeric(Left(Base,1)) AND Left(Base,1) Not In ("W","N"))
AND NOT (((Len([Basecode]))>6)AND ((Left([Basecode],2))='15')) OR
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='54')) OR
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='78')) OR
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='96'));

View 2 Replies View Related

Queries :: Mark Disappear Field Data Based On Field Data Last

Oct 15, 2014

How Mark disappear field data based on field data last.

Example:I have a field type in the name and on behalf of another field No.

In the case of the Type-B data is deleted Number field, which is before the character.

View 1 Replies View Related

Repeat Queries Of The Same Data

Jul 6, 2005

I read a lot for work and the material is from many different disciplines. To keep track of all this information, I set up a simple db to keep track of all my references and have fields containing various bits of information for each record. In the beginning, I assigned one keyword to each reference as a means of retrieving articles on related subjects. Over time it has grown to three keywords per record although some of the keyword fields are null. All keywords are in a single, separate table and I add a keyword to a record from a pick-list. When I increased the number of keyword fields to three in tblCatalog, I just added more links to tblKeyword. Now, I am having a tough time trying to query tblCatalog. Obviously, my basic db organization is not right so I am looking for suggestions on how to straighten out this mess. Here is what I would like to do:

* During data entry, select form one to three keywords for each record
* During data retrieval, concatenate all the keywords (one, two or three) and
list the resulting text in a single control on a form.

This seems like it ought to be easy but not today. There must be something fundamental that I do not understand.

View 2 Replies View Related

Queries To Summarise Data

Jun 14, 2006

ok heres the thing, im not brilliant at dababses but ive created one for my work. i want to create a query to summarise the information

the database is to log customer queries and there is a field for the department responsible e.g warehouse, sales etc. this is a list box by the way

i want to summaries the date for example

between 30/5/06 to 14/6/06

warehouse had 12 queries that were responsible for them.

i can so the date part of it but i dont know how to do that.

any help will be much appriciated!

View 9 Replies View Related

Lock Down Data In Queries

Oct 12, 2006

Hello there,

I have a query that calculates price x quantity. That bit is easy. However from time to time my suppliers will issue a detailed quotation (ie lots of items, lots of prices and lots of quantities), then one week later issues the exact same quotation but with price changes and/or quantity changes. I need to show to my client the progression of the costs, so the calculated (price x quantity) column has a cost at (date) heading that needs to be updated regularly. I am looking for a way in my query to lock down the data in in the calculated column at each date, so that when I get a revised quotation, I don't have to re-enter all the items, I just change either the price or quantity. Is there a way to do it ? This is about as clear as mud, but I hope someone out there understands. Thanks for your help.

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

Data From 3 Queries Into 1 Report

Apr 11, 2008

Hi all, let me start by saying that i am not that experienced with access but find it really enjoyable and want to learn more. i dont always understand or use the right "jargon" but here goes. i have a db with 3 main tables, each table has the same layout and info inputed into it (ie, name, address, product, ref no. etc.). from each table i can print a sales reciept with all of the relevent info on it. i also print a sheet (report) with the customer names & addresses on it, used as the postal labels. i currently print 3 postal label sheets, 1 for each table. what i would like to do is print just the one report but taking the name & address data from all 3 tables. would really, really, really, really appreciate any help on this as it is beginning to frustrate me, and waste a lot of paper !!.

i'm using access from microsoft office professional plus 2007.

many thanks

dbwannabe

View 4 Replies View Related

Queries :: Why Is Data Being Deleted

Jan 30, 2014

I have the following set up:A table in access for PurchaseOrders

-A table in access for ShipmentDetails, PK linked to PurchaseOrders OrderNumber
-A table in access for payments, an index linked to the PurchaseOrders OrderNumber
-An SQL database table of purchase order details from our existing system, linked to PurchaseOrders OrderNumber

Now, I create a new record in PurchaseOrders with OrderNumber="ABCD01". All good.I create a new record in ShipmentDetails with OrderRef= "ABCD01". Still good, creating a form and adding fields shows the data is linked correctly.

I now add the CreditorCode field from the linked SQL DB, it automatically shows like it should.I try to use the form to delete the "ABCD01" record from the PurchaseOrders table and it deletes the record FROM THE SQL DATABASE! It's basically acting like Enforce Referential Integrity is turned on with deletes. This backend DB is used by other programs. I don't ever want my app to modify it.

View 4 Replies View Related

Queries :: How To Subtract Data In One Row

Mar 29, 2013

I having a problem with Access

I have one row in table, for example:

km_counter
18000
18100
18300
18800

and when I run a query, I want to get:

km_counter km_diff
18000
18100_____ 100
18300_____ 200
18800_____ 500

is it possible to do that?

View 3 Replies View Related

Reports And Queries Not Getting Data From Tables

Jun 17, 2005

Hi all,

I'm trying to create a database to track all the students details, I've created the data base with all the tables, relationship etc. i tested it with dummy data too and it worked fine.

I then imported the proper data from a .csv file, using the "File-Get External Data-Import" method rather than a macro, it imports the data with a couple of errors but the data still got through, i had previously created queries and reports and they worked fine with the dummy data but will not show any of the new imported data, its like it hasn't got a link or something but if i create a linked form, it will pick up the data, meaning the relationships are working. hence my problem

Thanks

Dellero

View 2 Replies View Related

Insert Data From Queries To A Table

Apr 29, 2008

Is it possible to select some data in queries in the same db to a table?

Many thanks!

View 3 Replies View Related

Data Access Forms And Queries

Apr 13, 2006

I have a query which shows data according to a field on a form. I am trying to get this across the department on the intranet, so I replicated the form as a data access page but i can't see how to link the Criteria row on the paricular column in my query with the field on my data Access form.

Am I missing something, like data access forms are only for displaying or something ? :o

View 2 Replies View Related

Using Query Data As Another Queries Criteria

Sep 14, 2007

Cannot seem to find an answer to this, but please point me in the correct way if you know of one!

Quite simple i think, but blank mind at moment!

How would i use the values in a table/query as the criteria for another query? I believed i could type in [qryOne]![classification] in the criteria box, but this does not seem to work.

Thanks in advance,

Emily

View 5 Replies View Related

How To Display Data From 3 Queries In A Form?

Aug 10, 2005

Hi all,

Wondering if anyone can help here? I am currently working on a form that needs to display data from 3 unique queries - a crosstab query and two standard queries (one has line by line data and another summary data) that do not have a common link.

What is the best way to do this? Would it be possible to display the results of each of these queries in a single form by creating 2 subforms from 2 of the queries and placing them in the form of the 3rd?

Any suggestions on how to combine the data from the 3 queries into the 1 form would be much appreciated!!!

Thanks in advance.

G.

View 1 Replies View Related

Queries :: Delete Duplicates In Row Of Data

Feb 10, 2014

I need to delete duplicate rows (or create new table without them) but the duplicate is not the entire row of data.

Date Time LeagueMatchingId League HomeMatchingId HomeName AwayMatchingId AwayName
HomeScore AwayScore HomeRedCardCount AwayRedCardCount FTOU FTOverOdds FTUnderOdds
20121202 10/02/2014 20:34:02 17 FRANCE LIGUE 1 147 Lorient 154 Toulouse 0 0 0 0 1.5 0.88 -0.98
20121202 10/02/2014 20:34:02 17 FRANCE LIGUE 1 147 Lorient 154 Toulouse 0 0 0 0 1.5 0.88 -0.98

[Code] .....

In this example I am only interested in the first and last rows, since the ones in between have the same data in the last few columns. They are not true duplicates since the time stamp for each is different.

Currently thinking I need to compare each and every row to the prior row in VBA and delete if criteria match.

View 4 Replies View Related

Queries :: Joining Data That Is Not Identical

Nov 25, 2014

I have a question regarding making a query with joint data that are not identical, as I'm not sure whether it's possible.

I have to look at certain serum levels at te start of a new medication regimen, and serum levels after 5 years.

I have one database with:

Patient number
Start date medication
Start date medication + 5 years
And one database with:Patient number
Date of blood test
Serum level molecule A
Serum level molecule B

What I want to do is make a query where the patient numbers are joined. The problem is that the "start date of the medication" and the "start date + 5 years" are not identical to the dates of the blood tests. E.g.: I have a patient that started medication on 01/March/2006, but he has had blood tests done every 3 months from 28/November/2003 till now, and not on 01/March/2006.

I would need the results of the blood tests (of molecule A and B) at the start of the medication and after 5 years, but if the test date differs by a day, Access already sees they're not identical.

I would like to have access select a blood test date that is as close to the medication start date as possible, within a time frame of e.g. +3 and -3 months. Is this at all possible?

View 4 Replies View Related

Queries :: Query Not Pulling Through All Data?

Oct 6, 2014

I am currently trying to pull a query off from a table that needs to have 3 columns side by side, linking to the same ID number. I seem to have managed to do this by making three seperate queries for each column I need, however, when I put it into one query, it is only pulling through the data form the second column (a total of 273 rows), when it should be pulling through the data from the first column (800+ rows).

The SQL looks like thiss:

SELECT [SM_Antennas_DL-1_Q].[Site ID], [SM_Antennas_DL-1_Q].[Antenna Type], [SM_Antennas_DL-1_Q].[Electrical DT], [SM_Antennas_DL-2_Q].[Electrical DT], [SM_Antennas_DL-3_Q].[Electrical DT]
FROM ([SM_Antennas_DL-1_Q] INNER JOIN [SM_Antennas_DL-2_Q] ON [SM_Antennas_DL-1_Q].[Site ID] = [SM_Antennas_DL-2_Q].[Site ID]) INNER JOIN [SM_Antennas_DL-3_Q] ON [SM_Antennas_DL-1_Q].[Site ID] = [SM_Antennas_DL-3_Q].[Site ID];

So the data DL-1, DL-2, DL-3 is from the same database.

View 8 Replies View Related

Queries :: Extracting Data From A Query

Jun 19, 2015

SELECT tblLeaveBalance_Cashables1.Facility, tblLeaveBalance_Cashables1.Division, tblLeaveBalance_Cashables1.Position, tblLeaveBalance_Cashables1.Name, tblLeaveBalance_Cashables1.[Class Code], tblLeaveBalance_Cashables1.Class_Title AS Classification, tblLeaveBalance_Cashables1.CBID, tblLeaveBalance_Cashables1.[SS Months], tblLeaveBalance_Cashables1.Age,

[code]....

I need to extract data that where vacation time is >640 or annual leave is >640 and where personal Hol hours are >24 and CBID is M06,S06 and R06.

View 4 Replies View Related

Queries :: What Query - Add Data From One Database

May 24, 2015

What query do I use to add data from one db [same file names] to another db?

View 1 Replies View Related

Queries :: Exclude Data Using A Table?

Jul 11, 2010

if you have a table of data (for example, where I have a long list of investors who should not show up in my queries and I don't want to try typing a very long list in a query's IN statement).

So, I created a "Quick Tutorial" to show how easy it is to use a table to exclude data from a query.

View 14 Replies View Related







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