Alternative to union query

[deb]

This data is not imput via excel. It is a form. Please read my posts if full.

I am not sure how else I can explain what I am doing.

 

[Gina Whipp]

Deb...

I can see these postings are making you crazy so... Please go to my
website... send me an eMail with your phone number and I will call you NO
CHARGE and explain.

--
Gina Whipp

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

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

 

[Peter Hibbs]

Deb,

Your data is stored in tables, not a form. You need to first get your
table data normalised if you are to have any chance of making your
database work properly.

The Utility I mentioned should be able to do that for you, if you read
the User Manual carefully. Ignore the Excel reference in the name of
the utility program, that is there because usually the data is in
Excel to start with.

In your case, you have the data in an Access table which, by the sound
of it, is arranged something like a spreadsheet. If you export the
contents of this table to a .csv file (which can be loaded into Excel
if you need to look at it) and then import that .csv file into the
Converter Utility which will then convert that data into properly
normalised tables. When you have done that you can then design your
forms to show the data in a format which will be acceptable to your
users.

If you have any questions regarding this then post back but I suggest
you check the Web site I mentioned first.

Peter Hibbs.

 

[deb]

Thank you for your response.

I now have the data in a table that is normalized.

ProjectID = 123
ProjectName = ABC
KPI = OnTime
KPIstatus = R

How do I use my form to update the current data and add new records?
The form has 50 fields. When the user inputs R, G or Y into this field. I
need the data updated. Do I need 50 subforms (1 for each field) or is there
a better way?

 

[Peter Hibbs]

Deb,

OK, are you saying that your table has now got just four fields? If
so, then where do the 50 fields come from (and you definitely do not
want 50 sub-forms). I think it would be helpful if you told us exactly
what tables you have now and what fields you have in each table.
Something like :-

tblProjects
=======
ID (AutoNumber)
ProjectID (Text)
ProjectName (Text)
KPI (Yes/No)

tblSomethingElse
==========
ID (AutoNumber)
etc.
etc.
etc.

Also, if there any links between the tables, which fields are linked
to which.

As far as the form is concerned it sounds to me like a simple
Continuous form would do but I could be missing something here.

Peter Hibbs.

 

[John... Visio MVP]

No, a sub form can reference several fields from several tables. So it would
depend on how you have normalized the information. The chances of one
subform for rach field is very remote.

John... Visio MVP

 

[deb]

I wish I could post a printscreen of my form. That would help tremendously.

Yes, it would be much like a continuous form however the fields are setup
in a matrix format not one record after another.

My forms is like below ( my form holds approx 50 fields, not all shown, more
is substitute for some fields). A continuous form cannot be setup like a
matrix.

ProjectName ABC ProjID 123
____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


ProjID = 123
ProjectName = ABC
Indicator = AsSold
status = R

ProjID = 123
ProjectName = ABC
Indicator = OnTime
status = Y

ProjID = 123
ProjectName = ABC
Indicator = OnBudget
status = G


and so on for approx 50 fields which I need to be 50 records in a table
using one form that has the 50 fields on it in a matrix type setup.

 

[deb]

Thank you!!

I sent an email with a printscreen of my form. This may explain why I am
having problems. One picture is work a thousand words.

In the small box next to each label is a text box. If the user types in R,G
Y the box changes colors. Ther record for each ProjectID has all of those
fields that holds either R,G, Y or W (W is default).

For reporting purposes I need a record for each text box.
Record should contain...
ProjectID = 123
ProjectName = ABC
KPI = OnTime
KPIstatus = R

Maybe since the form is like a martix, there may not be a better way to do
what I want.

 

[Gina Whipp]

Deb,

Thinking out loud here after our phone converstation

tblProjects
pProjectID (PK)
pProjectName
etc...

tblProjectManagers (not really relevant to the issue)

tblProjectDetails
pdProjectID (FK)
pdProjectSubcategoryID (FK)
pdKPI
pdKPIstatus
pdDateTimeStamp
etc...

I think what is needed is...

tblProjectCategory
pcProjectCategoryID (PK)

tblProjectSubcategory
psProjectSubcategoryID (PK)
psProjectCategoryID (FK)

--
Gina Whipp

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

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

 

[Peter Hibbs]

Deb,

Sorry, but that is not what I asked for, this looks more like what you
want to see on screen rather than the table itself. If you have three
or more fields in the same table that store the same ProjectID and the
same ProjectName then the table is NOT normalized. I see that Gina has
spoken to you and suggested a table schema that should fit your
situation, I suggest you consider that carefully. If you already have
a lot of records with data that you don't want to lose then you should
consider splitting the data into those tables using the conversion
utility program I suggested.

Regarding the screen display that you want, you have said that you
want the data displayed in a 'matrix type set up' (I guess like a sort
of spreadsheet display) with the Project names on the left and the
project information in columns. Well, there is no easy way to do that
with the standard Access controls but you could use a FlexGrid control
on the form which will do that. The only problem with this idea is
that you would need to write a fair bit of VBA code to implement it
and I suspect you are not that expert with coding (or perhaps you
are!). If you would like to see some examples of using a FlexGrid
control (including a Project Management scheme) then have a look at :-

http://www.rogersaccesslibrary.com/...?TID=511&SID=3ba469zcfz7b248ef27fccf3f8ze9aa1

However, you still need to sort your tables out first.

Peter Hibbs.

 

[John W. Vinson]

Since there are over 50 fields, I had to do 2 union queries to get all of
the fields included.

I'm sorry, this isn't making any sense to me. A Table (local or linked) cannot
exceed 255 fields - 50 is wide but not extraordinaily so. What are your
"union" queries doing???

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.

Looking at the rest of the thread, and your discussion with Gina, let me toss
out a possible idea: you could have a normalized tall-thin table, and use an
UNBOUND form as you describe. The form would need VBA code to a) populate the
controls on the form to display the data when you choose a record and b)
reverse the process, opening a Recordset or running an append query to copy
data from the unbound form into the table.

This isn't trivial but not all that hard either. I think a lot of the
argumentation you're seeing has to do with confusion (on our part more than
yours!) between TABLE design and FORM design. Just remember that data storage
and data display are *different* functions, with different constraints; it
appears that you have a pretty strong need for a data *display* (and
interaction) layout which is somewhat incompatible with a properly normalized
table structure, at least using the "basic" Access tools.

 

[BruceM]

I *do* provide help regularly. Review the postings. In this case Jerry had
responded, so I saw no need to weigh in except to be sure the OP stayed away
from your "offer".

I will leave you alone when you stop abusing the intent of the newsgroups.
Sometimes you are maligned for your past conduct when all you have done is to
offer a suggestion. In those cases I have pointed out that the posting
should be treated differently than if it was a solicitation. In other words,
I have come to your defense. You can check that out too.

You have provided her NO help! If you quit wasting your time stalking me and
start gaining experience with Access, maybe someday you could provide help
to OPs.

Steve

 

[John... Visio MVP]

Deb,

You have now wasted several days and gotten nowhere. Let me help you
design your tables for a small fee and then you can go off on your own and
design your forms, reports and associated queries.

Steve
(e-mail address removed)

stevie, don't be shy, you should accept full credit for the delays. The one
and only reason for the noise on this post is your constant desire to abuse
these newsgroups in a lame attempt to seperate posters from their money in a
FREE service. There are legitimate forums for this, but it appears you are
not competent enough to deal with real competition.


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