Multiple items in one row

Okay, I am pretty new at this, so this may be obvious, but here's what I want to do.

I have an Access database that can be used to reserve resources. The user inputs a date range and then checks off what they want to reserve. It looks like this:

StartDate .............EndDate .........Laptop......Camera........Projector

10/29/2007...........10/29/2007........................X
10/29/2007...........10/31/2007..........X
10/31/2007.......... 11/01/2007........................X..................X

Of course there is other info, and a return date, but none of that is needed for this report.

I want the report to show what is available. A blank means the item is available and the X means it is unavailable. It should look like this:

Date...............Laptop.............Camera..........Projector
10/29/07.............X.....................X
10/30/07.............X
10/31/07.............X.....................X..................X
11/1/07...............X.....................X

The dates in the report come from a date table which make up Group Header 1.

This is a sample of the formula I used to get the X's to show up in the Detail section.

if {DateTable.Date}>={Reservation.StartDate}and{DateTable.Date}<={reservation.EndDate}
then if {reservation.Laptop}=true
then "X"

This works but the x's from different reservations show up on different lines. I was able to get the first detail line to match up with the date, by choosing Underlay Following Sections, but the others still show up in a different line. For example 10/31/07 looks like this:

Date.........Laptop.........Camera.............Projector
10/31/07.........X
....................................X.....................X

Your help is much appreciated!Try to create 1 formula for each of the fields you need to be shown as 'X',

for example:

@.Laptop:
if isnull(your_table.Laptop) then 'X'

@.Camera:
if isnull(your_table.Camera) then 'X'
.
.
.

Use them instead of the fields.|||I am not using fields. I am using a formula for each item. For example @.laptop looks like this:

if {DateTable.Date}>={Reservation.StartDate}and{DateTable.Date}<={reservation.EndDate}
then if {reservation.Laptop}=true
then "X"

I just want all the x's to appear on the same row as the date so if one person reserves something on 10/31 and someone else has reserved something else on the same day, (which is represented on two rows in the database) I want those items to have an X under them on the 10/31 row. I can get the first one to get up to the date row by using the Underlay, but I can not get the other rows up.

I've tried putting the details in with the group header, but everything disappears.

Maybe I should change the database? I don't know. Let me know what you think!

Multiple database files on the same disk

Hi there

It is obvious that putting multiple database files on different physical disk is better for performance, but what about splitting the data on different files on the same disk?

I have got a database of about 20GB and only a single data file. will I benefit from splitting this file to multiple files on the same disk?

The answer is not particularly!

What you may benefit from is utilising multiple filegroups, rather than multiple files in a single filegroup. This will allow you to physically place different database tables and indexes into different physical files, which will reduce fragmentation. It is usually also a good idea to create a Data filegroup and make that file the DEFAULT filegroup for new object creation. In this way, your data is stored in a physically separate file to the internal sql server database tables which will still exist in the PRIMARY filegroup.

Hope this helps

BigE

|||

Thanks BigE.

I understand that, but what if I do not have an extra disk, will I bebefit from splitting the big file into multilple files or it does not make any difference if I leave it on a single physical file/file group? will it ease SQL Server's IO work to deal with few files (all on the same disk) rather than with one big file ?

|||

Hi Aviel,

If your database is very huge-large and very active (busy), multiple files can be used to increase performance.

If the table is included in a single data file, SQL server would use only 1 thread to achieve a read of the rows in it.

But if the table were separated into 2 physical files, SQL server would use 2 threads to read it, which potentially could be faster.

In Addition, if each file were on its own disk array or physical disk, the performance gain would even be greater.

Regards,

Tarek Ghazali

SQL Server MVP

Web Site: http://www.sqlmvp.com

|||

If you've only got one physical disk, multiple IO threads will slow things down as you'll be forcing far less efficient random IO instead of a single sequential read which is much faster.

If IO's your bottleneck, upgrade the IO subsystem!

|||

Thanks Tarek and BigE.

Now I am in kind of a dilemma. Do multiple IO threads for one physical file is better or worse. Any way, I think that IO is not my biggest problem, but I try to make things better and elegant, and having one hugr file of about 20GB seem a bit inelegant.

|||Don't worry about SQL Server finding the positions that it wants within a large file: Infact, the structure of the SQL Server files is much more suited to finding data quickly than the structure of the NTFS file system!|||

Hi Tarek,

Is it possible to have one table data in 2 different files? I did know that.

|||

Hi Roman,

Table partitioning is a powerful new feature in SQL Server 2005, You can implement a horizontal data partition where you can split your data (records) in a specific table in different filegroups - datafiles, for more details check the following URL:
http://msdn2.microsoft.com/en-us/library/ms345146.aspx

Regards

Tarek Ghazali

SQL Server MVP

Web Site: http://www.sqlmvp.com

Multiple database files on the same disk

Hi there

It is obvious that putting multiple database files on different physical disk is better for performance, but what about splitting the data on different files on the same disk?

I have got a database of about 20GB and only a single data file. will I benefit from splitting this file to multiple files on the same disk?

The answer is not particularly!

What you may benefit from is utilising multiple filegroups, rather than multiple files in a single filegroup. This will allow you to physically place different database tables and indexes into different physical files, which will reduce fragmentation. It is usually also a good idea to create a Data filegroup and make that file the DEFAULT filegroup for new object creation. In this way, your data is stored in a physically separate file to the internal sql server database tables which will still exist in the PRIMARY filegroup.

Hope this helps

BigE

|||

Thanks BigE.

I understand that, but what if I do not have an extra disk, will I bebefit from splitting the big file into multilple files or it does not make any difference if I leave it on a single physical file/file group? will it ease SQL Server's IO work to deal with few files (all on the same disk) rather than with one big file ?

|||

Hi Aviel,

If your database is very huge-large and very active (busy), multiple files can be used to increase performance.

If the table is included in a single data file, SQL server would use only 1 thread to achieve a read of the rows in it.

But if the table were separated into 2 physical files, SQL server would use 2 threads to read it, which potentially could be faster.

In Addition, if each file were on its own disk array or physical disk, the performance gain would even be greater.

Regards,

Tarek Ghazali

SQL Server MVP

Web Site: http://www.sqlmvp.com

|||

If you've only got one physical disk, multiple IO threads will slow things down as you'll be forcing far less efficient random IO instead of a single sequential read which is much faster.

If IO's your bottleneck, upgrade the IO subsystem!

|||

Thanks Tarek and BigE.

Now I am in kind of a dilemma. Do multiple IO threads for one physical file is better or worse. Any way, I think that IO is not my biggest problem, but I try to make things better and elegant, and having one hugr file of about 20GB seem a bit inelegant.

|||Don't worry about SQL Server finding the positions that it wants within a large file: Infact, the structure of the SQL Server files is much more suited to finding data quickly than the structure of the NTFS file system!|||

Hi Tarek,

Is it possible to have one table data in 2 different files? I did know that.

|||

Hi Roman,

Table partitioning is a powerful new feature in SQL Server 2005, You can implement a horizontal data partition where you can split your data (records) in a specific table in different filegroups - datafiles, for more details check the following URL:
http://msdn2.microsoft.com/en-us/library/ms345146.aspx

Regards

Tarek Ghazali

SQL Server MVP

Web Site: http://www.sqlmvp.com

Multiple applications on 1 database - Namespacing ?

Hello,
I was wondering what would be the best practice to integrate multiple
application-specific objects into one database.
The most obvious way would be to prefix the object names for example
App1_tblUsers and App2_tblUsers.
Is there a better way to ultimately come to some kind of namespacing system
for the database ?
I've read about using the owner object, but since it's an existing and live
database, I'm reluctant to get my feet too wet into something, before I've
looked into other slick ways.
BerenBeren wrote:
> Hello,
> I was wondering what would be the best practice to integrate multiple
> application-specific objects into one database.
> The most obvious way would be to prefix the object names for example
> App1_tblUsers and App2_tblUsers.
> Is there a better way to ultimately come to some kind of namespacing
> system for the database ?
> I've read about using the owner object, but since it's an existing
> and live database, I'm reluctant to get my feet too wet into
> something, before I've looked into other slick ways.
> Beren
Sounds like you really need three databases if there is no or little
overlap between them. Any reason that's not an option. The tables should
not be "application" entities, but database ones. So, if you need to
prefix them with something, you'll probably want to give them a logical
database name prefix, not an application one.
Or maybe you can add an ApplicationID as a FK column to the User table
and create an Application table that defines all applications.
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:Ob5LUIiDFHA.1628@.TK2MSFTNGP15.phx.gbl...
> Beren wrote:
> Sounds like you really need three databases if there is no or little
> overlap between them. Any reason that's not an option. The tables should
> not be "application" entities, but database ones. So, if you need to
> prefix them with something, you'll probably want to give them a logical
> database name prefix, not an application one.
> Or maybe you can add an ApplicationID as a FK column to the User table and
> create an Application table that defines all applications.
> --
> David Gugick
> Imceda Software
> www.imceda.com
The problem is unfortunately your first statement. The existing tables have
almost no similarities to the new ones, and I'm explicitly told to use this
existing db for the second application too.
The only similarity would be the names I'm using (both apps have a tblUser
table etc); the problem.
The real downer though is that the second application already has its
specific tables and procedures nearly finished (on a test sql server), so
instead of renaming every table / view/ proc, from this second db, I'd need
a way to group my objects; so that - after merging the the test db with the
live db- the server recognizes the merged db as having unique names
nonetheless.|||Beren wrote:
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:Ob5LUIiDFHA.1628@.TK2MSFTNGP15.phx.gbl...
> The problem is unfortunately your first statement. The existing
> tables have almost no similarities to the new ones, and I'm
> explicitly told to use this existing db for the second application
> too. The only similarity would be the names I'm using (both apps have
> a
> tblUser table etc); the problem.
> The real downer though is that the second application already has its
> specific tables and procedures nearly finished (on a test sql
> server), so instead of renaming every table / view/ proc, from this
> second db, I'd need a way to group my objects; so that - after
> merging the the test db with the live db- the server recognizes the
> merged db as having unique names nonetheless.
You either have to rename or use a new database. Renaming will likely
have bigger implications than using a new database because of all the
table relationships and procedures involved.
Why not recommend a new database, since it's clearly the way to go.
David Gugick
Imceda Software
www.imceda.com