help with complex query

[mwm]

I have a single table that containes payments of home utilities such as water, gas and electric. the tables has accounts numbers for each utility. the homes have address, lot number and its a townhouse it includes unit number. What I need to do is to create one table to have the address, lot, unit, and account number for each utility. then have a second table that will only have the payment records. I don't know how to write a query to do this. any suggestion is much appreciated.

Thank You

CREATE TABLE `Paymentstype` (
`ID` INTEGER NOT NULL,
`community` VARCHAR(255),
`lot` DOUBLE NULL,
`unit` INTEGER,
`address` VARCHAR(255),
`account` VARCHAR(255),
`one` VARCHAR(255),
`two` VARCHAR(255),
`three` VARCHAR(255),
`four` VARCHAR(255),
`five` VARCHAR(255),
`six` VARCHAR(255),
`seven` INTEGER,
`type` VARCHAR(255)
) TYPE=MyISAM;

[guelphdad]

what are columns one through seven? or are they not relevant or are they the ones you are trying to get the data out of? are you dropping any columns from your first table once you get it out and place it in the second table?

also look into the use of UNION and UNION ALL as they will most likely be needed to grab the payments from the first table and insert them into the second.

[mwm]

columns 1-7 are not revlant as this time. If I can get the address, lot, unit and each utility account number in one single table. I will be able to drop columns as need later.

thanks

[guelphdad]

can you show some sample data? I'm pretty sure I know what you have in your table and how you want to extract it, but want to make sure.

DId you look at using UNION ALL? you would need one query for each utility and grab it out by combining them with UNION ALL and all results would then be inserted into a single table