Queries :: Cleaning Up Table With SQL Query?

Feb 21, 2014

In a table, there's a column, "Siebel or Sales Ref". Some of these will have a long sales reference number or an ID from a Siebel sales system (pants), in the format 1-XXXXXXX. The problem is that some have two IDs, separated by a forward slash (/), so like 1-ABC1234/1-DEF5678. He wants to go through the data, and where there's a double ID, split it out, and create a new row with the second ID.

I'm not sure this can be done in Access, I would have done a loop in Excel, but it's his database.

Code:
INSERT INTO ActualBaseData
SELECT *
FROM (SELECT 'ITQ ID', Deadline, Lot, 'Bid Progress', 'Framework Type', 'Tender Type', 'Siebel or Sales Ref'
FROM TempBaseData
WHERE InStr('Siebel or Sales Ref', '/') = 0

[Code] .....

View Replies


ADVERTISEMENT

General :: Monthly Cleaning Program - Crosstab Query To Generate A Report

Jun 12, 2012

I want to create a report for the Monthly cleaning plan of a hotel. For each day, how many rooms need new sheets, how many need new towels etc.

At this point I can generate a report for any given day.

This could be an example of what I want to achieve

Code:
------------ Date | Date+1 | Date+2 | Date+3
New Sheets 2 1 0 2
New Towels 1 3 0 1
Full Clean 0 1 2 0

"Date" is a date tat you can set, after which you'll get the following 30 days("Date+1","Date+2" etc)

I thought that a CrossTab query would give me what I want,but using the wizzard I can't get the result that I want.
Haven't worked with crosstab queries before so maybe I'm doing something wrong, or maybe this isn't even possible with a crosstab query.

View 4 Replies View Related

Cleaning Up Code?

May 5, 2006

Ok, I am working on an exsisting database at work that has alot of code/forms/reports and so on. Whoever worked on this before me apparently didn't believe indenting code or anything like that. Now I don't want to to go all this code to clean up the structure of the code. I remember back a few yeras ago I found an app for VB that did this for you. No clue where I came across it at or even what it was called but I was wondering if there was anything available like that for Access?

View 3 Replies View Related

Cleaning Data

Nov 16, 2007

Hi
I have a table that contains telephone numbers.
The data is in a variety of formats.
Here are some examples:
087 123456
087-123456
(W)087-123456
(H)123456

I would like to strip out hyphens, brackets, spaces, etc.

I hope to do this in a straighforward make table query.

Is there a function available that will strip out non-numerics characters?

Thanks
Noel

View 1 Replies View Related

General :: Cleaning Up Name Field

Sep 5, 2014

In given table name is formatted as:

HERNANDEZ JR, FELIX / BRADY, ANDEL

and I need to make it to "Felix Hernandez" How would I be able to clean up "/" , "JR," in my query.

View 8 Replies View Related

Cleaning Up Access UI And Showing Only Form

Oct 27, 2014

I want to release an Access file which has eveything locked down and cleaned up so that my Form is showing full screen (and nothing else) for an optimal user experience.

View 2 Replies View Related

How To Handle A Large Data Cleaning Task?

Jan 11, 2006

I need to create a program that will regularly import a text file of over one million records into an Access table. I've been give a list of about fifty different updates to perform on the data to clean it.

I can't imagine performing all these updates in one query. However, creating fifty individual queries seems horribly inefficient from a processing perspective.

I'm accustomed to stepping sequentially through a table in FoxPro, which seems ideal to me for this type of situation. What is the best way to handle this in Access?

View 4 Replies View Related

String Manipulation: Cleaning Up Address Labels

Dec 28, 2007

PROBLEM: String Manipulation

"Cleaning up Mom's Christmas address labels"

I need guidance on the best string manipulation functions (Instr, Left, Right) to cleanup my mother's Christmas address list of 300+ names.

I have successfully imported the text file into Excel and exported to Access; fieldnames: FULLNAME, ADDRESS, CITYSTATEZIP

I have found instructions on how to breakout FULLNAME field into FIRSTNAME and LASTNAME.

But within the FULLNAME field are many combinations of titles (Mr., Mr. & Mrs., Dr., HON.) with inconsistent periods applied.

Which one of string manipulation functions:

Instr
Left
Right


would be best for extracting these various titles from this name field?

I understand the concepts behind the above functions, but not enough experience using them to understand the tedious syntax or which string manipulation function would be best for extracting the varying title entries to a separate created field called TITLES.

So far, I have deduced this will be a multi-step process. But asking for guidance:

1.) Which string function is best suited for this?
2.) Example of the function syntax for an update query?
2.) Suggested order to administer update queries?...

to extract misc titles from the FULLNAME field.

I am a novice-casual Access user.

Thanks, Greg


(If someone would copyright these steps into a book called "Cleaning Up Mom's Christmas Address List"... I am sure they could retire from sales on Amazon. :-)

View 14 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 :: Join Multiple Fields From One Table To Same Table In A Query

Nov 21, 2014

I have a table that is basically a survey form. The same series of options was available for 35 questions, and the table used to have a text string written for each answer. Because of all the repetitive data, I created a second table that assigned a number value to each of the nine possible options in these 35 separate fields. What happened is that, instead of the same text strings repeated over and over (and taking up real estate), now each of the 35 columns had a single number in them.

Now comes the day of reckoning and TPTB want a query with the raw data and the original text strings back in instead of the numbers. I was thinking doing something along the lines of a DLookup, but I can't seem to make that work in a query correctly. Apart from calling the same table and linking it over and over to the different fields in the original data table (see photo for how insane that is).

View 2 Replies View Related

Queries :: Make Table Query - Find Table Name

Oct 14, 2013

How can you determine the name of a table that has/is going to be created by a make-table?

View 2 Replies View Related

Cleaning Up "Grouped" Data

Oct 26, 2005

I have a question and I'll apologize in advance because it may or may not really be an Access question.

I have a large ASCII spreadsheet that I have imported into Access. It has 4 columns, and has these headings: CodeID, Group, Division, Account. Many Division and Account combinations can exist within a Group, and there can be many Groups under a specific CodeID. Unfortunately the way I received the data was in a grouped fashion, like so:

CodeID Group Division Account
1234 1111 0001 0001
0002 0002
2222 0001 0001
0003 0003
1235 1111 0001 0001
0003 0003
etc.

Is there a way - easy or otherwise - to populate the blanks with the appropriate information? Essentially to "ungroup" I guess. I need to do this so I can sort and modify the data. Obviously as it is I can't do much with it.

Note, when I imported the data I had Access add unique counter as a primary key. That is allowing me to keep the data in order, but doesn't help beyond that.

Any suggestions???

View 7 Replies View Related

Queries :: Export Query To Excel Then Delete Query Data From Table

Nov 20, 2013

I am wondering if there is a quicker way to export a query to excel then have the data in that query removed from the original table. (effectively cutting the data from the table and exporting to excel)

I understand that this can be done by exporting the query to excel then running the same query as a delete query to remove the data but I just wondered if this is the most efficient way.

I have experience of VB in excel but currently only use the basic macro builder in Access though if Access VB is more efficient I can easily learn.

View 5 Replies View Related

Queries :: Run Append Query From Table A To Table B

Jul 30, 2014

how I could run an append query from table A to table B that only appends data that is not in table B.I want the primary keys of A to be exactly the same as B, because I will use B as a blank slate (another append query to append info to another table C with all the fields as 0 except for the primary key).

For example,

Table A - Supplier
Table B - Things that supplier does (blank)
Table C - Things that supplier does (information)

Lets say table A has 1,2,3,4 for supplier.Table B has 1, 2, 3, 4, as primary keys as well but all the other fields are zero.I insert PK "5" + data into table A through a data entry form, and then when I click on "save" in the data entry form, I want to macro an append/update qry (I don't know which one is supposed to be used in this instance) that will insert PK-5 into table B, so that I can append the blank slate info into table C.

View 1 Replies View Related

Queries :: Query To Insert Missing Rows From Another Query / Table

Oct 8, 2013

I am using an Access 2010 DB to keep track of a schedule. Essentially, at least one person needs to be signed up to work for every hour of every day in a week.

Tables:
Days with 7 records
Hours with 24 records
Workers with as many people that sign up to work the different hours
Schedule signifying the worker, day, and hour which are signed up.

As of now i have a query that relates these results and gives me a line detailing the worker/time information for the slots that are signed up for.What I'm TRYING to do is to create a query that gives me BLANK worker info when there is no one signed up for a particular hour.Currently my Schedule table has the following:

WorkerID | DayID | HourID
----------+---------+--------
1 | 5 | 12
4 | 5 | 13
16 | 5 | 15


What I'm looking to do is have this table matched up with another table (or query) that provides every combination of day/hour. When an day/hour combination is skipped, the query will be able to "fill in the blank" with a row. Like this:

WorkerID | DayID | HourID
----------+---------+--------
1 | 5 | 12
4 | 5 | 13
| | 14
16 | 5 | 15

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

Queries :: Insert From A Query To A Table When Query Is Changed

Jul 10, 2015

I work with access web database. In access web aggregate functions are disabled in query design. So I made a query in client and i thought then i can insert this data to another web table. but i don't know how to update this table.

Because the source table of the query is also updated.

I upload the database with a table in it and a query. I want to add the data of this query to another web table....

View 1 Replies View Related

Queries :: Getting Data From A Query Into A Table

Mar 4, 2014

I have a query (MonthlyInputQry) which calculates the total input into a production process by month (where month is a number 1-12) and there is a year (2013, 2014, etc). This query is based off a table (InputTbl) which records each individual order which goes through the process and then adds up the combined weight for all orders in the month.I also have a table (MonthlyCostsTbl) where the costs per month of the process are displayed, and on this table there is a space for to input the monthly input as calcuated by the previous query (with the aim to find the cost p/kg each month)

At the moment, at the end of each month I have been looking at the value contained within the query for the applicable month (so March 2014 would be Month = 3; Year = 2014) and copying and pasting this value into the table.

Is there a way in which I could get this value in the table to automatically update, either in the form of a running total or at the end of the month? Either from the query itself or from the original table of inputs.

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 :: Updating Table From A Query?

Jan 6, 2014

I have a table and a query both of which have the same [Resource ID], [Resource Name] fields.

I am trying to update the [CBL_1_Date], and the [CBL_1_kW] fields in the table with the [CBL_x_Date] and [CBL_x_kW] fields of the query. The problem is that when I try to run the query I keep getting "Operation Must Use and Updateable Query" error.

here is the SQL Code

UPDATE tCBLAggregation
INNER JOIN qBestof4CBLs_avg
ON (tCBLAggregation.[Event Date] = qBestof4CBLs_avg.[Event Start Date]) AND (tCBLAggregation.[Resource ID] = qBestof4CBLs_avg.[Resource ID])
SET tCBLAggregation.CBL_1_Date = [qBestof4CBLs_avg].[CBL_x_Date], tCBLAggregation.CBL_1_kW = [qBestof4CBLs_avg].[AvgOfCBL_x_kW];

View 1 Replies View Related

Queries :: Updating Table Through Query

Aug 9, 2014

I'm working on a project management database for my fathers small business. Data Entry goes as follows: Users enter a Work Order, and all the parts that must be created for that work order. The parts have different processes (tasks) done to them to be created. The database runs a simple system like this as we want it to work. I am now attempting to allow handling of more advanced work orders. Specifically, some parts require the completion of multiple other parts before they can be created.

Here is how the database should handle these events:

1) User clicks button named "requires other parts" when entering a new part.
2) On click a subform is opened that allows the user to enter which other parts need to be completed first.
3) Once all of the parts are completed the next part can begin.

This is my current solution:

1) A query calculates if a part is complete
2) Some form of logic looks up to see if all the required parts are completed. If the answer is yes, a field named ready changes to "Yes" and if the answer is no, a field named ready changes to "no"

View 9 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 :: Joining Table Without Making Another Query

Apr 30, 2014

I'm new to access. Basically I've put this formula in a field within my query(Query A):

PERIOD: [YEAR] & "-" & [Month Number]

And I want to use this newly created field "Period" to join another table (Table A) without having to create another query.

PERIOD(Query A) -> PERIOD(Table A) = Month Name (Table A)

I need the month Name from Table A but because Period(Query A) is a formula I created, I don't know any way of joining it to Table A without creating another query. Is there another way...

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 :: Multiple Similar Table Into 1 Query?

Sep 11, 2013

I'm doing a database for a friend and the challenge I have is there will be a lot of attachments going into this database, which will hit the 2gb file size limit in no time.

My solution is have each year of attachment in a separate databases and link them to the main database. The attachment are tax documents.

They table layout is all the same. It will be like this:

TaxYear, ClientID, 1040, 1040NR, 1040X, etc.....

TaxYear will be whatever year the database is designated and ClientID is the client for that particular year. The same client can shows up in multiple years.

I have thought of inserting each table in a form, but when a new year come, the form will need to be adjusted. And it will has empty field if a client skip a year or two.

View 6 Replies View Related

Queries :: Query To Select Part Or All Of A Table?

Nov 27, 2013

I need to give the user the ability to select either all of a table say

[tblStores].StoreNo

or only those stores where [tblStores].StoreNo Is Null

I cant for the life of me get my query to accept Null or Is Not Null in the parameter box.

What do I need to do.The end game is to produce a subform to allow users to maintain tblStores

eg

tblStore
StoreNo | StoreNm
000001 | StoreA
<Empty>| StoreB
000003 | StoreC

Null returns

Store B

Is Not Null returns

StoreA
StoreB
StoreC

View 2 Replies View Related







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