Posts by jaypatel

Re: If Statement

Okay for Doc:

Example being:

10001, 1
10001, 2
10002, 1
10002, 2
10002, 3

Now the results should be:
10001, 2
10002, 3

as for 10001, the highest in column b is 2
as for 10002, the highest in column b is 3

I hope this explains everything....

as for non vba solution, i need to do this for 50 seperate reports with thousands of lines......

So i would appreciate a vba script.

Regards

Jay

Hi,

I have 2 columns A and B of course.....

I need a script that would do the following:

If a3=a2 and b3=b2 then delete row
if a3=a2 and b3>b2 then delete row b2
if a3>a2 then keep row a2..... in a kind of loop effect.

Any help would be appreciated.

Regards

Jay

Re: Inserting Data Into New Table

Sorted.....

the code should be:

select skilltext, passportid,skilllevelid, completedat, active, pctcompleted
from passport2

needed to take out the char....

Jay

Hi,

below is my code:

SELECT
    tblSkillText."SkillText",
    tblSkillLevelValue."PassportID", tblSkillLevelValue."SkillLevelID", tblSkillLevelValue."CompletedAt", tblSkillLevelValue."Active", tblSkillLevelValue."PctCompleted"
FROM
    { oj (("Passports2"."dbo"."tblSkillLevelText" tblSkillLevelText INNER JOIN "Passports2"."dbo"."tblSkillText" tblSkillText ON
        tblSkillLevelText."LanguageID" = tblSkillText."LanguageID")
     INNER JOIN "Passports2"."dbo"."tblSkillLevel" tblSkillLevel ON
        tblSkillLevelText."SkillLevelID" = tblSkillLevel."SkillLevelID")
     INNER JOIN "Passports2"."dbo"."tblSkillLevelValue" tblSkillLevelValue ON
        tblSkillLevel."SkillLevelID" = tblSkillLevelValue."SkillLevelID"}
where
      tblSkillLevelValue."Active"=1 and tblSkillLevelValue."PctCompleted"=100 and tblSkillText."SkillText"='Operate a computer'

but the problem i have is that i need to trim the date field ie fourth column, "completed at".....

i have tried to use the code to obtain the first 11 characters:

select SUBSTRING (tblSkillLevelValue."CompletedAt",1,11)

but i do not know how to fit it into the main part of the query.....

Any assistance will be greatly appreciated.

kind regards

Jay

Re: Inserting Data Into New Table

Hi,

I think the problem lies with mentioning the schema......
if my orginal code is:

create view passport2
as 
SELECT
    tblSkillText."SkillText",
    tblSkillLevelValue."PassportID", tblSkillLevelValue."SkillLevelID", tblSkillLevelValue."CompletedAt", tblSkillLevelValue."Active", tblSkillLevelValue."PctCompleted"
FROM
    { oj (("Passports2"."dbo"."tblSkillLevelText" tblSkillLevelText INNER JOIN "Passports2"."dbo"."tblSkillText" tblSkillText ON
        tblSkillLevelText."LanguageID" = tblSkillText."LanguageID")
     INNER JOIN "Passports2"."dbo"."tblSkillLevel" tblSkillLevel ON
        tblSkillLevelText."SkillLevelID" = tblSkillLevel."SkillLevelID")
     INNER JOIN "Passports2"."dbo"."tblSkillLevelValue" tblSkillLevelValue ON
        tblSkillLevel."SkillLevelID" = tblSkillLevelValue."SkillLevelID"}
where
      tblSkillLevelValue."Active"=1 and tblSkillLevelValue."PctCompleted"=100 and tblSkillText."SkillText"='Operate a computer'

and my create table code is:

create table newpassport2
     (skilltext char(50), passportid char(10), skilllevelid char(10), dateid char(11), active char(10), pctcompleted char(10))

Everything above, i am happy with.....

the crux is inserting data from passport2 to newpassport2 and the code i used was:

Insert into newpassport2 (skilltext char(50), passportid char(10), skilllevelid char(10), dateid char(11), active char(10), pctcompleted char(10))
SELECT
    skilltext, passportID, SkillLevelID, CompletedAt, Active, PctCompleted
FROM
    Passport2

and i think the problem is the end part where i have not properly defined the schema? Does this make a difference?

Again, any help will be appreciated.

Regards

Jay

hi,

1.I have created a view via this method

create view passport2

2. Then i created a new table called newpassport2 via:

create table newpassport2
     (skilltext char(50), passportid char(10), skilllevelid char(10), dateid char(11), active char(10), pctcompleted char(10))

And the above is fine....

but when i try to insert into the new table, newpassport2 via passport2, using:

Insert into newpassport2 (skilltext char(50), passportid char(10), skilllevelid char(10), dateid char(11), active char(10), pctcompleted char(10))
SELECT
    skilltext, passportID, SkillLevelID, CompletedAt, Active, PctCompleted
FROM
    Passport2

it comes up with an error.......

Can someone help please.

Cheers

Jay

Re: Sql And Date Trimming

Hi,

It has been....

i created a new table and inserted into the table via:
(date char(10)).

Thanks

Jay

Hi,

The have the SQL:

SELECT
    tblSkillLevelValue."PassportID", tblSkillLevelValue."CompletedAt", tblSkillLevelValue."Active", tblSkillLevelValue."PctCompleted"
FROM
    "Passports2"."dbo"."tblSkillLevelValue" tblSkillLevelValue

and the date format comes up with "2003-01-01 16:57:21.750"!!!
i have tried to trim the date with no result...... can someone help me to try to trim it to the first 10 characters.... eg "2003-01-01".

Thanks in advance,

Jay

Hi,

I have just started SQL, and would appreciate some help..... i have the following code:

SELECT
    tblSkillText."SkillText",
    tblSkillLevelValue."PassportID", tblSkillLevelValue."SkillLevelID", tblSkillLevelValue."CompletedAt", tblSkillLevelValue."Active", tblSkillLevelValue."PctCompleted"
FROM
    { oj (("Passports2"."dbo"."tblSkillLevelText" tblSkillLevelText INNER JOIN "Passports2"."dbo"."tblSkillText" tblSkillText ON
        tblSkillLevelText."LanguageID" = tblSkillText."LanguageID")
     INNER JOIN "Passports2"."dbo"."tblSkillLevel" tblSkillLevel ON
        tblSkillLevelText."SkillLevelID" = tblSkillLevel."SkillLevelID")
     INNER JOIN "Passports2"."dbo"."tblSkillLevelValue" tblSkillLevelValue ON
        tblSkillLevel."SkillLevelID" = tblSkillLevelValue."SkillLevelID"}    
WHERE
    tblSkillText."SkillText" ='operate a computer' and tblSkillLevelValue."SkillLevelID" <6

It has churned out over 100,000 rows. The question or help i need is the variable: tblSkillLevelValue."PassportID" where it is like a membership number, and is repeated.... how can i get this variable to result in just one line where tblSkillLevelValue."CompletedAt" (it is in time format) to be used at the latest set:

eg:

passport id, date
23315 21/09/06
23315 22/09/06
23315 26/09/06

So i need the result to choose only 26/09/06 and eliminate the other 2. Is this possible?

Thanks

Jay

Hi,

I have the following code and using excel 2003:

Sub test() 
Sheets("Sheet2").Visible = True 
Application.Goto Reference:="month" 
ActiveWorkbook.Names("month").Delete 
Call macro3 
End Sub 




Sub macro3() 
Application.SendKeys "{DOWN}", True 
ActiveWorkbook.Names.Add Name:="month", RefersTo:=ActiveCell 
End Sub

But it does not seem to work.
The routine is:

make sheet2 visble;
goto "month"
delete "month"
go down 1 cell
range name cell "month"

Any help will be greatly appreciated.

Jay

Hi,

I am using Excel 2003.

how would you (via code) delete a named ranged which is on a certain cell, and move down one cell and rename that cell.

For example, c14 could be "house". I would like c15 to be "house" instead.

Regards

Hi,

I am using Excel 2003.

I have some data in column E, and the penultimate cell in column E is the cell i am after, as i wish to range name this "Automation".

You might think that it is easy, but when you create new data, "Automation" is not the penultimate cell.

So if someone can help with a macro that can range name the penultimate cell, i would be extremely grateful.

Cheers

Jay

Hi,

In my column L of my spreadsheet, i have a list of alphanumeric names.....

what would be the code to do the following:

not only delete the duplicates, but also to delete the one it has duplicated.

so if there is only one "A0039"in cell L9, then keep it,

but if there is 2 or more of "A0040" ie in cell L14, and L15, then delete BOTH rows 14 and 15.

Cheers

Jay

Hi,

Say if you want to "create" your own menu between "Windows" and "Help" called "Macro", with a submenu called "accounts"....how would you create this?

Cheers

Jay

Hi,

I need a macro that will close all the workbooks except the one that is "activated". I know the macro

workbooks.close

but it closes all the workbooks.

Cheers

Jay

in reply to John's email.....can you give an example please.....

Jay

Hi,

I have a set of data called "dataset" and a have a couple of other files that is activated or open, examples being "test.xls", "account.xls".

In the spreadsheet "dataset", there is 2 columns of interest.... column a which relates to the other "opened" spreadsheets.... so in our example cell a2 is "test" (ie test.xls) and a3 contains "account" (ie account.xls).

in column f i have the corresponding email addresses...........
so in row 2 i have

test x x x x [email protected] (where x is data for columns b through to e so, [email protected] is cell f2 and test is cell a2).

The question I have is, how do you sendmail with the corresponding spreadsheet.... ie the test.xls spreadsheet should be sent to [email protected],
and the account.xls spreadsheet should be sent to [email protected].

If you want me to email the spreadsheets I can.

Jay

I understand now why Excel is rejecting it.

In reply to Derk and Dave's solutions.....

say if i copied the contents of rows d1-d65536 to column e.......

what would be the code to then say....

let contents of col d be ranged name to col e....

so if d2 contents = "account1", then e2 should be ranged named "account1" ie offset by one column.

Jay

maybe i am doing something wrong.....
but it keeps on saying "runtime error 1004 that name is not valid".....

Any other suggestions.....

Jay

Hi,

I have just tested the second macro, and it says that "c.Name = c.Value" name is not valid.

The reason why i need it like this is that i need to transfer the data transferring across which changes each month..... so a range name seems the logical idea.

Jay