Alternative to union query

[deb]

Using Access 2003

I have a form that updates a table with over 50 fields in one record with
Project as Key
I am using 2 union queries and MkTbl/AppTbl to get the data in the format I
need for reporting purposes. It is very slow to run these queries,
MkTbl/AppTbls. Is there an alternative to union queries. can I have the
form send the data as individual records? Are there any other suggestions?

Project = ABC (Key)
AsSold = R
OnTime = Y
OnBudget = G
with approx 50 more...

Is it possible to instead send each field as it's own record..
Project = ABC (Key)
Indicator = AsSold
status = R

Project = ABC (Key)
Indicator = OnTime
status = Y

Project = ABC (Key)
Indicator = OnBudget
status = G

 

[Jerry Whittle]

Yes. You will need to normalize your data into a proper table structure. What
you have now is more like a spreadsheet.

I highly recommend getting some relational database training or reading
"Database Design for Mere Mortals" by Hernandez before proceeding any further
on this database.

 

[deb]

Thank you for your response. I will see if I can find this book.
Meanwhile I still need help. Please give me information that is actionable
and that I can work with. How would you handle these tables and form?

 

[BruceM]

Get lost, Steve. Nobody is going to hire you.

Deb,

You are seeing a problem that arises when your tables are incorrect. I can
design your tables correctly then create your report for a small fee. I
provide help with Access applications for a nominal fee. Contact me if you
want my help.

Steve
(e-mail address removed)

 

[Jerry Whittle]

Well instead of 50 fields across, you need a new Indicator table with much of
the same data in 3 or 4 fields.

Ind_ID Project Indicator Status
1 ABC AsSold R
2 ABC OnTime Y

And so on.

The problem that I see is if AsSold only has certain Statuses, for example,
it would be difficult preventing someone from putting in an incorrect value.

The Ind_ID field should just be an autonumber. The Project field will be the
foriegn key field linking to the Projects table.

Now what if you want to see the data across like your current table? You
create a crosstab query and that will do it.

You could populate this new table with the current data by running a series
of Append queries - one for each Indicator column.

INSERT INTO Indicators ( Project, Indicator, Status )
SELECT Project, "AsSold", AsSold
FROM Project
WHERE AsSold Is Not Null;

In the above you would change the 3 AsSold's to the proper field name in the
existing table. It's important to keep the double quotes around the first one.

 

[John W. Vinson]

Using Access 2003

I have a form that updates a table with over 50 fields in one record with
Project as Key

Ouch. As noted elsethread, this table is almost certainly not properly
normalized.

I am using 2 union queries and MkTbl/AppTbl to get the data in the format I
need for reporting purposes. It is very slow to run these queries,
MkTbl/AppTbls. Is there an alternative to union queries.

Are you certain that the Union queries are the bottleneck? Append queries are
slow (because Access must write the records to disk and possibly also update
indexes); MakeTable queries are even slower, as they must do all that, AND
create a new table, populate its structure into the builtin systems tables,
update indexes, etc. etc. Can you not do the reporting directly from the
Query? You DON'T need a separate table in order to generate a report!

can I have the
form send the data as individual records? Are there any other suggestions?

Where are you "sending" it? What's the current structure of your data, and the
desired format of the report or export?

 

[deb]

Thank you, Jerry

How do I use one form and get the fields into seperate records?
Under each field the user enters either R, Y, or G. When the user enters
this data how do I make the form create a record. I must have all fields on
this one form and there are too many fields to create a sub form for each.

I need to know how to send the data as a record when the data is entered
into the field or changed for that field with ProjectID as key

Thank you sooo much for your time

 

[John... Visio MVP]

I can design your tables correctly then create your report for a small
fee. I provide help with Access applications for a nominal fee. Contact me
if you want my help.

Steve
(e-mail address removed)

These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

A few gems gleaned from the Word New User newsgroup over the Christmas
holidays to show Stevie's "expertise" in Word.


Dec 17, 2008 7:47 pm

Word 2007 ..........
In older versions of Word you could highlght some text then go to Format -
Change Case and change the case of the hoghloghted text. Is this still
available in Word 2007? Where?
Thanks! Steve


Dec 22, 2008 8:22 pm

I am designing a series of paystubs for a client. I start in landscape and
draw a table then add columns and rows to setup labels and their
corresponding value. This all works fine. After a landscape version is
completed, I next need to design a portrait version. Rather than strating
from scratch, I'd like to be able to cut and paste from the landscape
version and design the portrait version.
Steve


Dec 24, 2008, 1:12 PM

How do you protect the document for filling in forms?
Steve


One of my favourites:
Dec 30, 2008 8:07 PM - a reply to stevie
(The original poster asked how to sort a list and stevie offered to create
the OP an Access database)

Yes, you are right but a database is the correct tool to use not a
spreadsheet.

Not at all. If it's just a simple list then a spreadsheet is perfectly
adequate...




John... Visio MVP

 

[Gina Whipp]

Deb,

If you would like you can post the fields in your table and me or anyone but
Steve will help you normalize your data for FREE. Without doing that you
will have to use Union queries which will cause you issues such as what you
are experiencing now.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[John... Visio MVP]

Deb,

John Vinson is confirming what I said that your tables are not correct. I
can
design your tables correctly then create your report for a small fee. I
provide help with Access applications for a nominal fee. Contact me if you
want my help.

Steve
(e-mail address removed)

These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

A few gems gleaned from the Word New User newsgroup over the Christmas
holidays to show Stevie's "expertise" in Word.


Dec 17, 2008 7:47 pm

Word 2007 ..........
In older versions of Word you could highlght some text then go to Format -
Change Case and change the case of the hoghloghted text. Is this still
available in Word 2007? Where?
Thanks! Steve


Dec 22, 2008 8:22 pm

I am designing a series of paystubs for a client. I start in landscape and
draw a table then add columns and rows to setup labels and their
corresponding value. This all works fine. After a landscape version is
completed, I next need to design a portrait version. Rather than strating
from scratch, I'd like to be able to cut and paste from the landscape
version and design the portrait version.
Steve


Dec 24, 2008, 1:12 PM

How do you protect the document for filling in forms?
Steve


One of my favourites:
Dec 30, 2008 8:07 PM - a reply to stevie
(The original poster asked how to sort a list and stevie offered to create
the OP an Access database)

Yes, you are right but a database is the correct tool to use not a
spreadsheet.

Not at all. If it's just a simple list then a spreadsheet is perfectly
adequate...




John... Visio MVP

 

[BruceM]

lol

Why don't you quit wasting your time stalking me and spend your time
gaining experience with Access.

Steve

 

[BruceM]

Jerry is already helping her for free, at a higher level than you have
demonstrated.

 

[deb]

Thank you,

Is there a way to post a bmp or some type of screen shot. That sure would
help with explaining.

My table looks like...
ProjectID_____ProjName____AsSold___OnTime___OnBudget___EAC about 50 more
__123_________ABC________R_________Y_________G________G

The input form is setup like a grid. The form is used as a KPI (Key Point
Indicator). Under the field name the use types either R,G,Y and the text box
is them colored as Red Yellor or Green or White (default) if N/A.

The top of the form has the department, down the left side is the project
phase. Each KPI pertains to a dept and phase.

ProjectName
____SALES________PROJMGMT_______FACTORY
E___AsSold=R_____OnBudget=G_______EAC=G
N___OnTime=Y_____More_____________More
T_____More________More_____________More
R_____More________More_____________More
Y_____More________More_____________More

C_____More________More_____________More
L_____More________More_____________More
O_____More________More_____________More
S_____More________More_____________More
E_____More________More_____________More


For the form purpose this table works great. I run into the issue when
having to do reports.

So my question is how do I keep my form as it is and when the user inputs
the R,G Y in the text box, make this an individual record without having 50+
sub forms.

 

[deb]

Since there are over 50 fields, I had to do 2 union queries to get all of
the fields included. so to get the 2 union queries data back together I had
to do a Make Table query with the first union and an append query for the 2nd
union, so I would have all of the data in one area.

Do you think a union query of 2 union queries would work?

Please see my previous post, explaining in detail what I am trying to
accomplish. I am sure there is a much better way to do what I need.

Thank everyone for their help!!

Steve... If I pay you for your services, I would not have the learning
experince I need to grow. Thanks anyway.

 

[Gina Whipp]

Deb,

Sounds like you have committed spreadsheet with a Microsoft Access table.
This would explain your issue. You would not need 50 or so subforms but
what you would need is normalized tables OR you will have to stick with
Union queries, Make Table queries and other manipulations to get the reports
the way you want to see them. This might take more time then you have... I
don't know but I would suggest you work with what you have and then start
reading...

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials


http://www.databasedev.co.uk/table-of-contents.html

One thing to keep in mind... tables just hold the data, they have nothing to
do with the way the data displays.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Thank you,

Is there a way to post a bmp or some type of screen shot. That sure would
help with explaining.

My table looks like...
ProjectID_____ProjName____AsSold___OnTime___OnBudget___EAC about 50 more
__123_________ABC________R_________Y_________G________G

The input form is setup like a grid. The form is used as a KPI (Key Point
Indicator). Under the field name the use types either R,G,Y and the text
box
is them colored as Red Yellor or Green or White (default) if N/A.

The top of the form has the department, down the left side is the project
phase. Each KPI pertains to a dept and phase.

ProjectName
____SALES________PROJMGMT_______FACTORY
E___AsSold=R_____OnBudget=G_______EAC=G
N___OnTime=Y_____More_____________More
T_____More________More_____________More
R_____More________More_____________More
Y_____More________More_____________More

C_____More________More_____________More
L_____More________More_____________More
O_____More________More_____________More
S_____More________More_____________More
E_____More________More_____________More


For the form purpose this table works great. I run into the issue when
having to do reports.

So my question is how do I keep my form as it is and when the user inputs
the R,G Y in the text box, make this an individual record without having
50+
sub forms.

 

[deb]

I understand that I need the data normalized. It seems that we have already
established the fact that the data needs to be input into the tables
differently.

My question is how do I get the data into the table as one record for each
field and still use the form as I have it?

Do you have an idea on how this can be achieved?

 

[Gina Whipp]

Deb,

To get the data to display as needed you would use a query not a table. You
would then use the query to display the data in the form the way you need it
to be displayed.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[John... Visio MVP]

I hope you were looking in a mirror when you typed that.

John...

 

[Peter Hibbs]

Deb,

As Jerry and Gina have indicated, you need to Normalise your data
first. Have a look at the Excel to Access Converter Utility program
that can (probably) do it for you automatically. Just export the whole
table as a CSV file and then import that into the Utility program to
separate out the data into the various tables that you need.

http://www.rogersaccesslibrary.com/...?TID=183&SID=e81a164e2314187z3e8587231e28cb7d

Once you have the data in separate tables then you can sort how to
display the data on screen.

HTH

Peter Hibbs.

 

[deb]

Shoot me now!!!

That is what I have done and it is too slow. Everyone says it is not
normalized. When I ask how to normalize this particular situation using my
form and I am told to read a billion websites. I am going in circles.


I will ask again.

Is it possible to have a form with many text boxes and if the user enters
data or updates the text box it creates/edits a record in a table as a
seperate record???

I know what a normalized database is. I just need to update my table via
this form and to do so each text box must create its own record.

Please help.