Breaking on table up into many

[Emma]

Hi I understand that the advantage of having many tables allows for future
database programmers to easily update the database. The problem is I have a
database with one large table. I ran the analyser and made it into 4 tables.
I clicked no when it asked if I wanted to make a query. So now I have 4
tables that do nothing. When I enter info on my form nothing changes to the
tables as I probably have to make subforms to go with it. Then I have to
think about all the reports. I'm expecting this would mean revamping the
whole project would it be easier to start from scratch? Is it really that
efficent not to be using one large table?

 

[Maury Markowitz]

Hi I understand that the advantage of having many tables allows for future
database programmers to easily update the database.

Normalization is often over-prescribed. There are MANY situations
where a single large table will make everyone's life much easier. If
the system works and isn't causing problems, there's likely no reason
to change it.

database with one large table. I ran the analyser and made it into 4 tables.
I clicked no when it asked if I wanted to make a query. So now I have 4
tables that do nothing.

Ok, what you need to know is what "key" analyser chose to use to link
the tables together. For instance, you might have had a column called
"ID" in the original table that had the key on it, in which case
analyser would use that as the key. Can you identify what I'm talking
about?

You can combine these back into something similar to the original form
with something like this...

CREATE VIEW vEverythingBackTogether
as
SELECT *
FROM table1
JOIN table2 on table2.ID=table1.key
JOIN table3 on table3.ID=table1.key
JOIN table4 on table4.ID=table1.key

Now you can do selects on that as if it were a single table...

SELECT * FROM vEverythingBackTogether

Is it really that efficent not to be using one large table?

It depends. If the columns were _extremely_ sparse, then yes, this can
offer better performance. If the columns were mostly used, then a
single large table is almost always going to be WAY faster. Just make
sure you use indexes.

Maury

 

[Emma]

Hi Maury,

Here's what I tried to put in the form visual basic section under OnCurrent
Event:


CREATE VIEW vEverythingBackTogether
as
SELECT *
FROM Table1
JOIN Demographics on Demographics.ID=Table1.[Lookup to Demographics]
JOIN [Provided Support] on [Provided Support].ID=table1.[Lookup to Provided
Support]
JOIN Questions on Questions.ID=table1.[Lookup to Questions
END

Unfortunately I can't even get it to compile. Am I putting it in the wrong
location or is there alot more wrong with this?

Thanks Emma

However

 

[Emma]

Here's what one of my queries look likes so far

SELECT [Front Desk].Age, Abs([Age]<18) AS [younger then 18], Abs([Age]
Between 19 And 24) AS [Inbetween 19 and 24], Abs([Age]>25 And [Age]<59) AS
[Inbetween 25 and 59], Abs([Age]>60) AS [Older then 60]
FROM [Front Desk];

Not sure where to add vEverythingBackTogether]\

Thanks Emma

Hi Maury,

Here's what I tried to put in the form visual basic section under OnCurrent
Event:


CREATE VIEW vEverythingBackTogether
as
SELECT *
FROM Table1
JOIN Demographics on Demographics.ID=Table1.[Lookup to Demographics]
JOIN [Provided Support] on [Provided Support].ID=table1.[Lookup to Provided
Support]
JOIN Questions on Questions.ID=table1.[Lookup to Questions
END

Unfortunately I can't even get it to compile. Am I putting it in the wrong
location or is there alot more wrong with this?

Thanks Emma

However

Normalization is often over-prescribed. There are MANY situations
where a single large table will make everyone's life much easier. If
the system works and isn't causing problems, there's likely no reason
to change it.


Ok, what you need to know is what "key" analyser chose to use to link
the tables together. For instance, you might have had a column called
"ID" in the original table that had the key on it, in which case
analyser would use that as the key. Can you identify what I'm talking
about?

You can combine these back into something similar to the original form
with something like this...

CREATE VIEW vEverythingBackTogether
as
SELECT *
FROM table1
JOIN table2 on table2.ID=table1.key
JOIN table3 on table3.ID=table1.key
JOIN table4 on table4.ID=table1.key

Now you can do selects on that as if it were a single table...

SELECT * FROM vEverythingBackTogether


It depends. If the columns were _extremely_ sparse, then yes, this can
offer better performance. If the columns were mostly used, then a
single large table is almost always going to be WAY faster. Just make
sure you use indexes.

Maury

 

[Emma]

Ok I created a query vEverythingBackTogether then changed the line FROM
[Front Desk]; to FROM vEverythingBackTogether; Now a SELECT DATA SOURCE Box
is coming up?

Here's what one of my queries look likes so far

SELECT [Front Desk].Age, Abs([Age]<18) AS [younger then 18], Abs([Age]
Between 19 And 24) AS [Inbetween 19 and 24], Abs([Age]>25 And [Age]<59) AS
[Inbetween 25 and 59], Abs([Age]>60) AS [Older then 60]Not sure where to add vEverythingBackTogether]\

Thanks Emma

Hi Maury,

Here's what I tried to put in the form visual basic section under OnCurrent
Event:


CREATE VIEW vEverythingBackTogether
as
SELECT *
FROM Table1
JOIN Demographics on Demographics.ID=Table1.[Lookup to Demographics]
JOIN [Provided Support] on [Provided Support].ID=table1.[Lookup to Provided
Support]
JOIN Questions on Questions.ID=table1.[Lookup to Questions
END

Unfortunately I can't even get it to compile. Am I putting it in the wrong
location or is there alot more wrong with this?

Thanks Emma

However

Hi I understand that the advantage of having many tables allows for future
database programmers to easily update the database.

Normalization is often over-prescribed. There are MANY situations
where a single large table will make everyone's life much easier. If
the system works and isn't causing problems, there's likely no reason
to change it.

database with one large table. I ran the analyser and made it into 4 tables.
I clicked no when it asked if I wanted to make a query. So now I have 4
tables that do nothing.

Ok, what you need to know is what "key" analyser chose to use to link
the tables together. For instance, you might have had a column called
"ID" in the original table that had the key on it, in which case
analyser would use that as the key. Can you identify what I'm talking
about?

You can combine these back into something similar to the original form
with something like this...

CREATE VIEW vEverythingBackTogether
as
SELECT *
FROM table1
JOIN table2 on table2.ID=table1.key
JOIN table3 on table3.ID=table1.key
JOIN table4 on table4.ID=table1.key

Now you can do selects on that as if it were a single table...

SELECT * FROM vEverythingBackTogether

Is it really that efficent not to be using one large table?

It depends. If the columns were _extremely_ sparse, then yes, this can
offer better performance. If the columns were mostly used, then a
single large table is almost always going to be WAY faster. Just make
sure you use indexes.

Maury

 

[John W. Vinson]

Hi Maury,

Here's what I tried to put in the form visual basic section under OnCurrent
Event:


CREATE VIEW vEverythingBackTogether
as
SELECT *
FROM Table1
JOIN Demographics on Demographics.ID=Table1.[Lookup to Demographics]
JOIN [Provided Support] on [Provided Support].ID=table1.[Lookup to Provided
Support]
JOIN Questions on Questions.ID=table1.[Lookup to Questions
END

Unfortunately I can't even get it to compile. Am I putting it in the wrong
location or is there alot more wrong with this?

You're putting it in the wrong place.

SQL is one language; VBA is a different language. Neither compiler understands
the other language.

Secondly, Access/JET does not support Views.

I'm very skeptical that it's even a good idea to "put your table back
together". The database analyzer isn't perfect, of course; it can't be as good
as a trained human mind. Surely you kept a backup of the database before it
was split...?

If you wish, you might post a description of your data structure (before or
after the analyzer chewed it up and spit it out); someone might suggest a way
to move forward.

 

[Maury Markowitz]

Ok I created a query vEverythingBackTogether then changed the line  FROM
[Front Desk]; to FROM vEverythingBackTogether; Now a SELECT DATA SOURCE Box
is coming up?

What happens when you just double-click on vEverythingBackTogether in
the Query tab? Does it come up with the table-like results?

Maury