Cristi,
> Ok, if you do not recommend a big ( about 20 columns ) table, how
>can I do the join then and how do I add to each customer with the same
>street name, street number, block of flats number and flat number, an id
>that will help me join them ?
Oh, you mean get all the _customers_ into one big table. Didn't we start
with that question? Once you have imported all the tables to MySQL::
(1) add int 'old_id' columns to the cp2, cp3 and cp4 tables,
populate with sequential values such that
old_id values are unique across cp2, cp3 or cp4
(2) make a new customers table structure as desired,
add the new cp2 cp3 & cp4 old_ids, and
add an auto_increment int primary key,
(3) import into customer from cp2, cp3, cp4,
excluding dupes based on name address or whatever you want
(4) make a customertype table whose columns include
all columns in cp2 cp3 & cp4 which are not in customers
an auto_increment primary key id
customer_id
customer_type (whatever values you want corresponding to 'cp2' etc)
(5) populate customer_type from joins on
customer and cp2
customer and cp3
customer and cp4
using old_id
(6) drop the old_id column from customers
Now you have the generic customer info in customer, and the
type-specific info in customertypes.
PB
-----
inferno wrote:
> Hi,
>
> Ok, if you do not recommend a big ( about 20 columns ) table, how
> can I do the join then and how do I add to each customer with the same
> street name, street number, block of flats number and flat number, an
> id that will help me join them ?
> I need to be able to see a customer even if it only has a packet in
> cp2/cp3/cp4 table, not only if he has a packet in every table.
>
> Best regards,
> Cristi Stoica
>
> Peter Brawley wrote:
>
>> > I have the data from MS Access to MySQL, half imported by now,
>> > that is not the problem, the same with importing from excel files.
>> > The problem is how can I get all the data in one big table ?
>>
>> With the data you originally described, one big table looks like a
>> bad idea.
>>
>> PB
>>
>>
>> inferno wrote:
>>
>>> Hi,
>>>
>>> I have the data from MS Access to MySQL, half imported by now,
>>> that is not the problem, the same with importing from excel files.
>>> The problem is how can I get all the data in one big table ?
>>>
>>> Best regards,
>>> Cristian Stoica
>>>
>>>
>>> Peter Brawley wrote:
>>>
>>>> Cristi,
>>>>
>>>> > In theory it is simple but I need some help on how that will be
>>>> >"translated" to mysql, I have a concept in mind, but I do not know
>>>> >how to exactly apply it to the situation.
>>>> > I am in a situation where I have the data gathered from more
>>>> persons
>>>> >and everybody had a personal way of designing the database or excel
>>>> >table and now I need to build a database that will include all the
>>>> data in
>>>> >the tables bellow, that is way I was not able to give you a table
>>>> >description from mysql since I do not have all the tables imported
>>>> into
>>>> >mysql.
>>>>
>>>> 1. First thing may be to turn the spreadsheets into tables. It's
>>>> trivial to turn a spreadsheet into an Access table (eg
>>>> http://databases.about.com/aa123100a.htm).
>>>>
>>>> 2. Once you have all the representations in Access, you have a
>>>> choice to make: standardise the data representation in Access, or
>>>> export all the data in its various models to MySQL and standardise
>>>> the mess there. Presumably you know Access much better than MySQL,
>>>> so you may want to do this standardisation in Access before
>>>> exporting to MySQL. In that case, you'll want to implement logic of
>>>> the sort I described in Access, before exporting the data to MySQL.
>>>>
>>>> 3. You have choices for how to export tables from Access to MySQL.
>>>> Simplest may be to export from Access to text files, then use the
>>>> MySQL utility mysqlimport to import the data into a mysql table.
>>>> Or, you can also use an Access-to-mysql converter script (Google
>>>> for them), or a GUI tool like DbTools, which is freeware. Or
>>>> (perhaps the most complex method), you can create the target MySQL
>>>> table, use ODBCAdmin to create a DSN for it, and export to that.
>>>>
>>>> Is that the info you are looking for?
>>>>
>>>> PB
>>>>
>>>> -----
>>>>
>>>> inferno wrote:
>>>>
>>>>> Hi,
>>>>>
>>>>> First of all thank you for your reply.
>>>>> The table names I've wrote in the e-mail were just for info, I
>>>>> do use underscore instead of space, as you can see I do the same
>>>>> for the fields in the table.
>>>>> In theory it is simple but I need some help on how that will
>>>>> be "translated" to mysql, I have a concept in mind, but I do not
>>>>> know how to exactly apply it to the situation.
>>>>> I am in a situation where I have the data gathered from more
>>>>> persons and everybody had a personal way of designing the database
>>>>> or excel table and now I need to build a database that will
>>>>> include all the data in the tables bellow, that is way I was not
>>>>> able to give you a table description from mysql since I do not
>>>>> have all the tables imported into mysql.
>>>>> After I will import the tables into mysql I have to make
>>>>> somehow the new table and I would like it to be good, and that it
>>>>> can be further upgraded ( maybe a new service will need to be
>>>>> entered for a customer and I need to have all the data in one
>>>>> place ) and to be able to handle each entry not by using 4 fields
>>>>> that are varchar.
>>>>>
>>>>> If you / someone can help me with this task I will be very
>>>>> grateful, as I have said before I am a beginer and do not have
>>>>> much experience with MySQL.
>>>>>
>>>>> Best regards and thank you for your advice,
>>>>> Cristi Stoica
>>>>>
>>>>> Peter Brawley wrote:
>>>>>
>>>>>> Cristi,
>>>>>>
>>>>>> > I have the following tables: ( some in Microsoft Access and
> some
>>>>>> > in Excel ) and I want to migrate the data into MySQL and
> develop
>>>>>> > an interface in PHP for easy administration and control...
>>>>>>
>>>>>> A few points:
>>>>>>
>>>>>> 1. Instead of spaces, use underscores or nothing in table names
>>>>>>
>>>>>> 2. If Address_Code contains addresses of Sales_Representatives,
>>>>>> it needs an
>>>>>> column for Sales_Representative ids.
>>>>>>
>>>>>> 3. One way to merge cp2, cp3 and cp4 would be:
>>>>>> (1) add int 'old_id' columns to cp2, cp3 and cp4,
>>>>>> populate with sequential values such that
>>>>>> old_id values are unique across cp2, cp3 or cp4
>>>>>> (2) make a new customers table structure as desired, and
> include
>>>>>> the new cp2 cp3 & cp4 old_ids, and
>>>>>> an auto_increment int primary key,
>>>>>> (3) import into customer from cp2, cp3, cp4,
>>>>>> excluding dupes computed without reference to old_id
>>>>>> (4) make a customertype table whose columns include
>>>>>> all columns in cp2 cp3 & cp4 which are not in
> customers
>>>>>> auto_increment primary key id
>>>>>> customer_id
>>>>>> customer_type (whatever values you want corresponding to
>
>>>>>> cp2 etc)
>>>>>> (5) populate customer_type from joins on
>>>>>> customer and cp2
>>>>>> customer and cp3
>>>>>> customer and cp4
>>>>>> using old_id
>>>>>> (6) drop the old_id column from customers
>>>>>>
>>>>>> PB
>>>>>>
>>>>>> -----
>>>>>>
>>>>>> inferno wrote:
>>>>>>
>>>>>>> Hi,
>>>>>>>
>>>>>>> I have the following tables: ( some in Microsoft Access
> and
>>>>>>> some in Excel ) and I want to migrate the data into MySQL and
>
>>>>>>> develop an interface in PHP for easy administration and
> control.
>>>>>>>
>>>>>>> *1) Sales Representative Code
>>>>>>> *sr_id INTEGER ( unique )
>>>>>>> sr_user VARCHAR
>>>>>>> sr_name VARCHAR
>>>>>>> sr_email VARCHAR
>>>>>>> /id INTEGER ( auto increment and primary key ) this is
>>>>>>> something that I will want to add/
>>>>>>>
>>>>>>> *2) Address Code*
>>>>>>> address_street VARCHAR
>>>>>>> address_street_number VARCHAR
>>>>>>> address_street_block_of_flats_number VARCHAR
>>>>>>> address_staircase_number VARCHAR
>>>>>>> address_area VARCHAR
>>>>>>> address_container INTERGER
>>>>>>> address_code VARCHAR
>>>>>>> /id INTEGER ( auto increment and primary key ) this is
>>>>>>> something that I will want to add/
>>>>>>>
>>>>>>> *3) Customer Packet 2*
>>>>>>> cp2_contract_no INTEGER ( unique )
>>>>>>> cp2_name VARCHAR
>>>>>>> cp2_street VARCHAR
>>>>>>> cp2_street_number VARCHAR
>>>>>>> cp2_block_of_flats_number VARCHAR
>>>>>>> cp2_staircase_number VARCHAR
>>>>>>> cp2_apartament_number VARCHAR
>>>>>>> cp2_packet_old VARCHAR
>>>>>>> cp2_packet_new VARCHAR
>>>>>>> cp2_packet2 VARCHAR
>>>>>>> cp2_user VARCHAR
>>>>>>> cp2_customer_phone VARCHAR
>>>>>>> cp2_id_serie VARCHAR
>>>>>>> cp2_id_number INTEGER
>>>>>>> cp2_personal_code INTEGER ( it's the unique code of a person
> so
>>>>>>> I cannot use it as a unique key since a client can have more
>
>>>>>>> contracts and more locations )
>>>>>>>
>>>>>>> *4) Customer Packet 3*
>>>>>>> cp3_client_id INTEGER( it is not for the location, so if I
>
>>>>>>> select all the data with this code it can show more than one
>
>>>>>>> entry since one client can have more locations )
>>>>>>> cp3_contract_number INTEGER
>>>>>>> cp3_customer_name VARCHAR
>>>>>>> cp3_customer_packet VARCHAR
>>>>>>> cp3_street VARCHAR
>>>>>>> cp3_street_number VARCHAR
>>>>>>> cp3_block_of_flats_number VARCHAR
>>>>>>> cp3_staircase_number VARCHAR
>>>>>>> cp3_floor_number VARCHAR ( ground floor is considered P in
> the
>>>>>>> current database and other problems )
>>>>>>> cp3_flat_number VARCHAR
>>>>>>>
>>>>>>> *5) Customer Packet 4*
>>>>>>> cp4_client_id INTEGER
>>>>>>> cp4_contract_number INTEGER
>>>>>>> cp4_customer_name VARCHAR
>>>>>>> cp4_customer_packet VARCHAR
>>>>>>> cp4_street VARCHAR
>>>>>>> cp4_street_number VARCHAR
>>>>>>> cp4_block_of_flats_number VARCHAR
>>>>>>> cp4_staircase_number VARCHAR
>>>>>>> cp4_floor_number VARCHAR
>>>>>>> cp4_flat_number VARCHAR
>>>>>>>
>>>>>>> Now a little info on the tables:
>>>>>>> Customer Packet 3 and 4 client_id are identical, but they do
> not
>>>>>>> exist all the time.
>>>>>>> Customer Packet 2,3,4 can be grouped based on street,
>>>>>>> street_number, block_of_flats_number, staircase_number,
>>>>>>> flat_number, the floor number is not in the Customer Packet
> 2,
>>>>>>> but there are a few clients that are only inf CP2, CP3 or CP4
>
>>>>>>> and I need to add him also, with the data that I find in the
>
>>>>>>> tables, otherwise I will lose this clients and the will not
> show
>>>>>>> up anymore.
>>>>>>>
>>>>>>> >From all the tables I will have to be able to make a new
> table
>>>>>>> that will have all the data, and if a customer is only in the
>
>>>>>>> customer packet 2, let's say, he will have blanks for the
> other
>>>>>>> fields in table CP( Customer Packet)3 and 4.
>>>>>>> Lets say consider this example like this:
>>>>>>> I have CP2 cable contracts, CP3 internet contracts and CP4
> the
>>>>>>> VoIP contracts and I need to get all the data in one table.
>>>>>>> I will need to be able to have an unique ID for each entry,
> so
>>>>>>> that is way I am thinking of creating a big table with all
> this
>>>>>>> data since I cannot use string as a condition, I've had some
>
>>>>>>> problems with imports from excel/csv into mysql and I would
> like
>>>>>>> to be able to control each location.
>>>>>>>
>>>>>>> I will need to get all this data into a new table that looks
>
>>>>>>> like this:
>>>>>>> *Custmer Data
>>>>>>> *id unique, primary key
>>>>>>> customer_name
>>>>>>> customer_street
>>>>>>> customer_street_number
>>>>>>> customer_block_of_flats_number
>>>>>>> customer_staircase_number
>>>>>>> customer_flat_number
>>>>>>> customer_cp2_packet_old ( use cp2_packet_old )
>>>>>>> customer_cp2_packet_new ( use cp2_packet_new )
>>>>>>> customer_client_id ( you will find it in cp3 or cp4, it
> should
>>>>>>> be in both, but since user error exists, it's not in both. )
>>>>>>> customer_cp3_packet ( use cp3_customer_packet )
>>>>>>> customer_cp3_name ( use cp3_customer_name )
>>>>>>> customer_cp3_contract_number ( use cp3_contract_number )
>>>>>>> customer_cp4_packet ( use cp4_customer_packet )
>>>>>>> customer_cp4_name ( use cp4_customer_name )
>>>>>>> customer_cp4_contract_number ( use cp4_contract_number )
>>>>>>> customer_area
>>>>>>> customer_container
>>>>>>> customer_code
>>>>>>> ( The customer_area, customer_container, customer_code are
> based
>>>>>>> on street, street number, block of flats number, staircase
> number)
>>>>>>>
>>>>>>> I think I will have to join the tables, but I have no
> experience
>>>>>>> with joining tables.
>>>>>>> MySQL version: 4.0.24
>>>>>>> OS: linux
>>>>>>>
>>>>>>> If you have any sugestions or recomandations on how the new
>>>>>>> table should look / how to solve this problem please feel
> free
>>>>>>> to advise me since I am willing to learn.
>>>>>>>
>>>>>>> Best regards,
>>>>>>> Cristi Stoica
>>>>>>>
>>>>>>>
> ------------------------------------------------------------------------
>>>>>>>
>>>>>>>
>>>>>>> No virus found in this incoming message.
>>>>>>> Checked by AVG Anti-Virus.
>>>>>>> Version: 7.0.344 / Virus Database: 267.11.13/126 - Release
> Date:
>>>>>>> 10/9/2005
>>>>>>>
>>>>>>>
>>>>>>
> ------------------------------------------------------------------------
>>>>>>
>>>>>>
>>>>>> No virus found in this outgoing message.
>>>>>> Checked by AVG Anti-Virus.
>>>>>> Version: 7.0.344 / Virus Database: 267.11.14/127 - Release Date:
>
>>>>>> 10/10/2005
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
> ------------------------------------------------------------------------
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
> ------------------------------------------------------------------------
>>>>>
>>>>>
>>>>> No virus found in this incoming message.
>>>>> Checked by AVG Anti-Virus.
>>>>> Version: 7.0.344 / Virus Database: 267.11.14/127 - Release Date:
>>>>> 10/10/2005
>>>>>
>>>>>
>>>>>
>>>> ------------------------------------------------------------------------
>
>>>>
>>>>
>>>> No virus found in this outgoing message.
>>>> Checked by AVG Anti-Virus.
>>>> Version: 7.0.344 / Virus Database: 267.11.14/127 - Release Date:
>>>> 10/10/2005
>>>>
>>>>
>>>>
>>>>
>>>> ------------------------------------------------------------------------
>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>> ------------------------------------------------------------------------
>>>
>>>
>>> No virus found in this incoming message.
>>> Checked by AVG Anti-Virus.
>>> Version: 7.0.344 / Virus Database: 267.11.14/127 - Release Date:
>>> 10/10/2005
>>>
>>>
>>>
>> ------------------------------------------------------------------------
>>
>> No virus found in this outgoing message.
>> Checked by AVG Anti-Virus.
>> Version: 7.0.344 / Virus Database: 267.11.14/127 - Release Date:
>> 10/10/2005
>>
>>
>>
>
>
>------------------------------------------------------------------------
>
>No virus found in this incoming message.
>Checked by AVG Anti-Virus.
>Version: 7.0.344 / Virus Database: 267.11.14/128 - Release Date: 10/10/2005
>
>
>
Attachment: [text/html]
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.11.14/128 - Release Date: 10/10/2005
