Queries :: Create Query To Find Certain Records Based On Data In CSV File
Aug 14, 2014
This may not even be possible, but I am looking to create a Query that can locate records in an Access Table based on 2 columns of data that I have stored in a CSV file.
My table contains several fields, 2 of which are "Dept" and "SKU" and has over a million records.
My CSV file contains 3 fields: "Dept", "SKU" and "Total" - total being the number of times that particular Dept/SKU combination is used.
I need to be able to parse the dept/sku values from each row in the CSV to the query and locate only the records that contain the same values in the Access table.
The plan being to delete out those that are identified by the query.
I have a text file linked to this database, and I'm trying to create a Query that will pull all of the data out of this text file, and add a rank if two of the values match in multipe records.So in other words, when multiple records have the same [JOBNBR] , I want to evaluate the [TIMESTAMP] value to see if it is the smallest one, and so on.I don't want to create multiple queries to do this, is there any way to do this? Here's what I have:
Code: DDR: (Select count(*) from tblMyDataImport Where [tblMyDataImport].[TIMESTAMP] < [TIMESTAMP] AND [tblMyDataImport].[JOBNBR]=[JOBNBR] )
Code: tblPrice PosNr PriceDate Company Price 1 01.01.2014 Firma A 5 2 02.01.2014 Firma A 7 3 03.01.2014 Firma A 9 4 04.01.2014 Firma A 8 5 06.01.2014 Firma A 6 6 02.01.2014 Firma XY 11 7 03.01.2014 Firma XY 9 8 04.01.2014 Firma XY 7 9 05.01.2014 Firma XY 8 10 06.01.2014 Firma XY 10
And I have a table with the dates, for which I need a price.
Code: tblDates PosNr PriceDate Company 1 01.01.2014 Firma A 2 02.01.2014 Firma A 3 03.01.2014 Firma A 4 04.01.2014 Firma A 5 05.01.2014 Firma A (no price available) 6 06.01.2014 Firma A 7 02.01.2014 Firma XY 8 03.01.2014 Firma XY 9 04.01.2014 Firma XY 10 05.01.2014 Firma XY 11 06.01.2014 Firma XY
And now I want to combine this tables, and for the dates which have no price, the last price should be taken.
Code: tblResult PosNr PriceDate Company Price 1 01.01.2014 Firma A 5 2 02.01.2014 Firma A 7 3 03.01.2014 Firma A 9 4 04.01.2014 Firma A 8 5 05.01.2014 Firma A 8 (actualy no priceavailable, so take last price) 6 06.01.2014 Firma A 6 7 02.01.2014 Firma XY 11 8 03.01.2014 Firma XY 9 9 04.01.2014 Firma XY 7 10 05.01.2014 Firma XY 8 11 06.01.2014 Firma XY 10
how I can get this?
I have this code, but it need hours.
Code: SELECT tblDates.PosNr, tblDates.Company, tblDates.PriceDate, (SELECT TOP 1 B.Price FROM tblPrices As B WHERE B.Company = tblDates.Company AND B.PriceDate <= tblDates.PriceDate ORDER BY B.PriceDates DESC ) AS Price FROM tblPrices RIGHT JOIN tblDates ON (tblPrices.PriceDate = tblDates.PriceDates) AND (tblPrices.Company = tblDates.Company);
I used the wizard to create a find duplicates query. I know for a fact that 15 records are duplicates. I'm getting over 300 records. When I dumped the data into Excel and did a pivot table to count the number of records by account number only 15 had a total count of 2. All others only had 1. Why are the ones with no duplicates showing? I just want to see the 15.
I have a table where csv files get imported to on a daily basis. The key fields I am working with are a supply number and date. The problem I have is that sometimes the csv file will contain information that is correcting/updating information held in the table. This is creating duplicated records.I need to either create a query to find records that have the same supply number and date or find a way of overwriting the records in the table with the new data.
I have a table 45 fields. There is a field ID. I would like to get the records where fields contains "No". I would not like to create 45 queries for each field check. how can i generate a table which shows ID, Field Name that contains value "No".
I have a combo named cbogroup. I have a tblGroup with several records (active, non-active, nursery, etc.). One of the records is *ALL*. Using the CboGroup the user can pick any of the records. Howeverr, if they pick the *ALL* record, I want the query to pull up animalID based on all records in the TblGroup. If another record is picked (i.e. nursery), then the query will pull up only animalIDs that are in the 'Nursery'.Can I put a (iff then) in a query in order to differentiate a query based on all group records or a query based on only one record?
I will need to create a new column in a query with data based off of two columns currently in my DB.
Current fields are: [TestType] & [TestReason]. New TEMP field will be [TestCombo]
TestType has 4 possible option via drop down. TestReason has 21 possible options via drop down. TestCombo will be one of 10 options depending on the data in TestType & TestReason.
examples: (here is an example of how the combo field will be populated. There will be ten total rules like this. one for each possible text option in the combo box.) If [TestType] is "DRUG" & [TestReason] is "PRE" or "RAND" or "PA" then [TestCombo] would be "DOTDT".
If [TestType] is "ALCOHOL" & [TestReason] is "PRE" or "RAND" or "PA" then [TestCombo] would be "DOTAT".
If [TestType] is "DRUG" & [TestReason] is "N-PRE" or "N-OTHER" or "N-FUP" then [TestCombo] would be "NDDT".
The data does not need to be saved anywhere at all in the database, just generated when pulling this query. It will then be exported and dumped into another system.
Thanks. Here is a temp copy of the database with some junk data to maybe make it a little more clear.
I have an table that contains StudentID, Name and Age. I have imported the data from Excel sheet and there are some records which contains Null Value and some "h", "n/a" etc. I would like to design query which finds the records that are non numeric.
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.
I have a graph chart and my row source type is a Query. So I retrieved data from the Query that I built.
However , I want to get the data from the uploaded excel file.
I have a form that will prompt user in the beginning to upload the excel file and replaced the old table in Access- "CrossSystemData"
Basically , I want to pull data from Excel file , Run the query(Data from excel file) then excute to "display a chart based on my query" ... Is it possible ?
Query SQL: "PolyWrongRegInsCount"
Code: SELECT GROUPING.INSTITUTION, Count(*) AS NO_OF_GROUP FROM (SELECT DISTINCT CATEGORY, GROUP_NO, INSTITUTION, IIF(Left(INSTITUTION,1)="P","POLYCLINIC","HOSPITAL") AS INS_TYPE FROM CrossSystemData WHERE INSTITUTION NOT LIKE "*UNKNOWN*") AS GROUPING WHERE (((GROUPING.CATEGORY)="WRONG REG") AND ((GROUPING.INS_TYPE)="POLYCLINIC")) GROUP BY GROUPING.INSTITUTION;
My Chart SQL :
Code: SELECT PolyWrongRegInsCount.[INSTITUTION], PolyWrongRegInsCount.[NO_OF_GROUP] FROM PolyWrongRegInsCount;
I have created a linked Excel table in Access 2010 called 'tblExcelLinked' and I have a form called 'ASB Log Form' for the purposes of presenting the data in a more readable manner that is easier to view, plus link other fields of data that are not directly related to the 'tblExcelLinked'.
Because there is no unique ID in the 'tblExcelLinked' to create a relationship, I have created a table called 'tblASB', which allows me to add other table data linked from same d/b.
I now want to update the 'tblASB' with data from the 'tblExcelLinked', but only append new records from 'tblExcelLinked', but my inadequate append query is duplicating the records each time I run it, rather than just adding the new ones.
Once sorted my next challenge is a macro so that this runs automatically rather than being manually triggered.
does access have the ability to create a query where all records are present, all fields are present, yet some data within some fields aren't displayed?
I am trying to create a query that takes values from a search form and provides records. I was having issues with getting results when some boxes on the form were left blank. I found a solution to that and it worked with a small number of fields. However when I make the full form query (about 8-10 fields) and run it says the query is too complex. I wrote the sql as I could not get designer to do what I wanted. Attached is the sql that works and that which is "too complex".
I am trying to create a query which will return records which are from a specific month of the previous year...
To better explain:
If I have sales from 2013 and 2014 in Jan, I want a query which will show me the results from Jan in 2013 only... I'm just unsure on how to make a query do this and it's probably really simple.
But then in 2015 I will want it to show results from 2014 without having to change the expression or criteria.
I currently have a form to manage inventory. On the left side of the form, I have a list box that shows all of the items in the database and finds the record on the form based on the selection.
I would like to have toggle boxes or something underneath the list box that when clicked will sort the box by vendor and item and another that when clicked will sort the list by part number and then select the record on the form based on the selection at that time.
I have no idea how to accomplish this...can anyone help?
I have a table with an empty column called ActionID. I need to generate a numerical number that begins with 5000 and goes up by 1 number on each saved record. I have a basic form that links all of my other field to the table except the one I need to generate (ActionID) but will need that number saved to the table once its created.
I am looking for a way to use vb, or any other way, to allow my users to enter a railcar initial and then a number sequence and add new records to the end of the table.
For example in a form the user can enter the car initial and the number sequence Railcar initial: GATX Number sequence : 290001 - 290100
I would like a way to create a temp table that then has 100 records GATX 290001 GATX 290002 GATX 290003 etc etc.
I can then use this temp table in an append query to add them to my main table.
I am trying to have a query sort out my invoices by year but also to have the possibility to show all invoices.
I have one table "INVOICE" where I have a column "YEAR" calculated with DatePart function from the invoice date.
On my form "INVOICE LISTING", I want to have a combox "Combo957" selecting the year. I have forced the "ALL" selection to the combox using a UnionQuery.
My problem, I cannot get the query to work. I have tried many ways, the closest I can get is :
I have a table with a field named BinNo which has a list of items with a bin no in the format 1.234( this number can be any number up to 6000.9999).
Is there a way for me to have a field on a form where I enter a number (i.e 1) and it lists all the items with a 1 before the decimal point but not 10 or an other number with a one in it. If I enter 10 then it will only give me the items with 10 before the decimal point and not 11 etc.
I have a query that pulls all the data into it but I only want the query to show the items relating to the number I enter into the form field.
Where a many to many relationships is resolved with a linking table...Is it possible to create an entry in the linking table where the two linked tables have the same value e.g. create a entry in the linking table where client table has same value as a job profile table i.e. both are in insurance. Therefore want to create an entry in the middle table with foreign keys from either link automatically
I have developed a Debt Management program for work but I am stuck on a feature that we need to add to it. Basically I have a table which holds "planned future transactions". These are ad hoc transactions that need to be added from time to time to the clients budget. For example the client might have $500 a quarter for school fees for the next two years or he might receive a bonus every six months for the next 5 years. Currently we are putting these in manually but it is tedious and is causing complaints. The feature I require is to be able to add a record such as the school fees, nominate that this fee is paid every three months for the next 8 quarters and get the system, say through an append query? to create 8 records, each with the appropriate date 3 months after the previous one.
Any suggestions or assistance would be appreciated.
