Access Reports: Adding Lines To Separate Rows And Columns Like Excel

Ive been converting .xls files to Access database files. I would like to use ACCESS to develop the Reports but the client wants the Reports to look like those in EXCEL..eg. where you have lines between rows and columns. If I could give my Access Reports the same look and feel, I could wean these guys off of Excel and into the wonderful world of relational models.

Does anyone have sqlcode or tricks I might use to create the Excel 'look alike' report in Access?

thks in advance...and I will sum.

the ravenman.



ADVERTISEMENT

Adding Blank Lines To Seperate Rows - Version: 2000 (9.0)

I have a report that sorts rows by "job". This is typically a 3 or 4 letter name. Ex. ADG, CPH, MOR or ZMIS. I want there to be an empty row between the jobs ADG and jobs CPH. The ZMIS are misc jobs. I have to add the Z infront of the Mis to get it to sort it to the bottom. Is there a better way of doing this?

View Replies View Related

Separate Protections For Columns In Excel

Hey, does anyone know how to set up a formula that will allow me to turn either one column on, or another, but not both at the same time for data entry eg. one column for dollar figures and another for sterling currencies?

Much appreciated!

View Replies View Related

Convert Rows To Columns In EXCEL. Thanks A Lot.

I don't know if it is the right place to post this question, but I know many people here are good at both Access and Excel. The question is:

In an excel sheet,
A1: Smith, John
A2: 111 Pine St.
A3: San Diego, CA
A4: (555) 128-549
A5: Jones, Sue
A6: 222 Oak Ln.
A7: New York, NY
A8: (555) 238-1845
A9: Anderson, Tom
A10: 333 Cherry Ave.
A11: Chicago, IL
A12: (555) 581-4914


2. Type the following formula in cell C1:

=OFFSET($A$1,(ROW()-1)*4+INT((COLUMN()-3)),MOD(COLUMN()-3,1))

3. Fill this formula across to column F, and then down to row 3.

4.Note that the data is now displayed in cells C1 through F3 as follows:
Smith, John 111 Pine St. San Diego, CA (555) 128-549
Jones, Sue 222 Oak Ln. New York, NY (555) 238-1845
Anderson, Tom 333 Cherry Ave. Chicago, IL (555) 581-4914

This is exactly what I want. And I can do step 3 manually to copy that formula to each cell. But,
My question is in step 3. If I have thousands of lines, how can I fill that formula to thousands cells? from C1-C1000, F1-F1000.

I really appreciate it. Thanks.

View Replies View Related

How To Make Separate Lines For Checkboxes

Hello,

I am using ASP to create a checkbox for 5 values and users can check multiple checkboxes at the same time. I store these values on ms access db table.

If a user check 2 checkbox (e.g. Mexico, Spanish), then the values is stored in the field will be "Mexico, Spanish", separate by "," or comma.

How do I create a query or report that display these values in different line?
Example:
Mexico
Spanish

Or is there any other way to do this?

Thanks for reading.
Jenny.

View Replies View Related

Using Lines To Separate Report Sections... - Version: 2000 (9.0)

In my report I have a dotted line under the fields that comprise my detail section. It makes the report look like a pre-printed "order form" which I like. Is it possible to NOT have the line appear on the last record in the detail? That way I can have a solid line in the Group footer section which will give it a nice termination point.

View Replies View Related

Extracting Lines From A Cell And Putting Them Into Separate Colu - Version: 2002 (10.0) XP

Hello,

I am running a contest on my site and the entries are being send to my outlook box in the following format:

Name:
[person's name]

Age:
[age]

Street address:
[street address]

City, State, Zip:
[city,state,zip]

Email:
[email]

I extracted all the text in the body of these emails [over 2000] into an access database. Now I have an Access DB with one column, 2000 rows, and about 5 rows of text in each cell. Is there any way I can extract these lines of text from the cell and place them into a separate DB with each line in a different column?

I eventually want the table to look like this:

Name | Age | Street address | city,state,zip | email |

Any help would be greatly appreciated.

View Replies View Related

Totalling Columns & Rows Into Columns

I'm affraid my confusing topic title is an indicator of how confused I am by this. I can't even understand the variables well enough to fully utilize Access Help or the Search function here...

What I have is a database hat has column headers that look something like this:
Customer_Name, Order_Date, Qty_Ord, Unit_Price, Total_Price

What I'm trying to get is a query output that will have

Customer_Name, Total Orders (in Dollars) for January, Total Orders (in Dollars) for February, Total Orders (in Dollars) for March, etc.

I've been able to set it up to SUM for one month, but not multiples.

I know I'm totally lame (for proof read any of my previous posts) but you guys totally bailed me out the other time I asked a lame question.

Thanks in advance!

View Replies View Related

Parsing String Into Separate Columns Of A Table

:confused:

I have a Microsoft Access table with the following columns: A,B,C,D,E,F.
In first row of Column A, I have the following string value: "Al,Peggy,Kelly,Bud,Buck"
What I would like to do is parse this string as such:

Column B:"Al"
Column C:"Peggy"
Column D:"Kelly"
Column E:"Bud"
Column F:"Buck"

Is there a simple VB funtion to accomplish this?

View Replies View Related

Importing Excel Sheet Into Access, Phantom Lines

Hi,

I have been importing excel spreadsheets succesfully for a long time now and this new problem I have never seen before. I don't know how to deal with it. I have had a look on here but could find nothing similar, I have not really had a any luck searching google, mainly because I am at wits end to find a suitable search term.

Here's what's happening:
When i upload the sheet as is, it works fine, bar a "data conversion error" for one column which contains mainly numbers but a few cells of letters.

as before, I have inserted a line at the top of the spreadsheet (below the column headings), and put a 1 in for each numeric column and a letter for each alphanumeric and text based column.

Now when i import to access, I get no conversion error, but access has inlcuded about 20'000 blank lines between the headings and the actual data.

I really don't know what is going on.

View Replies View Related

Adding Columns To Existing Access 2003 Table

Hi,
I am fairly new to doing DBA, and I am having trouble adding a new column to a table that is existing. Is there any simple way to do this or does it have to be done throught code? And if so how.
Thanks
zorter8

View Replies View Related

Rows To Columns

Hi all,

Is there a way i can transform through a query or vba?
A | B | C
701 70-6
701 70-7
701 70-9
702 70-1
702 70-3
..... etc

transform it to

701 | 70-6 | 70-7 | 70-9
702 | 70-1 | 70-3
703 ....

Thanks in advance

View Replies View Related

Converting Rows To Columns

I'm filling in for someone who has a strange love for mail merge and now I need to covert employee history from a row to a column for the mail merge.

I currently have a list of employees and every change in position is listed as a new row. Since all employees are not the same some employees have more rows than others. What I need to do is move the history rows into columns. So what I'll end up with is one single row per employees with their history going to the right. Some employees will have more columns than others. I tried doing a crosstab but that will only let me pick one column from the original table.

I have three columns that I need repeated over and over to reflect their work history.

this is what im working with
Name or ID will be the key
Job start date
Job title
department

currently every one of theese fields is in a column but every entry has its own row.

I need a single row for every employee and the columns to repeat to the right to reflect every move.

Can someone help me :D I'm stuck :confused:

I doesnt matter if the output is in something other than access

View Replies View Related

Display Rows Into Columns

I have a two tables in my database related to employee ratings

Employee
TokenID
Division
Band

PMRating
TokenID
H1

Now this statement
Code:SELECT DISTINCT Employee.Band FROM Employee

This would display me all the band levels within Employee table in each row..

Is there any way I could built a query to the Band names as seperate columns?

i'm doing this so as to display the average H1 rating for each Band level ..that is grouped by Employee.Division..

View Replies View Related

Convert Columns To Rows

Hello All,
I have created a table in access with the following column headings:

ID, A, B, C, D, E.

Column ID contains 9 digits text;
Column A column contains email address;
---------------------------------
Column B contains Currency ($); |
Column C contains Currency ($); | Source Fields
Column D contains Currency ($); |
---------------------------------
Column E contains order number;

Now I need to normalize this table to produce a new table with this structure - UNION QUERY is ruled b/c i have more than the three source fields:

ID, Source, and AMT

Where -

ID: would equal to ID;
Source: would be the value of the heading such as B or C or D;
AMT: would be the currency value of each corresponding column.

Help with the Module or any other way to do this will be greatly appericated.

Thank you.

-testcode

View Replies View Related

Convert Columns To Rows

Hello,
I'm helping someone can help me with a little problem I am having with a peculiar data arrangement in Access. I have the following scenario. My table is set up with the following columns:

AgentID Skill_1 Skill_2 Skill_3 Skill_4
100 51 52 53 54

I want to create a new table that arranges the data like this:

AgentID Skill
100 51
100 52
100 53
100 54

Does anyone have any suggestions how I can do this?

View Replies View Related

Insert Multiple Rows In Excel From Access - Version: 2002 (10.0) XP

I am sending subform data rows to an excel spreadsheet and need to insert the number of rows below the starting point by a value captured in my subform query, with is a row count.

At the place in the code where I need to add the row insert - the excel document has already been opened, header data has already been inserted, the first row of the subform data has been inserted, and now before it loops to finish inserting, I need to insert the number of rows. This is so if the number of rows to be inserted exceeds the formatted section of the master excel form, the rows won't simply insert on the excel sheet past the formatted section and end up formatted differently. Hope that makes sense.

I have been trying versions of below, and my row cound value is dimmed as long, strRowsToInsert. How do I tell it to insert the strRowsToInsert value below the excel active cell? And is there anything special I need to do to ENSURE the formatting stays the same as the row of the active cell? So far, everything I've tried - I have been able to get the rows to insert, but they take on the formatting of another section of the spreadsheet.

Appreciate any assistance, thanks.

With oApp
Selection.EntireRow.Insert

View Replies View Related

Excel Export From Access- Deleting Old Rows - Version: 2003 (11.0)

I have a very simple Macro using the TransferSpreadsheet Action that regualary exports the contents of a table into an Excel worksheet. However the number of rows it exports can vary on a huge scale. My problem is that if the new export has less number of rows than the previous export, all the additional rows from the previous export will still remain in excel.

i.e. previous export fills rows in excel to 200. New export has rows to 150. Rows 151-200 will contain old data.

How can I stop this happening so the user does not have to go into excel each time to delete the old data first?

I'm afraid I'm not at all familiar with Modules/VBA. Is there a simple way of doing this?

View Replies View Related

Copying Rows Of Dates From Excel To Access - Version: 2003 (11.0)

I'm working on a table where I'm copying columns of dates from excel and pasting them in access. The problem I'm having is some of the cells don't have dates at all, so when I paste it into access it changes all the fields that have dates to numbers. Can someone please tell me what I can do to copy everything over even the blanks and not have them change to numbers.

Thanks

View Replies View Related

Take Data From Rows And Move Into Columns.

Hi, I have data from a form on my website in a text file, that corresponds to each visitor's input, each 13 lines in the form belongs to one visitor, as shown (twice) at the end of this message.


What I would like to do is have each visitors inputs translated to ONE row, with 13 columns/fields each. It could be appended to the same table or preferably generated in a new one. Note, there are no blank fields, some won't have data after their title, i.e. addy_line_2: is often blank, but at the very least, addy_line_2: or another field name is always there.

It would ROCK if I could also automatically take the name of each field out, i.e. each name is continuous characters up to the : (colon) ...

Thanks in advance, my Access knowledge is obviously limited, I'm sure this is fairly simple!

The fields:

Phone: 213-555-1212
Submit: Continue
addy_line_1: 1000 Melrose Place
addy_line_2:
badge:
city: Los Angeles
email:
homegroup:
name:
program:
state: CA
volunteer:
zip:
Phone:
Submit: Continue
addy_line_1:
addy_line_2:
badge:
city:
email:
homegroup:
name:
program:
state:
volunteer:
zip:

thank you thank you thank you thank you thank you !!

View Replies View Related

Query To Total Columns AND Rows

Is it possible to total columns in a query? Right now, I have a query that produces the following column counts, but I'd like to total Pending, Overdue, etc. This data is being displayed in a subform.

Process Pending Overdue Total
-------------------------------------
Engineering 1 2 3
Procurement 0 6 6
<etc>
-------------------------------------
TOTAL 1 8 9 <- this is the line I want to add

Here's what the query (qryStatusRptB) looks like thus far:
Field: Process
Table: tblProcesses
Total: Group By

Field: Pending
Table: qryStatusRptA
Total: Sum

Field: Overdue
Table: queryStatusRptA
Total: Sum

Field: TotalRecords: Count(*)
Table:
Total: Expression

Can someone explain how to get the TOTAL ROW in here? (I can do it via another query, but that won't work since the data is displayed in a subform. I've tried crosstabs without success.)

Many thanks,
Christine

View Replies View Related

Merging Data From Columns Into Rows

Hi everyone,

I can't get my head around this so I'm looking for some help if possible please, there are two questions, the first is:

When in the query, I want the criteria for the date selection to be a question, ie. "[Week Start Date?]" but I want the actual criteria selection to be from the start date plus 5 days, the only way I've done that so far is to do ">=[From?] and <=[To?]", which uses two questions and I don't seem to be able to do ">=[Week Start Date?] and <=[Week Start Date?]+5" which seems basically correct, but I expect I'm writing it incorrectly (basic access knowledge I'm afraid :( )

The second question (after I've got the 5 day date criteria sorted) is that the query produces a table that shows basically the following:

Name Store Date Visited
Tom Bury 18/01/06
Tom Bury 19/01/06
Tom Diss 20/01/06
Dick Thetford 18/01/06
Harry Diss 20/01/06

The query is based on a part week period with the starting date ALWAYS a Monday, so no more than Mon-Sat will appear, I want to put the information into a table or query, so the result ends up as:

Name Store Mon Tue Wed Thu Fri Sat
Tom Bury 18/01/06 19/01/06 'Blank' 'Blank' 'Blank' 'Blank'
Tom Diss 'Blank' 'Blank' 20/01/06 'Blank' 'Blank' 'Blank'
Dick Thetford 18/01/06 'Blank' 'Blank' 'Blank' 'Blank' 'Blank'
Harry Diss 'Blank' 'Blank' 20/01/06 'Blank' 'Blank' 'Blank'

*Where the blanks are simply left empty, rather than putting in the word 'blank'

Essentially converting the "[From?]" (as stated earlier) or "[Week Start Date?]" to Monday, that date + 1 to Tuesday, etc, BUT putting multiple dates relating to name and store criteria onto one record :eek:

Beyond me I'm afraid, any pointers would be seriously appreciated, I expect I'm approaching the problem from the wrong angle.

Regards
Tony

View Replies View Related

Merging Data Columns Into Rows

Hi everyone,

I can't get my head around this so I'm looking for some help if possible please, there are two questions, the first is:

When in the query, I want the criteria for the date selection to be a question, ie. "[Week Start Date?]" but I want the actual criteria selection to be from the start date plus 5 days, the only way I've done that so far is to do ">=[From?] and <=[To?]", which uses two questions and I don't seem to be able to do ">=[Week Start Date?] and <=[Week Start Date?]+5" which seems basically correct, but I expect I'm writing it incorrectly (basic access knowledge I'm afraid :( )

The second question (after I've got the 5 day date criteria sorted) is that the query produces a table that shows basically the following:

Name Store Date Visited
Tom Bury 18/01/06
Tom Bury 19/01/06
Tom Diss 20/01/06
Dick Thetford 18/01/06
Harry Diss 20/01/06

The query is based on a part week period with the starting date ALWAYS a Monday, so no more than Mon-Sat will appear, I want to put the information into a table or query, so the result ends up as:

Name Store Mon Tue Wed Thu Fri Sat
Tom Bury 18/01/06 19/01/06 'Blank' 'Blank' 'Blank' 'Blank'
Tom Diss 'Blank' 'Blank' 20/01/06 'Blank' 'Blank' 'Blank'
Dick Thetford 18/01/06 'Blank' 'Blank' 'Blank' 'Blank' 'Blank'
Harry Diss 'Blank' 'Blank' 20/01/06 'Blank' 'Blank' 'Blank'

*Where the blanks are simply left empty, rather than putting in the word 'blank'

Essentially converting the "[From?]" (as stated earlier) or "[Week Start Date?]" to Monday, that date + 1 to Tuesday, etc, BUT putting multiple dates relating to name and store criteria onto one record :eek:

Beyond me I'm afraid, any pointers would be seriously appreciated, I expect I'm approaching the problem from the wrong angle.

Regards
Tony

View Replies View Related

Transpose Weekday Columns To Rows

I have a table with 8 columns titled - Destination, Mon, Tues, Wed …to.. Sun. This is shown as "OriginalTable" worksheet in the attached example workbook. I want to create a new table from this original table that looks like "NewlTable" worksheet in the attached example. So I will have a new table that has 3 columns titled - Destination, Day, Weight. Weekdays will become rows so each destination will have 7 rows, one for each day of the week. The measure will be listed against each day of the week. Can someone please look at the attached example for me and help me with this.

Many thanks in advance.

View Replies View Related

Automatically Creating Columns And Rows???

I am using IIS 6.0 and frontpage 2003.
I have a web site where people submit questions and others submit answers.

I want to program the site so that each new question creates a new column automatically. (Q1=A1, Q2=B1, Q3=C1, etc)

Also, When the question is viewed on the website, there is 1 text box for someone to submit an answer.

Here is what I am trying to do:
If one person answers Q1, then this answer goes to A2. The next person to answer Q1, this answer should go into A3, and so on.

How can I do this?

Mike

View Replies View Related

Changing Data From Columns To Rows

Hi all,

I have a query which runs off a table. I have the following Fields as columns in query: WeekID, A, B, C, D, E

For each week, the letters correlate points given. So for week 1, "A" could have 1 point, "B" could have 3 points, etc.

I want to run a Query that will show A, B, C, D, E as rows like the following:

Letter_Week 1_____Week 2_____Week 3
A_______1_________5___________3___
B_______3_________4___________2___
C_______2_________1___________7___
D_______6_________3___________3___
D_______3_________1___________4___

I can't run a crosstab because it will only crosstab values within all of A, B, etc.

Any idea guys? Thanks in advance, as always.

Caliboi

View Replies View Related

Rows To Columns - Version: 2003 (11.0)

I have data in a query result that needs to be converted to columns. Column 1 is SUPV_NAME and column 2 is EMPL_NAME. Since a supervisor may have one or more employees their name appears in Supv_Name for every instance of a subordinate Empl_Name.

What i need to do is create a new table that displays the SUPV_NAME once only then moves each unique EMPL_NAME to a column.

So from this:
SUPV_NAME / EMPL_NAME
Tom / Joe
Tom / Steve

To this desired result:

SUPV_NAME / EMPL_1 / EMPL_2
Tom / Joe / Steve

I am not skilled at VB code so i am struggling. Any help would be appreciated

View Replies View Related

Changing Rows To Columns And Visa Versa

I work at a college and have created a report that automatically retrieves the instructors name, class number, class name and the scores that instructor received on his evaluations. Right now the report is showing the questions of the evaluations on a row at the top with the numbers underneath, like this:

Code:Instructor Punctual Knowledgeable RespectfulBio1 3.4 3.7 2.8CH393094Chem1CL349750 2.4 3.8 3.7My boss is wanting it to look like this:Instructor Bio1 Chem1 CH089084 CH90750Punctual 3.4 3.2Knowledge 2.8 3.7Respectful 3.8 2.8

But no matter what I do I can't get it to change to that. Even though I've set up all the things to look like that I can't get it to work.

Any suggestions?

Oh yeah, I'm using Access 2003.

View Replies View Related

How To Change Rows Into Columns? - Version: 2007 (12.0)

Hi all,
I have a problem with converting rows into columns.
Here is the query result I got:

CourseCode SubjectName Resutlt
CR1 aaa 7
CR1 bbb 8
CR1 ccc 9
CR2 aaa 7
The result I want to get is the data sheet like this:
Filer by course code and show
----------------------
aaa bbb ccc
7 8 9
----------------------

Could you please help me to solve this?

Tnks so much.

View Replies View Related

Table Columns As Rows - Version: 2003 (11.0)

Hi All,

I am a newbie and need yourl help, I have following simple table

Date.......Plan Stock........Curr stock < -- Column names
01.06....... 100 ................99
02.06.........110...............110
03.06.........100.................98

I have a chart which is based on above figures and below the chart, I want the above table be displayed in follliwng way (column names as row names:

Date ........... 01.06...02.06 ....03.06
Plan Stock........100.....110.......100
Curr Stock ........99......110 .......98

Can anyone help me to solve the above...

thanks in advance

regards
vsk

View Replies View Related

Print Columns As Rows - Version: 2003 (11.0)

I know there are threads in UA somewhere that describle how to do this but I can't find what I want.

Could someone give me a link that I could go to.

Thanks

View Replies View Related

Transforming Rows Into Columns - Version: 2002 (10.0) XP

I'm trying to create a personnel database that lists the employee's name, and his gross salary each week.

I want to be able to have two column headings: Name, Week.
Under the "week" column I want to put the gross salary amount that applies to that payroll for a specific employee.

I currently have a query which gives me each week along with the gross salary, but it has to repeat the name field as many times as there are weeks (52 each year).

How can I make each week appear as the column heading, instead of a distinct row?

View Replies View Related

Combine 6 Separate Reports Into A Single Report - Version: 2003 (11.0)

I have 6 separate Access Report Lists (there is no Parent Child Link) that I need to combine into a single report. They are all landscape tabular, but some of the reports have 3 fields some have 8 fields etc Some cover a single page and some span across a couple of pages.



I have full Adobe PDF on my machine. Is there an easy way to combine these into a single report and mainatian the headers acros multiple pages specific to each report?



Thanks

Edited by: DoctorV3774 on Mon Mar 10 14:05:13 EDT 2008.

View Replies View Related







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