Hello All,

I'm facing a bizarre problem and I was hoping someone could help me. I'm a
beginning Access user using Access 2003 on an XP machine. I've got a huge
database on my hands which I have split into multiple smaller databases
(though I suppose that's not relevant at the moment).

Here's my intriguing problem. The database I'm working with has only one
table, and that table has only 9 columns: a Key ID column, then three ID
numbers (an Account, Electric, and Gas) and then Name, Address, City, State,
and Zip.

My problem is that the database is constructed in a screwy way. All of the
rows, each of which represents a house, have the Name Address City State and
Zip. But each row DOES NOT contain all the ID information in it (Acct ID,
Electric, & Gas). Instead, each row has the Acct ID, but each row is doubled
(so the table has twice as many records in it as there are houses*). One row
has the Acct ID, the ElectricID, a blank GasID field, and then the name and
address info; the second row has the Acct ID, a blank Electric field, the
GasID, and identical name and address info.

I want to integrate them, so that each house only appears once. Now that
would be hard enough as it is, but here's another wrinkle: a few of the
houses don't have natural gas accts, and so have no GasID, and therefore only
appear once. That means that the total number of rows in the table is NOT
exactly twice the number of houses, as I stated before for simplicity's sake.

For the life of me, I can't figure out how to seperate out and recombine all
the information so that it's in the right format...something to do with
queries, maybe querying out some and exporting them to manipulate them in
Excel, or creating csvs and reimporting the information in some way....I'm
sure it could be done with a custom macro, but I don't know Visual Basic

Let me know if you have ideas, and THANK YOU for reading!

Alex,
nonprofiteer in san francisco

Alex,

Not sure if it will help but have a look at the Excel to Access
Converter utility at :-
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='Excel%20to%20Access%20Converter%20Utility%20program'

Peter Hibbs

On Thu, 8 Nov 2007 11:49:02 -0800, AlexanderKingsleyCotton
<(E-Mail Removed)> wrote:

>
>Hello All,
>
>I'm facing a bizarre problem and I was hoping someone could help me. I'm a
>beginning Access user using Access 2003 on an XP machine. I've got a huge
>database on my hands which I have split into multiple smaller databases
>(though I suppose that's not relevant at the moment).
>
>Here's my intriguing problem. The database I'm working with has only one
>table, and that table has only 9 columns: a Key ID column, then three ID
>numbers (an Account, Electric, and Gas) and then Name, Address, City, State,
>and Zip.
>
>My problem is that the database is constructed in a screwy way. All of the
>rows, each of which represents a house, have the Name Address City State and
>Zip. But each row DOES NOT contain all the ID information in it (Acct ID,
>Electric, & Gas). Instead, each row has the Acct ID, but each row is doubled
>(so the table has twice as many records in it as there are houses*). One row
>has the Acct ID, the ElectricID, a blank GasID field, and then the name and
>address info; the second row has the Acct ID, a blank Electric field, the
>GasID, and identical name and address info.
>
>I want to integrate them, so that each house only appears once. Now that
>would be hard enough as it is, but here's another wrinkle: a few of the
>houses don't have natural gas accts, and so have no GasID, and therefore only
>appear once. That means that the total number of rows in the table is NOT
>exactly twice the number of houses, as I stated before for simplicity's sake.
>
>For the life of me, I can't figure out how to seperate out and recombine all
>the information so that it's in the right format...something to do with
>queries, maybe querying out some and exporting them to manipulate them in
>Excel, or creating csvs and reimporting the information in some way....I'm
>sure it could be done with a custom macro, but I don't know Visual Basic
>
>Let me know if you have ideas, and THANK YOU for reading!
>
>Alex,
>nonprofiteer in san francisco

No, you don't want to integrate them so that each house appears once. What
you want to do is normalize the database, which means you need two (and
possibly) three tables.

One table would be the Household table: it would contain one row for each
house (AcctID, Name, Address, City, State and Zip).

One table would contain one row for each utility for each house (AcctID,
UtilityType, UtilityNumber)

The (perhaps optional) third table would have one row for each utility type
(i.e.: right now it would have two rows: Electric and Gas)

Fortunately, you can easily convert your existing table to that proper
format.

Create a query that returns only the details required for the first table.
The SQL would look something like:

SELECT DISTINCT AcctID, Name, Address, City, State, Zip
FROM ExistingTable

Use that query to populate your first table

(I don't see the point of the KeyID field, but you could include that too if
you want)

Create a second query that returns the details required for the second
table. The SQL would look something like:

SELECT AcctID, "Electric" AS UtilityType, ElecticID AS UtilityID
FROM ExistingTable
UNION
SELECT AcctID, "Gas" AS UtilityType, GasID AS UtilityID
FROM ExistingTable

Again, use that query to populate the second table.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"AlexanderKingsleyCotton"
<(E-Mail Removed)> wrote in message
news:E4450154-86C8-44C8-BE8E-(E-Mail Removed)...
>
> Hello All,
>
> I'm facing a bizarre problem and I was hoping someone could help me. I'm a
> beginning Access user using Access 2003 on an XP machine. I've got a huge
> database on my hands which I have split into multiple smaller databases
> (though I suppose that's not relevant at the moment).
>
> Here's my intriguing problem. The database I'm working with has only one
> table, and that table has only 9 columns: a Key ID column, then three ID
> numbers (an Account, Electric, and Gas) and then Name, Address, City,
> State,
> and Zip.
>
> My problem is that the database is constructed in a screwy way. All of the
> rows, each of which represents a house, have the Name Address City State
> and
> Zip. But each row DOES NOT contain all the ID information in it (Acct ID,
> Electric, & Gas). Instead, each row has the Acct ID, but each row is
> doubled
> (so the table has twice as many records in it as there are houses*). One
> row
> has the Acct ID, the ElectricID, a blank GasID field, and then the name
> and
> address info; the second row has the Acct ID, a blank Electric field, the
> GasID, and identical name and address info.
>
> I want to integrate them, so that each house only appears once. Now that
> would be hard enough as it is, but here's another wrinkle: a few of the
> houses don't have natural gas accts, and so have no GasID, and therefore
> only
> appear once. That means that the total number of rows in the table is NOT
> exactly twice the number of houses, as I stated before for simplicity's
> sake.
>
> For the life of me, I can't figure out how to seperate out and recombine
> all
> the information so that it's in the right format...something to do with
> queries, maybe querying out some and exporting them to manipulate them in
> Excel, or creating csvs and reimporting the information in some way....I'm
> sure it could be done with a custom macro, but I don't know Visual Basic
>
>
> Let me know if you have ideas, and THANK YOU for reading!
>
> Alex,
> nonprofiteer in san francisco

=?Utf-8?B?QWxleGFuZGVyS2luZ3NsZXlDb3R0b24=?=
<(E-Mail Removed)> wrote in
news:0BA49668-3F8C-4CCD-87A2-(E-Mail Removed):

>
> Wow, well thank you for your suggestion!
>
> My only worry is that I think I *do* (unfortunately) need to
> combine them. We're developing a custom database and web
> application in MySQL and php, and it will need to search this
> database in order to auto-populate a form with client data. That
> search and autopopulation will need to happen very quickly, and
> the programming needs to be simple...so I assumed that we would
> need to search only one table. I'll ask our programmer and see
> whether it's feasible to search a relational database of the type
> that you suggest.
>
A properly normalized database will be faster than one which has
lots of redundant data.

Q

> If it isn't, do you have any ideas for paths I could pursue to get
> an integrated table?
>
> Thank you again!
>
> Alex
>
> "Douglas J. Steele" wrote:
>
>> No, you don't want to integrate them so that each house appears
>> once. What you want to do is normalize the database, which means
>> you need two (and possibly) three tables.
>>
>> One table would be the Household table: it would contain one row
>> for each house (AcctID, Name, Address, City, State and Zip).
>>
>> One table would contain one row for each utility for each house
>> (AcctID, UtilityType, UtilityNumber)
>>
>> The (perhaps optional) third table would have one row for each
>> utility type (i.e.: right now it would have two rows: Electric
>> and Gas)
>>
>> Fortunately, you can easily convert your existing table to that
>> proper format.
>>
>> Create a query that returns only the details required for the
>> first table. The SQL would look something like:
>>
>> SELECT DISTINCT AcctID, Name, Address, City, State, Zip
>> FROM ExistingTable
>>
>> Use that query to populate your first table
>>
>> (I don't see the point of the KeyID field, but you could include
>> that too if you want)
>>
>> Create a second query that returns the details required for the
>> second table. The SQL would look something like:
>>
>> SELECT AcctID, "Electric" AS UtilityType, ElecticID AS UtilityID
>> FROM ExistingTable
>> UNION
>> SELECT AcctID, "Gas" AS UtilityType, GasID AS UtilityID
>> FROM ExistingTable
>>
>> Again, use that query to populate the second table.
>>
>> --
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no private e-mails, please)
>>
>>
>> "AlexanderKingsleyCotton"
>> <(E-Mail Removed)> wrote in
>> message
>> news:E4450154-86C8-44C8-BE8E-(E-Mail Removed)...
>> >
>> > Hello All,
>> >
>> > I'm facing a bizarre problem and I was hoping someone could
>> > help me. I'm a beginning Access user using Access 2003 on an XP
>> > machine. I've got a huge database on my hands which I have
>> > split into multiple smaller databases (though I suppose that's
>> > not relevant at the moment).
>> >
>> > Here's my intriguing problem. The database I'm working with has
>> > only one table, and that table has only 9 columns: a Key ID
>> > column, then three ID numbers (an Account, Electric, and Gas)
>> > and then Name, Address, City, State,
>> > and Zip.
>> >
>> > My problem is that the database is constructed in a screwy way.
>> > All of the rows, each of which represents a house, have the
>> > Name Address City State and
>> > Zip. But each row DOES NOT contain all the ID information in it
>> > (Acct ID, Electric, & Gas). Instead, each row has the Acct ID,
>> > but each row is doubled
>> > (so the table has twice as many records in it as there are
>> > houses*). One row
>> > has the Acct ID, the ElectricID, a blank GasID field, and then
>> > the name and
>> > address info; the second row has the Acct ID, a blank Electric
>> > field, the GasID, and identical name and address info.
>> >
>> > I want to integrate them, so that each house only appears once.
>> > Now that would be hard enough as it is, but here's another
>> > wrinkle: a few of the houses don't have natural gas accts, and
>> > so have no GasID, and therefore only
>> > appear once. That means that the total number of rows in the
>> > table is NOT exactly twice the number of houses, as I stated
>> > before for simplicity's sake.
>> >
>> > For the life of me, I can't figure out how to seperate out and
>> > recombine all
>> > the information so that it's in the right format...something to
>> > do with queries, maybe querying out some and exporting them to
>> > manipulate them in Excel, or creating csvs and reimporting the
>> > information in some way....I'm sure it could be done with a
>> > custom macro, but I don't know Visual Basic
>> >
>> >
>> > Let me know if you have ideas, and THANK YOU for reading!
>> >
>> > Alex,
>> > nonprofiteer in san francisco
>>
>>
>>
>



--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Wow, well thank you for your suggestion!

My only worry is that I think I *do* (unfortunately) need to combine them.
We're developing a custom database and web application in MySQL and php, and
it will need to search this database in order to auto-populate a form with
client data. That search and autopopulation will need to happen very quickly,
and the programming needs to be simple...so I assumed that we would need to
search only one table. I'll ask our programmer and see whether it's feasible
to search a relational database of the type that you suggest.

If it isn't, do you have any ideas for paths I could pursue to get an
integrated table?

Thank you again!

Alex

"Douglas J. Steele" wrote:

> No, you don't want to integrate them so that each house appears once. What
> you want to do is normalize the database, which means you need two (and
> possibly) three tables.
>
> One table would be the Household table: it would contain one row for each
> house (AcctID, Name, Address, City, State and Zip).
>
> One table would contain one row for each utility for each house (AcctID,
> UtilityType, UtilityNumber)
>
> The (perhaps optional) third table would have one row for each utility type
> (i.e.: right now it would have two rows: Electric and Gas)
>
> Fortunately, you can easily convert your existing table to that proper
> format.
>
> Create a query that returns only the details required for the first table.
> The SQL would look something like:
>
> SELECT DISTINCT AcctID, Name, Address, City, State, Zip
> FROM ExistingTable
>
> Use that query to populate your first table
>
> (I don't see the point of the KeyID field, but you could include that too if
> you want)
>
> Create a second query that returns the details required for the second
> table. The SQL would look something like:
>
> SELECT AcctID, "Electric" AS UtilityType, ElecticID AS UtilityID
> FROM ExistingTable
> UNION
> SELECT AcctID, "Gas" AS UtilityType, GasID AS UtilityID
> FROM ExistingTable
>
> Again, use that query to populate the second table.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "AlexanderKingsleyCotton"
> <(E-Mail Removed)> wrote in message
> news:E4450154-86C8-44C8-BE8E-(E-Mail Removed)...
> >
> > Hello All,
> >
> > I'm facing a bizarre problem and I was hoping someone could help me. I'm a
> > beginning Access user using Access 2003 on an XP machine. I've got a huge
> > database on my hands which I have split into multiple smaller databases
> > (though I suppose that's not relevant at the moment).
> >
> > Here's my intriguing problem. The database I'm working with has only one
> > table, and that table has only 9 columns: a Key ID column, then three ID
> > numbers (an Account, Electric, and Gas) and then Name, Address, City,
> > State,
> > and Zip.
> >
> > My problem is that the database is constructed in a screwy way. All of the
> > rows, each of which represents a house, have the Name Address City State
> > and
> > Zip. But each row DOES NOT contain all the ID information in it (Acct ID,
> > Electric, & Gas). Instead, each row has the Acct ID, but each row is
> > doubled
> > (so the table has twice as many records in it as there are houses*). One
> > row
> > has the Acct ID, the ElectricID, a blank GasID field, and then the name
> > and
> > address info; the second row has the Acct ID, a blank Electric field, the
> > GasID, and identical name and address info.
> >
> > I want to integrate them, so that each house only appears once. Now that
> > would be hard enough as it is, but here's another wrinkle: a few of the
> > houses don't have natural gas accts, and so have no GasID, and therefore
> > only
> > appear once. That means that the total number of rows in the table is NOT
> > exactly twice the number of houses, as I stated before for simplicity's
> > sake.
> >
> > For the life of me, I can't figure out how to seperate out and recombine
> > all
> > the information so that it's in the right format...something to do with
> > queries, maybe querying out some and exporting them to manipulate them in
> > Excel, or creating csvs and reimporting the information in some way....I'm
> > sure it could be done with a custom macro, but I don't know Visual Basic
> >
> >
> > Let me know if you have ideas, and THANK YOU for reading!
> >
> > Alex,
> > nonprofiteer in san francisco
>
>
>

Again, I doubt very much that you must have your table in that format.

Just because that's the format you want for searching for your form doesn't
mean that's the format in which you need to store the data. You can create a
query that presents the data in the way in which you've outlined and use
that query rather than the table.

Remember that it's all about the data. Design the tables around what's right
for storage, not how you intend to use the data. You can always create
queries (and, in Access, forms and reports) that presents the data how you
wish to present it to the users.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"AlexanderKingsleyCotton"
<(E-Mail Removed)> wrote in message
news:0BA49668-3F8C-4CCD-87A2-(E-Mail Removed)...
>
> Wow, well thank you for your suggestion!
>
> My only worry is that I think I *do* (unfortunately) need to combine them.
> We're developing a custom database and web application in MySQL and php,
> and
> it will need to search this database in order to auto-populate a form with
> client data. That search and autopopulation will need to happen very
> quickly,
> and the programming needs to be simple...so I assumed that we would need
> to
> search only one table. I'll ask our programmer and see whether it's
> feasible
> to search a relational database of the type that you suggest.
>
> If it isn't, do you have any ideas for paths I could pursue to get an
> integrated table?
>
> Thank you again!
>
> Alex
>
> "Douglas J. Steele" wrote:
>
>> No, you don't want to integrate them so that each house appears once.
>> What
>> you want to do is normalize the database, which means you need two (and
>> possibly) three tables.
>>
>> One table would be the Household table: it would contain one row for each
>> house (AcctID, Name, Address, City, State and Zip).
>>
>> One table would contain one row for each utility for each house (AcctID,
>> UtilityType, UtilityNumber)
>>
>> The (perhaps optional) third table would have one row for each utility
>> type
>> (i.e.: right now it would have two rows: Electric and Gas)
>>
>> Fortunately, you can easily convert your existing table to that proper
>> format.
>>
>> Create a query that returns only the details required for the first
>> table.
>> The SQL would look something like:
>>
>> SELECT DISTINCT AcctID, Name, Address, City, State, Zip
>> FROM ExistingTable
>>
>> Use that query to populate your first table
>>
>> (I don't see the point of the KeyID field, but you could include that too
>> if
>> you want)
>>
>> Create a second query that returns the details required for the second
>> table. The SQL would look something like:
>>
>> SELECT AcctID, "Electric" AS UtilityType, ElecticID AS UtilityID
>> FROM ExistingTable
>> UNION
>> SELECT AcctID, "Gas" AS UtilityType, GasID AS UtilityID
>> FROM ExistingTable
>>
>> Again, use that query to populate the second table.
>>
>> --
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no private e-mails, please)
>>
>>
>> "AlexanderKingsleyCotton"
>> <(E-Mail Removed)> wrote in message
>> news:E4450154-86C8-44C8-BE8E-(E-Mail Removed)...
>> >
>> > Hello All,
>> >
>> > I'm facing a bizarre problem and I was hoping someone could help me.
>> > I'm a
>> > beginning Access user using Access 2003 on an XP machine. I've got a
>> > huge
>> > database on my hands which I have split into multiple smaller databases
>> > (though I suppose that's not relevant at the moment).
>> >
>> > Here's my intriguing problem. The database I'm working with has only
>> > one
>> > table, and that table has only 9 columns: a Key ID column, then three
>> > ID
>> > numbers (an Account, Electric, and Gas) and then Name, Address, City,
>> > State,
>> > and Zip.
>> >
>> > My problem is that the database is constructed in a screwy way. All of
>> > the
>> > rows, each of which represents a house, have the Name Address City
>> > State
>> > and
>> > Zip. But each row DOES NOT contain all the ID information in it (Acct
>> > ID,
>> > Electric, & Gas). Instead, each row has the Acct ID, but each row is
>> > doubled
>> > (so the table has twice as many records in it as there are houses*).
>> > One
>> > row
>> > has the Acct ID, the ElectricID, a blank GasID field, and then the name
>> > and
>> > address info; the second row has the Acct ID, a blank Electric field,
>> > the
>> > GasID, and identical name and address info.
>> >
>> > I want to integrate them, so that each house only appears once. Now
>> > that
>> > would be hard enough as it is, but here's another wrinkle: a few of the
>> > houses don't have natural gas accts, and so have no GasID, and
>> > therefore
>> > only
>> > appear once. That means that the total number of rows in the table is
>> > NOT
>> > exactly twice the number of houses, as I stated before for simplicity's
>> > sake.
>> >
>> > For the life of me, I can't figure out how to seperate out and
>> > recombine
>> > all
>> > the information so that it's in the right format...something to do with
>> > queries, maybe querying out some and exporting them to manipulate them
>> > in
>> > Excel, or creating csvs and reimporting the information in some
>> > way....I'm
>> > sure it could be done with a custom macro, but I don't know Visual
>> > Basic
>> >
>> >
>> > Let me know if you have ideas, and THANK YOU for reading!
>> >
>> > Alex,
>> > nonprofiteer in san francisco
>>
>>
>>