Separate Records By Column

Apr 26, 2006

I have a table set up as the following:

ssn account
01234567 1234567890
01234567 9876543210
01234567 7539514562
98765432 8765432109
98765432 9876541230

and so on...

I would like to create a query that will split the records up like this:

ssn account1 account2 account3
01234567 1234567890 9876543210 7539514562
98765432 8765432109 9876541230

and so on...

It can be in any format, either query or report, I just have to be able to export it into a text file in the same format. Does anyone have any ideas?

Thanks!

Vassago

View Replies


ADVERTISEMENT

Split 1 Column Data Into 2 Separate Column

Oct 5, 2005

Hi,

I need a query that could split data into 2 different field.
The table pic is attached for your ready reference. I tird with Left & Right funtions with no results.

Any one can help me please.

Table name is Agcad and field on which I need to split is Board_Size. The problem is Board_Size data is sometime in 3 digit (first half part) and sometime second half part is in 4 digit. It is a text data type field.

Thanks in advance...
Ashfaque

View 1 Replies View Related

Adding Values In Separate Columns Dependent On Text In Another Column?

May 16, 2013

I'm trying to make a database to track inventory or several items. Basically, I have four tables:

1) RawMaterialList - includes a list of all raw materials.
2) PartList - includes a list of all finished product using said raw materials.
3) RawMaterialRecieving - contains details from each packing slip of incoming raw materials.
4) ShipmentRecord - contains details of daily shipments.

Each of these tables is fed by a form of the same name. I should note at this point that I basically taught myself how to use Access and I imagine I'm in the dark about quite a few things it can do. I've made several databases over the last few years, but I'm stumped at this point.

Here's my problem. In the form RawMaterialReceiving, I have several fields aside from basic information:

1) Item - a list of of raw materials from table RawMaterialList
2) Description - also dependent on info entered into table RawMaterialList
3) Quantity

But, I have 12 of these instances.

Item1, Item2...Item12;
Description1, Description2...Description12;
Quantity1, Quantity2...Quantity12.

My problem is I want to add up the quantities of each raw material and I'm not sure how to go about that. Lets say on May 13, I received 15pcs of Part A and 20pcs of Part B. I enter this information as Item1 and Item2 respectively. On May 14, I received 30pcs of Part B. I enter this information under Item1. Now I want to add up all of Part B (50 pcs). But Part B has one value listed in the field Quantity1 and one value listed in the field Quantity2.

Example:

1st Entry:
May 13
Item1 = PartA Description1 = PartA's description Quantity1 = 15
Item2 = PartB Description2 = PartB's description Quantity2 = 20

2nd Entry:
May 14
Item1 = PartB Description1 = PartB's description Quantity1 = 30

How do I get it to add up Part B to get 50pcs?

View 10 Replies View Related

Combining 2 Query Columns Into 1 Column With Data In Separate Rows

Mar 29, 2012

I have a database with all the hours employees have logged stored in the database. Our payroll company wants an excel spreadsheet that has very specific info in particular columns and fields on the excel spreadsheet, so I'm trying to design a query which will put the correct info in the correct fields per their system.

The challenge is, I have currently a query with Employee ID, Overtime Hours, and Regular Hours as separate columns.

I need to translate this to a query with a single column for hours and a separate column that designates those hours as OT or Reg, with two rows for those employees who have both types.

Current:

ID / Regular Hours / OT Hours
101 / 70 / 7.5
102 / 30 / 0
103 / 5 / 0

Needed:

ID/ Hours / Type
101 / 70 / Reg
101 / 7.5 / OT
102 / 30 / Reg
103 / 5 / Reg

I don't know how to create a query or a formula in a query to break out each employee row into multiple rows with different data in the hours column. It seems like there's something pretty straightforward that I've done in a similar vein but it doesn't seem to work - I can do the opposite and combine those hours by using the SUM function in a query, but I can't seem to break it out this way.

Access 2007, Windows 7.

View 5 Replies View Related

Exluding Records For Separate Table

Apr 29, 2006

I created a report where is lists sales volume by day. I was asked to create a table where data can be excluded from this report. The exclusion table consists of only three fields:

Date Department and a check box

What do I need to do in the query to have it exclude the data listed in this exclusion table. The table only holds data we want excluded, not all dates and departments.

I originally added the table to the source query and made the criteria "False" for the check box but when the query runs it does return any data at all.

Please help!

View 1 Replies View Related

Excluding Records From Separate Table

Apr 29, 2006

I created a report where is lists sales volume by day. I was asked to create a table where data can be excluded from this report. The exclusion table consists of only three fields:

Date Department and a check box

What do I need to do in the query to have it exclude the data listed in this exclusion table. The table only holds data we want excluded, not all dates and departments.

I originally added the table to the source query and made the criteria "False" for the check box but when the query runs it does return any data at all.

Please help!

View 1 Replies View Related

General :: Delimiting - Separate File Into Individual Records

May 24, 2013

I am trying to import several hundred records into Access from a txt file but I can't find a way to separate the file into individual records. A portion of the file is given below:

'C Olive Teece', 'Census, 1940', 'birth: 1919 New York', 'residence: 1940 Ward 4, Canandaigua, Canandaigua City, Ontario, New York', 'spouse: John H Teece', 'parents:', 'children:'
'Edith Teece', 'Census, 1940', 'birth: 1888 England', 'residence: 1940 Smithtown Town, Suffolk, New York', 'spouse:', 'parents:', 'children:'
'St Clair Teece', 'Census, 1940', 'birth: 1867 Pennsylvania', 'residence: 1940 Ward 3, New Castle, New Castle City, Lawrence, Pennsylvania', 'spouse:', 'parents:', 'child: Edward C Teece'

View 14 Replies View Related

Forms :: Use Form To Edit Records In 2 Separate Tables

Apr 2, 2013

I have the logical process, but I just don't know how to put it into code.

I have a form with two boxes HireMovieID and HireCustomerID, with a button HireButton.

The two tables which I have are CustomerInfo and MovieList.

Here's the psuedocode for what I need to happen:

Code:
IF HireMovieID.Genre = "R16"
(IF DOB > Today - 16 years)
Display Error: "Too young"
ELSE Hire()

[Code] ....

I need to somehow make it so that the MovieID is valid, as in, if it doesn't exist then it comes up with an Error MsgBox.

View 4 Replies View Related

Calculating Field Specific Averages From Records In Separate Tables

Apr 28, 2014

Let's say I have a table sort of like this one: [URL] .... (Table 1)

What I want to do is make another table that references the first table: [URL] ... (Table 2)

I want the cells in the Average field in Table 2 to calculate an average of all the values for records in Table 1 with Color fields that correspond to the Color field in Table 2 (this makes a little more sense if you look at the pictures). I could do this in Excel, but then problems would arise whenever I would add a new entry to the database, or re-alphabetized the data, since Excel math is depends entirely on the positions of cells, and I want these averages to be continually calculated correctly and to change whenever I add related records to the database.

View 1 Replies View Related

Combining Records With Attachments From Separate Computers Into A Master Database

Jun 21, 2013

We're a small shop building a modest database to keep track of our product listings on eBay. Two people on non networked computers are doing the data entry into cloned copies of our database. This includes attached photos for each record. At the end of each week, we want to combine the records created separately into the master database on my system.

It seems like it would be simple enough to just cut and the paste the records from the two databases into the identical master. The problem I'm anticipating concerns the attached photos.

Some strategies are to transfer the records from the data entry computers to the master database while keeping the attached photos intact.

Is there an effective, efficient way to set this up or would it be best to use the OLE format for the pictures instead of attaching them (which I prefer)?

View 1 Replies View Related

Modules & VBA :: Access 2007 / Code For Bulk Importing TXT File Into Separate Records Same Table?

Jul 9, 2014

I have 12,000 cvs that i need to get into An access database so i can start to extract email info etc. They are stored in one folder All Cvs under each of their names edc.txt (i have converted them to .txt) i want to create one table with two fields name (taken from the cv filename and contents (taken fromthe contents of the .txt file). I am using Access 2007.

View 2 Replies View Related

Modules & VBA :: Write A Procedure To Send A Separate Email To Each Store That Contains Records Found In Table 2

Feb 9, 2015

I have two tables

1) has email address, and Store ID
2) has multiple records per store

I need to write a procedure to send a separate email to each store that contains the records found in table 2 ( excel format).

View 3 Replies View Related

Count Records With The Same Value In Column B

Jun 24, 2005

Yes i'm having trouble with a Count problem, don't laugh at me.

I've got a query with 200000 plus records. Each record has an equipment ID number (its not the primary key) in column B. I just want to count up the number of records that have each equipment ID code, but im not sure how to do it.

Please help

View 2 Replies View Related

How To Cut Second And Third Letter In One Column Records

Nov 2, 2005

Hi!
I have one column with text field
I would like to know how to cut for. example 2nd and 3 letter in records collected in this column?
Thanks in advance for any examples

View 1 Replies View Related

Deleting Records In Column

Oct 4, 2006

I'm trying to delete just ONLY the records inside of the column, Not the whole ROW. But I just don't get it. This is what I used.

DoCmd.RunSql "DELETE tblMasterProfileSheet.QTY FROM tblMasterProfileSheet"

Everytime when I execute that script, it's deleting the whole row. I just want to delete records inside of the QTY column, not the ROW.

View 1 Replies View Related

Concantenating Records Within A Single Column

Feb 20, 2006

Hey buddays,

I'm not sure what the definition of this type of concatenation is, so I'm having trouble searching for it.

I have three tables - one is tblNames (with ID, first_name, last_name) with 25 records, and tblStates (with StateID, state_name, state_abbrev) with 50 records, and the requisite join table between them - tblNamesState (with autonumber and the two FKs), because some of the folks in tblNames can have locations in multiple states. Good enough.

But now it's output-to-spreadsheet time, and I want to run a query that will give me 25 records, and concatenate the location records in a single row. An example would be:

first_name | last_name | state_name
John | Johnson | AL, AK, TX, FL
Eddie | Edwards | MA, TX, FL
Sally | Smith | VT, WA, CA, TX

How do I concatenate records from a single column?

View 3 Replies View Related

Queries :: Records Count In New Column

Oct 21, 2013

I have a bunch of records in 1 table. Some records could have 1 duplicate field. What I am trying to do is create a query that produces the total or count in a new column. For example:

ID
FName
SName
Account#

If account appears more than once, I would like a new column to count the number of times in total that record appears. Then use this in a report afterwards.

Is this possible?

View 6 Replies View Related

Exporting Records That Match One Or More Than One Column

Jan 12, 2015

I have stored data in access. The records have name, address and unique Id. I want to be able to extract the unique ID from the Access database for all those records that match the name and address in any new file that I compare my Database with.

View 10 Replies View Related

Queries :: UPDATE Multiple Records In Same Column

Jan 16, 2015

I am trying to run below to update multiple records in the same column and get error message saying characters found after end of SQL statement. I tried to remove ; but then get a syntax error.

Code:
UPDATE [tblMonthly] SET [Date] = #20130701# where [File] = 'A';
UPDATE [tblMonthly] SET [Date] = #20130801# where [File] = 'B';

View 4 Replies View Related

Tables :: Updating One Column Of Data Across Many Records

Jul 9, 2014

I have a database containing 10,000+ trouble tickets identified by a unique field called "Ticket ID". This is the primary key for my table. (It's on just one table.) These tickets came from an excel sheet that was exported out of a different database. (For various reasons, we've moved the tickets to access.)

Among many descriptor fields, I have a field called "SPR Type" that shows what type of ticket each entry is. (Bug, Defect, Enhancement, etc, etc) The problem we have here is... not all of the ticket entries have an SPR Type listed on my access table because not all of the tickets coming out of the original excel sheet had one.

I was given a new excel sheet containing 400 to 500 ish Ticket IDs and the SPR Type that applies to each of those tickets. How can I take that sheet and use it to update my access table? Ie. Use the excel sheet containing Ticket ID and SPR Type to populate the blank SPR Type fields for each of their respective Ticket IDs?

**Update: I'm trying something like this...

UPDATE [SPR Priority] inner join [EB Update of SPR] on [SPR Priority.Ticket ID] = [EB Update of SPR.Ticket ID]
SET [SPR Priority.SPR Type] = [EB Update of SPR.SPR Type]

But I am getting an error that says "Invalid Bracketing of name [SPR Priority.Ticket ID] and similar errors. What am I getting wrong here?

I thought about an Update query that would populate the SPR Type fields based on Ticket IDs. I could import the new excel sheet into a separate access table and update from there.

View 2 Replies View Related

Forms :: Restrict Column Records Based On Criteria

Jun 16, 2014

This seem to be simple for single criteria but i am not able to do it multiple. I want to restrict user to customer list they r able to see in form combo box drop down list.

users are in employee TBL
employee group criteria is in tbl_Groups which contains value Admin,Read Only, manager etc
employee r grouped through tbl_AssignUsersToGroups.
customer r grouped in customerType TBL. i.e class1, class2 etc
created EmployeesClassTypeRestriction TBL to store restrictions for employees

Restrictions criteria:

1-employee can view only specific class which includes all customers in that class
2- employee can view only specif customers in a class only.
3-employee can view only specif customers in a ANY class.
4- No criteria No restrictions and can view all customers in all classes( For Admin )

Tried to used Tempvar criteria in customer combo box qry but it works for above point 1 only.

Attached accdb sample also.

View 5 Replies View Related

Multiple Text Search On One Column To Filter Records?

Mar 6, 2013

I have a column that has a bunch of keywords they are separated by comma... so for each row of that column it will have a few different keywords example: lake superior, river, mountain, lake wollongong

I know its a bit of a nono with databases to have columns with comma separated text.. well so i read somewhere anyway but the document i have been handed to work has hundreds of rows in this column with up to 14 keyword entries.

I have a form that searches through Item's names based on 2 keyword boxes.

Here's the criteria in my query:

Is Null Or Like "*" & [Forms]![frmItemView]![SearchPhrase1] & "*" And Like "*" & [Forms]![frmItemView]![SearchPhrase2] & "*"

This will show all records when both keywords are blank, and filter records using the 2 keywords otherwise.

I have a form with two text boxes and have set the correct values as outlined in Evans post. I then have the query set to run via a button. I run it but it will only give back records for the entry i have put in the 1st text box. This would work well for me otherwise... mine is like this:

Is NULL or Like "*" & [Forms]![Searchtable]![Key1] & "*" And Like "*" & [Forms]![Searchtable]![Key2] & "*"

Searchtable being my search form
key1 being my first text box entry
key2 being my second text box entry

View 1 Replies View Related

Modules & VBA :: How To Use Form Fields To Fill Numerous Records Under Same Column

Feb 2, 2015

I am using .FormFields to fill a quotation template in Word, but the subform I am retrieving the data from has rows of records under each column and I need to send more than just the first row to fill the required bookmarks in Word.

The code I'm using is

Set doc = appWord.Documents.Open("S:TemplatesQuotation Template - Test.doc", , True)
With doc
.FormFields("FirstName").Result = Me!FirstName
.FormFields("FirstName2").Result = Me!FirstName
.FormFields("LastName").Result = Me!LastName
.FormFields("LastName2").Result = Me!LastName

[Code] ......

View 4 Replies View Related

Forms :: List Boxes - Single Column Versus Multi Column

Apr 3, 2013

How do I select the first column of a multi-column list box (called "List1") for a query.

A single column list box works fine.

Code:

SELECT Tble_Employee.Emp_No, [forms]![attendee_form]![list1] AS SelectedCourse
FROM Tble_Employee;

View 6 Replies View Related

Queries :: Run A Simple Update Query To Copy Data From One Column To Another Column

Sep 24, 2013

I am trying to run a simple update query to copy data from one column (Addrl1)to another column (Working_Addrl1) within the same file and I can't for the life of me figure it out. Then I need to repeat for addrl2 and addrl3 to working_addrl2 and working_addrl3.

View 7 Replies View Related

Populating A Column In A Table Based On Values In An Existing Column

Mar 5, 2007

Hi all,

In the organisation that I work for employees get paid every 2 weeks on a Saturday. So for this financial year the pay period end dates have been 08/07/2006, 22/07/2006, 05/08/2006 etc


I have a column in an Access table listing various dates. I want the next column to be
populated with the next pay period end date after that date.

So if DATE is 05/07/2006 I want PAY PERIOD END to be 08/07/2006
and if DATE is 09/07/2006 I want PAY PERIOD END to be 22/07/2006 etc

How do I do this?

Kind Regards,

Matthew

View 1 Replies View Related







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