2014-11-22

Dear All

I`m looking for help with creation of some advanced tables, with advanced sorting and with a lot off condition.

Problem is that database contain 60.000 records and i would like to create using SQL, problem is that i`m only operating on the graphical access, not with the source of code.

I have database with 2 tables

Model and First Day of Sale is in strange standard MM.YYYY

Model

Day of Sale

XX-ABC2-A

01.2010

XX-ABC2-D

02.2012

XX-ABC2-K

03.2013

XX-ABC2-I

04.2010

XX-ABC20-B

03.2010

XX-ABC200-C

02.2009

YY-ABD2-A

01.2009

YY-ABD2-B

02.2008

YY-ABD3

10.2012

ABDZZZZ-A

06.2014

YY-ABDZ-A

05.2014

XX-11A222

03.2014

I would like to add 3rd table called category and fill the field with some name for example Graphic-Card

Model

Day of Sale

Category

XX-ABC2-A

01.2010

Graphic-Card

XX-ABC2-D

02.2012

Graphic-Card

XX-ABC2-K

03.2013

Graphic-Card

XX-ABC2-I

04.2010

Graphic-Card

XX-ABC20-B

03.2010

Graphic-Card

XX-ABC200-C

02.2009

Graphic-Card

YY-ABD2-A

01.2009

Graphic-Card

YY-ABD2-B

02.2008

Graphic-Card

YY-ABD3

10.2012

Graphic-Card

ABDZZZZ-A

06.2014

Graphic-Card

YY-ABDZ-A

05.2014

Graphic-Card

XX-11A222

03.2014

Graphic-Card

After that i would like to add many condition for the table model, which will create for me additional table called CORE (Core it will contain some of letters from table model, but there are a lot off condition for names)

Model

Day of Sale

Category

Core

XX-ABC2-A

01.2010

Graphic-Card

XX-ABC2

XX-ABC2-D

02.2012

Graphic-Card

XX-ABC2

XX-ABC2-K

03.2013

Graphic-Card

XX-ABC2

XX-ABC2-I

04.2010

Graphic-Card

XX-ABC2

XX-ABC20-B

03.2010

Graphic-Card

XX-ABC20

XX-ABC200-C

02.2009

Graphic-Card

XX-ABC200

YY-ABD2-A

01.2009

Graphic-Card

YY-ABD2

YY-ABD2-B

02.2008

Graphic-Card

YY-ABD2

YY-ABD3

10.2012

Graphic-Card

YY-ABD3

ABDZZZZ-A

06.2014

Graphic-Card

ABDZZZZ

YY-ABDZ-A

05.2014

Graphic-Card

YY-ABDZ

XX-11A222

03.2014

Graphic-Card

A222

As we can saw, some of models names have 7 characters, sometimes 9 or more or less, sometimes i want to get only 3 or 4 letters

After that they will be available in database Model, Day Of Sale, Category, Core

Next step is to sort by the table core and create additional tabel called as Year.

Year will be created from the Day of Sale, and also will be sorted.

Model

Day of Sale

Category

Core

Year

XX-11A222

03.2014

Graphic-Card

A222

2014

XX-ABC2-A

01.2010

Graphic-Card

XX-ABC2

2010

XX-ABC2-D

02.2012

Graphic-Card

XX-ABC2

2012

XX-ABC2-K

03.2013

Graphic-Card

XX-ABC2

2013

XX-ABC2-I

04.2010

Graphic-Card

XX-ABC2

2010

XX-ABC20-B

03.2010

Graphic-Card

XX-ABC20

2010

XX-ABC200-C

02.2009

Graphic-Card

XX-ABC200

2009

YY-ABD2-A

01.2009

Graphic-Card

YY-ABD2

2009

YY-ABD2-B

02.2008

Graphic-Card

YY-ABD2

2008

YY-ABD3

10.2012

Graphic-Card

YY-ABD3

2012

YY-ABDZ-A

05.2014

Graphic-Card

YY-ABDZ

2014

ABDZZZZ-A

06.2014

Graphic-Card

ABDZZZZ

2014

After that i would like to assign for the same core minimum year and put it to the additional tabel such as ModelYear, which will be created.

Model

Day of Sale

Category

Core

Year

ModelYear

XX-11A222

03.2014

Graphic-Card

A222

2014

2014

XX-ABC2-A

01.2010

Graphic-Card

XX-ABC2

2010

2010

XX-ABC2-D

02.2012

Graphic-Card

XX-ABC2

2012

2010

XX-ABC2-K

03.2013

Graphic-Card

XX-ABC2

2013

2010

XX-ABC2-I

04.2010

Graphic-Card

XX-ABC2

2010

2010

XX-ABC20-B

03.2010

Graphic-Card

XX-ABC20

2010

2010

XX-ABC200-C

02.2009

Graphic-Card

XX-ABC200

2009

2009

YY-ABD2-A

01.2009

Graphic-Card

YY-ABD2

2009

2008

YY-ABD2-B

02.2008

Graphic-Card

YY-ABD2

2008

2008

YY-ABD3

10.2012

Graphic-Card

YY-ABD3

2012

2012

YY-ABDZ-A

05.2014

Graphic-Card

YY-ABDZ

2014

2014

ABDZZZZ-A

06.2014

Graphic-Card

ABDZZZZ

2014

2014

The my question is how to do that ? Is it possible ?

Is there chance that this final version will created additional fields in a few seconds or it will take a time for example 1h ?

Many Many thank of anybody, who can join and help me with this problem.

Show more