Spaces between records in a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Spaces between records in a table

Author

Topic

vgunas1
Starting Member

18 Posts

Posted - 2003-05-23 : 11:23:01

The following are the fields that I have in my table1.

ProjectNo. ProjectName ProjectLead
IT02 IT transfer Louise Alcott
EN82 Marketing Design Chris Webber
CS46 HR Online Jane Austen
EN56 Marketing Services Emily Bronte
IT53 IT website Elizabeth Ryan
IT847 Intranet Zenith May
CS01 New Hires Meg Hayden

I would like to know of a query or a macro that arranges the records in the following manner.

ProjectNo. ProjectName ProjectLead
IT02 IT transfer Louise Alcott
IT53 IT website Elizabeth Ryan

CS01 New Hires Meg Hayden
CS46 HR Online Jane Austen

EN56 Marketing Services Emily Bronte
EN82 Marketing Design Chris Webber

i.e I want a two empty records after each category. Can this be done in Access? If not i would like to know if there is any way to this using a macro and read it in the above format into an excel spreadsheet.
Please do let me know!

Thanks!

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-05-23 : 14:01:58

Why would you want empty rows in a database????

If you are trying to just get them to display I suggest that you use the Group by function of Access Report. Note: you may need to add an extra column and update it with the proper part of the string to get your groups.

Jim
Users <> Logic

vgunas1
Starting Member

18 Posts

Posted - 2003-05-23 : 17:11:04

I want the empty rows because that is the specification. I already bunched up the same type of project numbers together. All I want is empty rows between each type.

Thanks!

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-23 : 18:20:31

If you're outputting this to excel via vba you can do something like the following:
You'll have to read up on create an excel object and creating a recordset in ado.

Dim i,j As Integer
Dim strType As String

i=0
strType = ""
While Not rs.EOF
If (i> 0 and strType <> rs.Fields.Item(1) ) Then
i = i + 1
Else
For j=0 To rs.Fields.Count-1
With objExcel
.Cells(i,j).FormulaR1C1 = rs.Fields.Item(j)
End With
Next
strType = rs.Fields.Item(1)
rs.MoveNext
i=i+1
Wend

Alternatively you can try to create a set of records in a table or query that have the following data.

Group Order
IT 1
CS 2
EN 3

Then you could have the following SQL



DECLARE @Table1 TABLE (ProjectNo NVARCHAR(10), ProjectName NVARCHAR(25), ProjectLead NVARCHAR(25))
INSERT INTO @Table1 (ProjectNo, ProjectName, ProjectLead)
SELECT 'IT02','IT transfer','Louise Alcott' UNION ALL
SELECT 'EN82','Marketing Design','Chris Webber' UNION ALL 
SELECT 'CS46','HR Online Jane','Austen' UNION ALL 
SELECT 'EN56','Marketing Services','Emily Bronte' UNION ALL 
SELECT 'IT53','IT website','Elizabeth Ryan' UNION ALL 
SELECT 'IT847','Intranet','Zenith May' UNION ALL 
SELECT 'CS01','New Hires','Meg Hayden'

DECLARE @GroupingTable1 TABLE (GroupingCode NVARCHAR(2), GroupingOrder INTEGER)
INSERT INTO @GroupingTable1 (GroupingCode, GroupingOrder)
SELECT 'IT', 1 UNION ALL
SELECT 'CS', 2 UNION ALL
SELECT 'EN', 3 

SELECT AA.ProjectNo, AA.ProjectName, AA.ProjectLead
FROM
(
SELECT *
FROM @Table1 A INNER JOIN @GroupingTable1 B ON LEFT(A.ProjectNo,2) = B.GroupingCode
UNION ALL
SELECT NULL, NULL, NULL, C.GroupingCode, C.GroupingOrder
FROM @GroupingTable1 C
UNION ALL
SELECT NULL, NULL, NULL, C.GroupingCode, C.GroupingOrder
FROM @GroupingTable1 C
) AA
ORDER BY GroupingOrder, CASE WHEN ProjectNo IS NULL THEN 1 ELSE 0 END, ProjectNo, ProjectName, ProjectLead

Results:
-------------------------------------
IT02 IT transfer Louise Alcott
IT53 IT website Elizabeth Ryan
IT847 Intranet Zenith May
NULL NULL NULL
NULL NULL NULL
CS01 New Hires Meg Hayden
CS46 HR Online Jane Austen
NULL NULL NULL
NULL NULL NULL
EN56 Marketing Services Emily Bronte
EN82 Marketing Design Chris Webber
NULL NULL NULL
NULL NULL NULL

Also I notice that you use names in the field projectlead
it's a bad idea because think about if you ever had to have
hierarchies or two people with same name or mispellings (bad,bad)
Use the id from the employees table instead.

If you have time you may want to post the design of your database and let us help your build a better design.

Edited by - ValterBorges on 05/23/2003 18:21:43

Edited by - ValterBorges on 05/23/2003 18:24:00

vgunas1
Starting Member

18 Posts

Posted - 2003-05-28 : 15:33:19

We have an excel sheet with the following fields:

Project Number
Project Name
Project Status
Overall Project Manager
Project Phase
Project Type
Purpose of Project
Customer
Proposed Start Date
Approved Start Date
Proposed End Date
Approved End Date
Project Sponsor
Executive Sponsor
IT Project Manager
Business Project Manager
Project Champion
Project Classification
Program Status
Comments
Budget Review Category
Description of Purpose

We export the fields from excel into an access database. We sort the various records based on certain criteria and then export the results back to excel and run macros to format the excel sheet. Since the records are all exported from excel into access we cannot change the design of the database.So inserting the space between the records can be done either in access or in excel.
Based on the project number the records will have to be grouped and space has to be inserted between records belonging to different groups.
Eg. for project number: AM123
KT121
GS419
AM897
KT589

The records will have to be placed in the following manner:
AM123
AM897

GS419

KT121
KT589

Thanks!

quote:

If you're outputting this to excel via vba you can do something like the following:
You'll have to read up on create an excel object and creating a recordset in ado.

Dim i,j As Integer
Dim strType As String

i=0
strType = ""
While Not rs.EOF
If (i> 0 and strType <> rs.Fields.Item(1) ) Then
i = i + 1
Else
For j=0 To rs.Fields.Count-1
With objExcel
.Cells(i,j).FormulaR1C1 = rs.Fields.Item(j)
End With
Next
strType = rs.Fields.Item(1)
rs.MoveNext
i=i+1
Wend

Alternatively you can try to create a set of records in a table or query that have the following data.

Group Order
IT 1
CS 2
EN 3

Then you could have the following SQL

DECLARE @Table1 TABLE (ProjectNo NVARCHAR(10), ProjectName NVARCHAR(25), ProjectLead NVARCHAR(25))
INSERT INTO @Table1 (ProjectNo, ProjectName, ProjectLead)
SELECT 'IT02','IT transfer','Louise Alcott' UNION ALL
SELECT 'EN82','Marketing Design','Chris Webber' UNION ALL
SELECT 'CS46','HR Online Jane','Austen' UNION ALL
SELECT 'EN56','Marketing Services','Emily Bronte' UNION ALL
SELECT 'IT53','IT website','Elizabeth Ryan' UNION ALL
SELECT 'IT847','Intranet','Zenith May' UNION ALL
SELECT 'CS01','New Hires','Meg Hayden'

DECLARE @GroupingTable1 TABLE (GroupingCode NVARCHAR(2), GroupingOrder INTEGER)
INSERT INTO @GroupingTable1 (GroupingCode, GroupingOrder)
SELECT 'IT', 1 UNION ALL
SELECT 'CS', 2 UNION ALL
SELECT 'EN', 3

SELECT AA.ProjectNo, AA.ProjectName, AA.ProjectLead
FROM
(
SELECT *
FROM @Table1 A INNER JOIN @GroupingTable1 B ON LEFT(A.ProjectNo,2) = B.GroupingCode
UNION ALL
SELECT NULL, NULL, NULL, C.GroupingCode, C.GroupingOrder
FROM @GroupingTable1 C
UNION ALL
SELECT NULL, NULL, NULL, C.GroupingCode, C.GroupingOrder
FROM @GroupingTable1 C
) AA
ORDER BY GroupingOrder, CASE WHEN ProjectNo IS NULL THEN 1 ELSE 0 END, ProjectNo, ProjectName, ProjectLead

Results:
-------------------------------------
IT02 IT transfer Louise Alcott
IT53 IT website Elizabeth Ryan
IT847 Intranet Zenith May
NULL NULL NULL
NULL NULL NULL
CS01 New Hires Meg Hayden
CS46 HR Online Jane Austen
NULL NULL NULL
NULL NULL NULL
EN56 Marketing Services Emily Bronte
EN82 Marketing Design Chris Webber
NULL NULL NULL
NULL NULL NULL

Also I notice that you use names in the field projectlead
it's a bad idea because think about if you ever had to have
hierarchies or two people with same name or mispellings (bad,bad)
Use the id from the employees table instead.

If you have time you may want to post the design of your database and let us help your build a better design.

Edited by - ValterBorges on 05/23/2003 18:21:43

Edited by - ValterBorges on 05/23/2003 18:24:00

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-28 : 23:24:10

Both ideas are still ok, however since your using access if you decide to use the second method you might have to use a sub-query, not sure if access allows you to join based on a function like left(). I believe it doesn't.

If you need more specifics post some code.

vgunas1
Starting Member

18 Posts

Posted - 2003-05-29 : 10:05:21

I would like to use the query that you had posted. However,I need some clarifications about the query.

1)I would like to know exactly how the query works.
2) What do you mean by AA, A, B and C in the query?
3) Could you please give us the query specific to the fields that we had posted, if possible?

Thanks so much. Really appreciated.

quote:

Both ideas are still ok, however since your using access if you decide to use the second method you might have to use a sub-query, not sure if access allows you to join based on a function like left(). I believe it doesn't.

If you need more specifics post some code.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-29 : 22:21:02

quote:

... and run macros to format the excel sheet ....

Why not have the macro insert blank rows wherever you need them? You should not be forcing queries or databases to alter data returned when the object displaying the data is perfectly capable of formatting it as needed.

For example:

if the worksheet is sorted by col1, and between each new value in col1 you'd like there to be a blank row, just run something like this (or have your formatting macro call this):

Sub AddBlankRows(W as worksheet)

Dim i As Integer
Dim lastval As String

i = 1
For i = 1 To W.UsedRange.Rows.Count
If lastval <> W.Cells(i, 1) Then
W.Cells(i, 1).EntireRow.Insert
i = i + 1
End If
lastval = W.Cells(i, 1)
Next

End Sub

If you are already doing some work to format the data in Excel, finish up the job in Excel and add those blank lines.

- Jeff

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-30 : 00:19:10

VBA is definitely the way to go but let me explain the query.

SELECT *
FROM @Table1 A INNER JOIN @GroupingTable1 B ON LEFT(A.ProjectNo,2) = B.GroupingCode

Creates rows like the following

'IT02','IT transfer','Louise Alcott','IT', 1

SELECT NULL, NULL, NULL, C.GroupingCode, C.GroupingOrder
FROM @GroupingTable1 C

Creates rows like the following notice there are two of these.

NULL, NULL, NULL, 'IT', 1

THE UNION combines these set to give something like

'IT02','IT transfer','Louise Alcott','IT', 1
NULL, NULL, NULL, 'IT', 1
NULL, NULL, NULL, 'IT', 1

So this is what the inner portion returns.

The A, AA, BB, B those are aliases that are used to keep the table names short when you want to refer to them.

Then there is the order by

ORDER BY GroupingOrder, CASE WHEN ProjectNo IS NULL THEN 1 ELSE 0 END, ProjectNo, ProjectName, ProjectLead

first I want to order the records based on their order in the GroupingTable, next by put the projects first and nulls second that's what the case statement does, then by projectno, projectname and project lead.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources