In over my head!

[Susanne]

I had to go against everything I know about database design since the
customer wanted a form to appear exactly as it does on paper. The majority
of all fields pull data from the same table (components) and retain the info
in a different field, instead of doing a continuous form (one in component 1,
a different one in component 2, ... component n), there are seperate fields
for each: component 1, ... component n, with a restriction on what can be
selected for each of these fields, though the data all comes from the same
table.

Now, I need these multitude of fields to come together to construct a query
so I can create a form to show which components are used on what piece of
equipment for whatever amount of time it was there. In this way, we will
know when the components need to go in for repair.

Unfortunately, I have no way or knowledge of how to put this information
into one field, assuming it would be in a query of some sort, or even if it
could be done. I wanted to construct the db differently, but have no idea
had to have the correct layout in relation to the paper form. Also, so items
will have a left component and a right component, so, technically 2 fields of
components side by side (on paper).

HELP!

 

[Mike Painter]

I hope you put everything into writing and had somebody sign off on it.
"It has to look like this" is a time bomb.
Chaces are even if you do get the bosses to sign off they will never be
happy and it will be expensive.
You have a lot of coding in front of you.

The way I solved my problem was to create a flat file (as it appears you
have) name the fields comp1, comp2, comp3... and maintain a properly related
table in code from that form.
That way reports,etc came together with little problem.
That table had it's own key,the foreign key from the flat file, the field
number from that record and the actual data that was needed.

It was similar to the way the multivalued fields work in 07.

 

[Susanne]

Thanks. Yeah, in my head I have an idea where I need to go, and yes, it
appears that coding is not going to be my friend.

 

[JString]

Is there any way you can give more detail?

To me it's sounding like you need to put together a pretty complex series of
queries... If it were me I would avoid coding anything to do with the
report's record source.

 

[Keith Wilby]

I had to go against everything I know about database design since the
customer wanted a form to appear exactly as it does on paper.

The customer is making the classic mistake of attempting to tell you the
solution instead of telling you their requirements. It's like going to buy
a car and telling Ford how to make the engine. 7 cylinders sounds good to
me but I know fek-all about how to design an engine. If I were you I'd be
using all of my persuasive powers to convince the customer that the *how* is
my domain.

2p supplied

Keith.
www.keithwilby.co.uk

 

[Paul Shapiro]

Even if the customer wants the forms to look a certain way, that would not
necessarily mean the data structures have to be done that way. I've always
found that I can store the data in a proper way, and then with extra work
make the forms look the way they want. Most of the time when I tell the
client that it will cost more to do it their way, as in several thousands of
dollars more, they're more willing to consider the suggested alternatives.
If not, it's their money and they can have the UI they want, but I can have
the data structures that represent the data correctly. You can write queries
to give them the data structure they want.

 

[Susanne]

Let me know your thoughts if you've ever designed a form the way you
described with what I need. I've been out of programming for years and am
trying to get back into it and my brain is beyond fried, typical mom jumping
back into the working field in an industry that changes too fast ... too much
info I know.

Think of an Excel spreadsheet with 3 columns. Everything entered into this
spreadsheet (components) is related to a piece of equipment. The first
column has each component listed (a list). Some components are listed more
than once since some components are used mulitple times (at once) on the
equipment. The second column is where the component (part-serial number) is
selected based. Each of these are filtered out based on what part number is
associated with the component. The list then contains all the serial numbers
of that part.

Equipment #

|Component 1 | part-serial # |
|Component 2 | part-serial# left | part-serial# right |
|Component 3 | part-serial# left | part-serial# right |
....
|Component n| part-serial # |

Some of these components have one column beside them (in excel you would
merge the 2 fields together) and some have the 2 columns (a left and right
side).

The customer wants to see the entire form layed out in front of him: the
equipment info at the top, then each component and part-serial# below. I
need to know also which item was on the right, and which on the left.

 

[Peter Hibbs]

Susanne,

As the others have mentioned, it is essential to keep the basic data
in properly 'normalised' tables.

To display the data on a form in a way which is acceptable to the end
user you could use a Flex Grid Control which should do what you want
but you will need to write a bit of VBA code, have a look at my Flex
Grid Demo program for some examples.

Go to http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=180

HTH

Peter Hibbs.

 

[Albert D. Kallal]

What you can do is the following, but it is messy:


Build a union query that pulls together each field in a separate query.

eg:
select main_id, compounent1 as Compount from tblComp where compounent1 is
not null

union all

select main_id, compounent2 as Compount from tblComp where compounent2 is
not null

union all

select main_id, compounent3 as Compount from tblComp where compounent3 is
not null

union all .... etc. etc. etc.

Do the above as many of the un-normalized fields you have. You can now crate
a report in
which the main part of report is based on the one MAIN record, and a
sub-report can be based on the above union query.......

The above approach should get you around this problem until you can perhaps
try move forward and fix the design of the application. Remember, at the
end of the day so what if you got some problems in your design, we don't
live in a perfect world.

However, the problem that arises with a bad design is not so much that you
have a bad design, the problem arises when somebody asks you to build a
report, or make some small modification to the application. What happens is
the amount of time and effort it takes to accomplish that goal becomes too
long in terms of the effort worth to get a soltion. No one really cares
about some bad design, but they will care about how effectively and
efficiently and how easy it is to get a report out when you are asked to do
so.

If your data had been originally normalized correctly, then you could've
built the whole report with the wizard in less then 5 minutes with a few
mouse clicks. Now, you have to resort to building some complex union query
to get your desired results.

 

[Susanne]

Thanks. I have hopefully resolved my problem. I griped enough to my
"person" and I got approval to set up the form the way I need it so that I
can normalize the data better. Of course, I did insist that what we want to
do with reports and searches would be much easier. I'll just figure a way to
create the reports how I need them. It will be more intensive having the
layout right, but I can now do what I need to.

Something to keep under my cap for next time.