Prev: Dateformat
Next: Reading .LDF
From: Manuel on 31 Jul 2006 05:23

That's exactly it. The first part (for any ProjectID).
And I understand it ;-)

Thanks for everything.


--
Manuel


"Steve Kass" wrote:

> Manuel,
>
> It's not really an Excel issue at this point, but I think what you want is
>
> SELECT *
> FROM OPENROWSET(... <same as before> )
> ) AS E
> WHERE EXISTS (
> SELECT * FROM dbo.ProjectItems_tbl AS T
> WHERE T.Length= E.Length
> AND T.Height = E.Height
> AND T.Width = E.Width
> )
>
> In other words, find the items in the spreadsheet that look like
> they are already in the table. (As written, for any projectID
> value, but if you want to check if they are in the table only
> for a specific projectID value (3 for this example):
>
> SELECT *
> FROM OPENROWSET(... <same as before> )
> ) AS E
> WHERE EXISTS (
> SELECT * FROM dbo.ProjectItems_tbl AS T
> WHERE T.Length= E.Length
> AND T.Height = E.Height
> AND T.Width = E.Width
> )
> AND ProjectID = 3
>
>
> I left out checking for a match on ItemID also, since it is
> an auto number, and could be different in the spreadsheet
> from the number it is in the table, but if that discripancy can't
> happen, then you would include the additional condition in
> the where clause.
>
> SK
>
> Manuel wrote:
>
> >Let me try to be more specific and at the same time it might help explain a
> >bit of what is behind all this.
> >This is a company (my daughter´s) that moves objects of art (the items)
> >within Europe. The project is the job of moving for example a collection of
> >art from Museum A in Madrid to Museum B in Lisbon.
> >
> >project_tbl (some colums)
> >
> >projectID(autoNumber) projectCityOfOrigin projectCityOfDestination
> >
> >1 Madrid Lisbon
> >2 Paris Madrid
> >
> >Then I have projectItems_tbl
> >
> >projectItemID(autoNumber) projectID Length Height Width
> >
> >1 1 2.5
> >3.5 6
> >2 1 3.2
> >4.5 7
> >3 1 3.5
> >4.2 8
> >
> >The customers have a supplied Excel template with the exact same column
> >names as projectItems_tbl, without obviously "projectoItemID", and
> >"projectID" that they leave blank as they don´t know what will it be, that
> >they fill with the description of the items to be moved and send it by E-mail.
> >Once received, the new job is created. Knowing the new "projectID", whoever
> >receives the Spreadsheet fills all rows with that specific "projectID" and
> >saves it always with the same name, the one that the query refers to.
> >Ideally would be to detect any row in the Spreadsheet with the same values
> >as in "projectItems_tbl". For example 1, 3.5, 4.2, 8.
> >That item, wich is "projectItemID=3" already exists belonging to projectID=1
> >and with the same values.
> >
> >I really hope you undestand what I´m trying to achieve.
> >
> >Thank you very much
> >
> >
> >
> >
> >
>
First  |  Prev  | 
Pages: 1 2 3 4 5
Prev: Dateformat
Next: Reading .LDF