Access Logic Puzzle....help, anyone?

[Guest]

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

 

[Peter Hibbs]

Alex,

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

Peter Hibbs

 

[Douglas J. Steele]

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

(no private e-mails, please)


"AlexanderKingsleyCotton"

 

[Bob Quintal]

=?Utf-8?B?QWxleGFuZGVyS2luZ3NsZXlDb3R0b24=?=

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

 

[Guest]

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]

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

(no e-mails, please!)


"AlexanderKingsleyCotton"