Mark duplicate records in a Select statement ?

I have a requirement to mark duplicate records when I pull them from the database.

However, I only want to mark the 2nd, 3rd, 4th etc record - not the first one.

The code I have below creates a column called Dupes but marks all the duplicates - including the first one.

Is there a way to only mark the 2nd, 3rd, 4th etc record ?

SELECT *, cs.CallStatusDescription as CSRStatusDesc, cs2.CallStatusDescription as CustomerStatusDesc, (Select MAX(CallAttemptNumber)From CallResults cr Where cl.Id = cr.CallLogId) as CallAttemptNumber,

Dupes = (select count(id)
from CallLogs
where (CustomerHomePhone != '' AND cl.CustomerHomePhone = CustomerHomePhone)
OR (CustomerBusinessPhone != '' AND cl.CustomerBusinessPhone = CustomerBusinessPhone)
AND DealerId= 'hdsh'
AND CSRStatus IS NULL
and datediff(d, logdate, getdate()) <= 21),

FROM CallLogs cl
left Join CallStatus cs on cs.Id = cl.CSRstatus
left Join CallStatus cs2 on cs2.Id = cl.Customerstatus
Where SaleStage IN ('1', '2', '3', '4', '5', '6') And (LogProcessFlag = 1 Or LogProcessFlag = 0)
And DealerId='hdsh'
And Logdate Between '08/01/2007' And '08/31/2007'

Can't you just decrement the 'count' statement by 1 before assigning it to Dupes?

1Dupes = (SELECTcount(id)
2FROM CallLogs
3WHERE(CustomerHomePhone !=''AND cl.CustomerHomePhone = CustomerHomePhone)
4 OR (CustomerBusinessPhone !=''AND cl.CustomerBusinessPhone = CustomerBusinessPhone)
5AND DealerId='hdsh'
6 AND CSRStatusISNULL
7 ANDdatediff(d, logdate,getdate()) <= 21))- 1

That way it just accounts for itself by subtracting 1 from the total 

|||

The problem is that both records get the same Dupe number.

I need the 2nd "dupe" to be marked as 2, the 3rd "dupe" to be marked as 3 etc

Many Detail Record to One

I have records in a table that have a format like the following
Id / Code / Amount
ex.
1 / PDM / 50.00
1 / BIN / 75.00
1 / REN / 30.00
The records have the same id but different codes - the codes will never be
anything different than what is listed - so I could add a where clause for
each code.
I would like to select the three records and combine them into one. So that
it looks like the following:
ID / PDMAMT / BINAMT / RENAMT
1 / 50.00 / 75.00 / 30.00
I have looked at subqueries and the exists operator but I can't seem to find
what I am looking for.
Thanks for the help
http://www.aspfaq.com/2462
http://www.aspfaq.com/
(Reverse address to reply.)
"Heather" <Heather@.discussions.microsoft.com> wrote in message
news:EA6225CF-BF9C-4DB5-AB3C-1D31104E12E7@.microsoft.com...
> I have records in a table that have a format like the following
> Id / Code / Amount
> ex.
> 1 / PDM / 50.00
> 1 / BIN / 75.00
> 1 / REN / 30.00
> The records have the same id but different codes - the codes will never be
> anything different than what is listed - so I could add a where clause for
> each code.
> I would like to select the three records and combine them into one. So
that
> it looks like the following:
> ID / PDMAMT / BINAMT / RENAMT
> 1 / 50.00 / 75.00 / 30.00
> I have looked at subqueries and the exists operator but I can't seem to
find
> what I am looking for.
> Thanks for the help
|||Heather,
This is called a cross-tab (a.k.a. pivot), and in your case you could do it
this way:
SELECT id,
SUM(CASE WHEN Code = 'PDM' THEN Amount ELSE 0 END) AS PDMAMT,
SUM(CASE WHEN Code = 'BIN' THEN Amount ELSE 0 END) AS BINAMT,
SUM(CASE WHEN Code = 'REN' THEN Amount ELSE 0 END) AS RENAMT
FROM YourTable
GROUP BY id
"Heather" <Heather@.discussions.microsoft.com> wrote in message
news:EA6225CF-BF9C-4DB5-AB3C-1D31104E12E7@.microsoft.com...
> I have records in a table that have a format like the following
> Id / Code / Amount
> ex.
> 1 / PDM / 50.00
> 1 / BIN / 75.00
> 1 / REN / 30.00
> The records have the same id but different codes - the codes will never be
> anything different than what is listed - so I could add a where clause for
> each code.
> I would like to select the three records and combine them into one. So
that
> it looks like the following:
> ID / PDMAMT / BINAMT / RENAMT
> 1 / 50.00 / 75.00 / 30.00
> I have looked at subqueries and the exists operator but I can't seem to
find
> what I am looking for.
> Thanks for the help

Many Detail Record to One

I have records in a table that have a format like the following
Id / Code / Amount
ex.
1 / PDM / 50.00
1 / BIN / 75.00
1 / REN / 30.00
The records have the same id but different codes - the codes will never be
anything different than what is listed - so I could add a where clause for
each code.
I would like to select the three records and combine them into one. So that
it looks like the following:
ID / PDMAMT / BINAMT / RENAMT
1 / 50.00 / 75.00 / 30.00
I have looked at subqueries and the exists operator but I can't seem to find
what I am looking for.
Thanks for the helphttp://www.aspfaq.com/2462
http://www.aspfaq.com/
(Reverse address to reply.)
"Heather" <Heather@.discussions.microsoft.com> wrote in message
news:EA6225CF-BF9C-4DB5-AB3C-1D31104E12E7@.microsoft.com...
> I have records in a table that have a format like the following
> Id / Code / Amount
> ex.
> 1 / PDM / 50.00
> 1 / BIN / 75.00
> 1 / REN / 30.00
> The records have the same id but different codes - the codes will never be
> anything different than what is listed - so I could add a where clause for
> each code.
> I would like to select the three records and combine them into one. So
that
> it looks like the following:
> ID / PDMAMT / BINAMT / RENAMT
> 1 / 50.00 / 75.00 / 30.00
> I have looked at subqueries and the exists operator but I can't seem to
find
> what I am looking for.
> Thanks for the help|||Heather,
This is called a cross-tab (a.k.a. pivot), and in your case you could do it
this way:
SELECT id,
SUM(CASE WHEN Code = 'PDM' THEN Amount ELSE 0 END) AS PDMAMT,
SUM(CASE WHEN Code = 'BIN' THEN Amount ELSE 0 END) AS BINAMT,
SUM(CASE WHEN Code = 'REN' THEN Amount ELSE 0 END) AS RENAMT
FROM YourTable
GROUP BY id
"Heather" <Heather@.discussions.microsoft.com> wrote in message
news:EA6225CF-BF9C-4DB5-AB3C-1D31104E12E7@.microsoft.com...
> I have records in a table that have a format like the following
> Id / Code / Amount
> ex.
> 1 / PDM / 50.00
> 1 / BIN / 75.00
> 1 / REN / 30.00
> The records have the same id but different codes - the codes will never be
> anything different than what is listed - so I could add a where clause for
> each code.
> I would like to select the three records and combine them into one. So
that
> it looks like the following:
> ID / PDMAMT / BINAMT / RENAMT
> 1 / 50.00 / 75.00 / 30.00
> I have looked at subqueries and the exists operator but I can't seem to
find
> what I am looking for.
> Thanks for the help

Manually Insert a Primary Key Value

I have a colleague who mysteriously lost his record in our Employee table.
The "employee ID" field serves as the primary key on the table.
How do I manually insert his record, including the old primary key value,
back into the table? That is, how do I bypass the primary-key constraint?
Thanks in advance,
Mark HolahanWhat is the definition of the table?
AMB
"Mark Holahan" wrote:

> I have a colleague who mysteriously lost his record in our Employee table.
> The "employee ID" field serves as the primary key on the table.
> How do I manually insert his record, including the old primary key value,
> back into the table? That is, how do I bypass the primary-key constraint?
> Thanks in advance,
> Mark Holahan
>
>|||Is this an identity field? if so use:
SET IDENTITY_INSERT ON
--execute insert statement here
SET IDENTITY_INSERT OFF|||You can't "bypass" a primary key constraint unless you drop it. I
assume you are actually referring to the IDENTITY property on this
column. The IDENTITY property is quite distinct from a PRIMARY KEY
constraint. If you want to insert an explicit IDENTITY value then use
the SET IDENTITY_INSERT table_name ON option.
Why does it matter to you if the row gets inserted with a different
IDENTITY value to the one it originally had? It shouldn't have been
possible for the accidental delete to cause "orphan" rows in a
referencing table - That's assuming you have correctly declared foreign
key constraints against the employee ID column. If you don't have
foreign keys then that's something you really ought to fix.
David Portas
SQL Server MVP
--|||AMB,
The table definition follows:
CREATE TABLE [dbo].[Employee] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[FName] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MI] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LName] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BranchId] [int] NULL ,
[SalesRepId] [int] NULL ,
[Email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NetworkId] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UserName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Password] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Deactivated] [datetime] NULL ,
[ResetPW] [bit] NOT NULL ,
[Tries] [tinyint] NULL ,
[LastLoginDtm] [datetime] NULL ,
[PendingInfoUpdate] [bit] NOT NULL ,
[IsSalesRep] [bit] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Employee] WITH NOCHECK ADD
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[Employee] ADD
CONSTRAINT [DF_Employee_ResetPW] DEFAULT (0) FOR [ResetPW],
CONSTRAINT [DF_Employee_PendingInfoUpdate] DEFAULT (0) FOR
[PendingInfoUpdate],
CONSTRAINT [DF_Employee_IsSalesRep] DEFAULT (0) FOR [IsSalesRep]
GO
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:3E956B3B-CF85-4FBA-B885-41BE4C9A96FD@.microsoft.com...
> What is the definition of the table?
>
> AMB
> "Mark Holahan" wrote:
>|||Read David's post.
AMB
"Mark Holahan" wrote:

> AMB,
> The table definition follows:
> CREATE TABLE [dbo].[Employee] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [FName] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MI] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [LName] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [BranchId] [int] NULL ,
> [SalesRepId] [int] NULL ,
> [Email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [NetworkId] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [UserName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Password] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Deactivated] [datetime] NULL ,
> [ResetPW] [bit] NOT NULL ,
> [Tries] [tinyint] NULL ,
> [LastLoginDtm] [datetime] NULL ,
> [PendingInfoUpdate] [bit] NOT NULL ,
> [IsSalesRep] [bit] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Employee] WITH NOCHECK ADD
> CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
> (
> [id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Employee] ADD
> CONSTRAINT [DF_Employee_ResetPW] DEFAULT (0) FOR [ResetPW],
> CONSTRAINT [DF_Employee_PendingInfoUpdate] DEFAULT (0) FOR
> [PendingInfoUpdate],
> CONSTRAINT [DF_Employee_IsSalesRep] DEFAULT (0) FOR [IsSalesRep]
> GO
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:3E956B3B-CF85-4FBA-B885-41BE4C9A96FD@.microsoft.com...
>
>|||Distinction noted.
CIO of company claims RI puts unneeded burden on SQL Server. Therefore we
handle RI on the front end. I don't necessarily agree, especially when I
read in BOL that, "The query optimizer also uses constraint definitions to
build high-performance query execution plans." But I've never done the
homework to disprove his theory. So I abide.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1107450710.189817.206210@.g14g2000cwa.googlegroups.com...
> You can't "bypass" a primary key constraint unless you drop it. I
> assume you are actually referring to the IDENTITY property on this
> column. The IDENTITY property is quite distinct from a PRIMARY KEY
> constraint. If you want to insert an explicit IDENTITY value then use
> the SET IDENTITY_INSERT table_name ON option.
> Why does it matter to you if the row gets inserted with a different
> IDENTITY value to the one it originally had? It shouldn't have been
> possible for the accidental delete to cause "orphan" rows in a
> referencing table - That's assuming you have correctly declared foreign
> key constraints against the employee ID column. If you don't have
> foreign keys then that's something you really ought to fix.
> --
> David Portas
> SQL Server MVP
> --
>|||The CIO is wrong. If he wants to design databases he should take a course
first ;-)
Obviously handling RI on the front end isn't working otherwise you wouldn't
have this problem. No surprises there.
David Portas
SQL Server MVP
--

Manually change report

Hello,

I use Reporting Services in my solution. The data i have as source is very uncertain. Sometimes i manually have to delete a record from the report (not the database itself). I woluld like to have a checkbox or something simular to delete from report and totals, grafs would be updated. At the same time I would like the report to make a comment that a record was taken away, alternative let the user make a comment. Is this possible? DO i have to make a own application for that?

Thank you for your help!

Best regards,

Luskan

If you want my personal opinion, I don't think RS would be the best application for this.

Personally I would return the data from SQL to something like Excel using ADO/VBA or even MSQuery

and base your report / charts on the data then in Excel.

Deleting/changing the data will then have no effect on the data in your SQL database and if

constructed correcly, your charts/reports in excel will be updated automatically...

|||

Well, thank you for your opinion!

But i think I got a Solution. Besides exporting to Excel you can use cascading report parameters in order to to achive the same thing. The only problem i have know is that "Value field" and "label field" does not work as it is inteded to do.

Best regards,

Luskan

Manipulate the 'deleted record' flag on DBF files

Hi,

I am using OLE DB provider for Foxpro (VFPOLEDB.1) to query DBF files. I need to migrate the content of these files to a SQL Server 2005 database.

These DBF files have some (actually a lot) records marked as deleted using the DBF 'deleted' flag. When I submit a SELECT command to the OLE DB Provider, it returns me all the non-deleted records from the file.

It is very Ok as long as the 'deleted' rows actually have no more business value, but in my case, I need to do some processing on them, and even to migrate their data.

What are the options available for me to be able to query and differentiate the 'deleted' records ?

Thank you in advance,

Bertrand Larsy

Hi Bertrand,

Here's some example VB code to work with the deleted status of a row:

Try

Dim cn1 As New OleDbConnection( _
"Provider=VFPOLEDB.1;Data Source=C:\Temp\;")
cn1.Open()
'-- Make some VFP data to play with
Dim cmd1 As New OleDbCommand( _
"Create Table TestDBF (Field1 I, Field2 C(10))", cn1)
Dim cmd2 As New OleDbCommand( _
"Insert Into TestDBF Values (1, 'Hello')", cn1)
Dim cmd3 As New OleDbCommand( _
"Insert Into TestDBF Values (2, 'World')", cn1)
Dim cmd4 As New OleDbCommand( _
"Delete From TestDBF Where Field1 = 1", cn1)
cmd1.ExecuteNonQuery()
cmd2.ExecuteNonQuery()
cmd3.ExecuteNonQuery()
cmd4.ExecuteNonQuery()
cn1.Close()

Dim cn2 As New OleDbConnection( _
"Provider=VFPOLEDB.1;Data Source=C:\Temp\;")
cn2.Open()

Dim cmd5 As New OleDbCommand( _
"Select * From TestDBF", cn2)
Dim da1 As New OleDbDataAdapter(cmd5)
Dim ds1 As New DataSet
Dim dr1 As DataRow
da1.Fill(ds1)
For Each dr1 In ds1.Tables(0).Rows
Console.WriteLine( _
dr1.Item(0).ToString() & ", " & dr1.Item(1).ToString)
Next
Console.ReadLine()
cn2.Close()

Dim cn3 As New OleDbConnection( _
"Provider=VFPOLEDB.1;Data Source=C:\Temp\;")
cn3.Open()

Dim cmd6 As New OleDbCommand( _
"Set Deleted Off", cn3)
cmd6.ExecuteNonQuery()
Dim cmd7 As New OleDbCommand( _
"Select Deleted('TestDBF') As IsDeleted, TestDBF.* From TestDBF", cn3)
Dim da2 As New OleDbDataAdapter(cmd7)
Dim ds2 As New DataSet
Dim dr2 As DataRow
da2.Fill(ds2)
For Each dr2 In ds2.Tables(0).Rows
Console.WriteLine( _
dr2.Item(0).ToString() & ", " & dr2.Item(1).ToString() & ", " & dr2.Item(2).ToString())
Next
Console.ReadLine()
cn2.Close()

Catch e As Exception
MsgBox(e.ToString())
End Try

|||

Thank you,

This is indeed a workable solution.

Now, is there a way to perform the same work in a T-SQL script ?

I tried, but could not manage to "chain" successfully a 'SET DELETED OFF' statement and a SELECT with OPENQUERY or OPENDATASOURCE:

SELECT [name]

FROM OPENQUERY(linkedServer,'SELECT name FROM Members')

This returns all records, but not the deleted ones

SELECT [name]

FROM OPENQUERY(linkedServer,'SET Deleted OFF;

SELECT name FROM Members')

This produces the error '

Msg 7357, Level 16, State 2, Line 1

Cannot process the object "SET Deleted OFF;

SELECT name FROM Members". The OLE DB provider "VFPOLEDB" for linked server "linkedServer" indicates that either the object has no columns or the current user does not have permissions on that object.

'

When doing it with an EXECUTE statement as pass-through query on a linked server (using OLE DB provider for FoxPro, of course), it says it successfully executes, but does not return the result of the select query:

DECLARE @.Query varchar(max)

SET @.Query='SELECT name FROM Members'

EXECUTE (@.Query) AT linkedServer

This returns all records, but not the deleted ones

DECLARE @.Query varchar(max)

SET @.Query='SET Deleted OFF' + CHAR(13) + 'SELECT name FROM Members'

EXECUTE (@.Query) AT linkedServer

This produces the query output 'Command(s) completed successfully.', but does not return any result.

Kind regards,

Bertrand Larsy

|||

Hi Bertrand,

I don't have time to try it now but it might work with a semicolon: "Set Deleted Off;Select * From MyTable..."

|||

Neither of the 2 following samples give any result:

DECLARE @.Query varchar(max)

SET @.Query='SET Deleted OFF;SELECT name FROM Members'

EXECUTE (@.Query) AT linkedServer

DECLARE @.Query varchar(max)

SET @.Query='SET Deleted OFF;' + CHAR(13) + 'SELECT name FROM Members'

EXECUTE (@.Query) AT linkedServer

Anyway, thank you for your answers, I will make a CLR stored procedure of your first reply.

Kind regards,

Bertrand Larsy

Manipulate Page-Numbers

Hi,

I need to manipulate the page-numbers, I've a dataset with for example 8 Records, where each record has its own page (page break at end) but some Records have 2Pages. So I want that every "first" page of a record gets page-number 1 and for those reports that need two pages the next page should have page-number 2. So in a PDF the page numbers look be like this: 1,1,1,2,1,1,2,3,1,1,1,2...

I tried a custom assembly with a static variable m_page on it which is resettet to 1 in a textfield at the recordbegin (=MyLib.MyClass.resetPage()) and is shown and incrementet in each page footer (=MyLib.MyClass.nextPage()). But when I print that to PDF it seems that the page footers are alltogether generated at the end, so I get numbers from for example 20 to 30 (my report has 10pages).

Is there any possibility? In Access this was quite easy ;(

Sorry bothering you, I should have used the search with the right keywords ;)

The solution is:

http://blogs.msdn.com/bwelcker/archive/2005/05/19/420046.aspx

Managing record groupings

Hi group,
I cannot figure out the best way to manage some data I have to put into a
database without doing something il-advised. Imagine this scenario:
Table: Animals
AnimalID AnimalType
1 Cat
2 Dog
3 Ferret
4 Iguana
5 Orangutan
6 Nurse Shark
7 Binturong
8 King Snake
9 Moth
10 Crawfish
11 Pelican
12 Man
13 Porpoise
14 Ermine
15 Seahorse
Fine, so there's a long list of animals, say a few hundred, and what an end
user needs to be able to do is select any number of animals and assign them
to carriers for transportation. The end user may select that one animal
type is alone, or one animal will travel with anywhere from 1 to
count(animalid)-1 animals. So, I can't do anything like:
AnimalID AnimalType TravelsWith01 TravelsWith02 TravelsWith03
That would be silly. But I can't figure out how to make a table that can
manage an unlimited number of AnimalIDs that would indicate that they are
related in some way (will travel together). There won't necessarily be a
fixed number of traveling containers either. Like, I can't do:
TravelContainerID Animal1, Animal2, Animal3
That would also leave me with a bunch of Animal columns that wouldn't make
sense to have exist. The way I'm thinking about it now is:
MatchID AnimalID
1 1
1 6
1 9
2 3
2 11
3 12
4 7
4 15
4 10
4 5
4 13
And so on. So, that would mean that animals 1, 6, and 9 travel together,
and so on. But something about that doesn't seem right either. Can anyone
offer advice for my design please?
Thank you,
Ray at workRay,
I think your end solution is fine. You have basically
created a join table that contains yours "Transit ID"
associated with the animals in that "Transit ID". You
could now add a shipping ID to track how the animals were
sent and still be able to tell what group they were in.
Hope that helps.
Derek
>--Original Message--
>Hi group,
>I cannot figure out the best way to manage some data I
have to put into a
>database without doing something il-advised. Imagine
this scenario:
>
>Table: Animals
>AnimalID AnimalType
>1 Cat
>2 Dog
>3 Ferret
>4 Iguana
>5 Orangutan
>6 Nurse Shark
>7 Binturong
>8 King Snake
>9 Moth
>10 Crawfish
>11 Pelican
>12 Man
>13 Porpoise
>14 Ermine
>15 Seahorse
>Fine, so there's a long list of animals, say a few
hundred, and what an end
>user needs to be able to do is select any number of
animals and assign them
>to carriers for transportation. The end user may select
that one animal
>type is alone, or one animal will travel with anywhere
from 1 to
>count(animalid)-1 animals. So, I can't do anything like:
>AnimalID AnimalType TravelsWith01 TravelsWith02
TravelsWith03
>That would be silly. But I can't figure out how to make
a table that can
>manage an unlimited number of AnimalIDs that would
indicate that they are
>related in some way (will travel together). There won't
necessarily be a
>fixed number of traveling containers either. Like, I
can't do:
>TravelContainerID Animal1, Animal2, Animal3
>That would also leave me with a bunch of Animal columns
that wouldn't make
>sense to have exist. The way I'm thinking about it now
is:
>
>MatchID AnimalID
>1 1
>1 6
>1 9
>2 3
>2 11
>3 12
>4 7
>4 15
>4 10
>4 5
>4 13
>And so on. So, that would mean that animals 1, 6, and 9
travel together,
>and so on. But something about that doesn't seem right
either. Can anyone
>offer advice for my design please?
>Thank you,
>Ray at work
>
>.
>|||Thank you Derek.
Ray at work
"Derek Wilson" <derek_wilson@.rmic.com> wrote in message
news:016f01c34006$3d6dbfd0$a501280a@.phx.gbl...
> Ray,
> I think your end solution is fine. You have basically
> created a join table that contains yours "Transit ID"
> associated with the animals in that "Transit ID". You
> could now add a shipping ID to track how the animals were
> sent and still be able to tell what group they were in.
> Hope that helps.
> Derek
> >--Original Message--
> >Hi group,
> >
> >AnimalID AnimalType TravelsWith01 TravelsWith02
> TravelsWith03
> >
> >That would be silly. But I can't figure out how to make
> a table that can
> >manage an unlimited number of AnimalIDs that would
> indicate that they are
> >related in some way (will travel together). There won't
> necessarily be a
> >fixed number of traveling containers either. Like, I
> can't do:
> >
> >TravelContainerID Animal1, Animal2, Animal3
> >
> >That would also leave me with a bunch of Animal columns
> that wouldn't make
> >sense to have exist. The way I'm thinking about it now
> is:
> >
> >
> >MatchID AnimalID
> >1 1
> >1 6
> >1 9
> >2 3
> >2 11
> >3 12
> >4 7
> >4 15
> >4 10
> >4 5
> >4 13
> >
> >And so on. So, that would mean that animals 1, 6, and 9
> travel together,
> >and so on. But something about that doesn't seem right
> either. Can anyone
> >offer advice for my design please?
> >
> >Thank you,
> >
> >Ray at work
> >
> >
> >.
> >