Access Database

[Mike]

This is the first of many questions believe me! The first question which most
concerns me is the following:

I am creating quite a unique database for a Steelwork Fabrications Company
that will give the standard prices of all gates we make for a large number of
openings. This, in turn has resulted in me creating 31 different sub menus
from the main menu, for all different types of gates that each contain 152
different sizes. When one of these buttons is clicked it will open a report
for that gate giving a breakdown of all the materials used, labour, finish
etc and totals it all up giving the overall price for the customer.

This i have worked out will result in me having to create 4712 reports that
are derived from 4712 queries to create these reports.

My question is, will Access allow me to create such a large amount of
reports and queries etc? Is there a limit to the amount of queries and
reports i can create?

I have already created two example reports for one gate, and when i click to
open them it already takes a while to open! I may put the database on a
future SQL Server to make it run faster, but there is no point if access 2007
will not allow me to create so many reports and queries...

Help....

 

[Stefan Hoffmann]

hi Mike,

This, in turn has resulted in me creating 31 different sub menus
from the main menu, for all different types of gates that each contain 152
different sizes. [...]
This i have worked out will result in me having to create 4712 reports that
are derived from 4712 queries to create these reports.

Working with an appropriate table design and sub reports, you should
never need 4712 individual reports or that many menu items.


mfG
--> stefan <--

 

[Hans Mol]

Your reports are based on a large nuber of queries, consider to base your
reports on sql statements which are contained in the reports, or on
statements with variables, so the large nuber of reports can be reduced.

 

[Pat Hartman]

It doesn't sound like you have enough data for the report to open slowly so
there is some problem with its design. We'll need more information to
resolve that issue.

As far as your reports/tables question goes - the data for ALL gates should
go into a single table. You should have ONE report of each type. Create a
form that has a combo and a button. the combo will allow the user to choose
the gate for the report and the button will run the report using the
OpenForm method and pass the gateID as criteria in the WHERE argument.

Does that simplify things a little bit

 

[Mike]

Hi Stefan,

Thanks for your reply, I partially understand what you mean, however, i have
designed the database for customers to view with me as i give them a gate
quote, this is why i have designed the main menu to open a gate quote sub
menu, with all the different gate types on it as buttons, for example: a
Westminster gate bowing upwards, Westminster straight top, Georgian gate, and
so on, up to 31 different types. With each of these gate types, there are 152
different sizes to fit any size opening.

Due to the different sizes, there are different amounts of materials needed
to make them at different prices... so every gate is different, therefore
every report will be completely different.

Here is a rough overview of my tables design:

Tables:
*Materials Table: (Containing all the Steel available for light industrial
work and prices),
*Patterns and Heads: (a table containing all the pre-made patterns and heads
that are welded into the gate that gives it its style)
*Labour: (price of hourly rates)
*Finish: (a table containing details on the various finishes for a gate eg
colour, galvanizing etc).

I have created relational queries for each gate to pull certain bits of
information out of each table such as the amount of steel needed, the hours
that it would take to make it etc for each gate and put the detail into a
totals report.

When you say subreports and appropriate table design, i don't think i fully
understand. I honestly am stuck on designing the database more efficiently.
It is quite unique! I would prefer the database to ask me what gate type,
when i type the name, and size opening it puts all the data together and
automatically puts it on to a report for me with the total price at the
bottom.

hi Mike,

This, in turn has resulted in me creating 31 different sub menus
from the main menu, for all different types of gates that each contain 152
different sizes. [...]
This i have worked out will result in me having to create 4712 reports that
are derived from 4712 queries to create these reports.

Working with an appropriate table design and sub reports, you should
never need 4712 individual reports or that many menu items.


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Mike,

When you say subreports and appropriate table design, i don't think i
fully understand. I honestly am stuck on designing the database more
efficiently. It is quite unique!

Your gates are not indivisible entities.

for example: a
Westminster gate bowing upwards, Westminster straight top, Georgian gate, and
so on, up to 31 different types. With each of these gate types, there are 152
different sizes to fit any size opening.

This information should be stored like that:

GateFamily(Id, Name): (1,Westminster), (2,Gregorian), ...

GateType(Id, Name): (1, bowing upwards), (2, straight top), ...

GateConfiguration(Id, GateFamily_Id, GateType_Id): (1, 1, 1), (2, 1, 2), ...

I would prefer the database to ask me what gate type,
when i type the name, and size opening it puts all the data together and
automatically puts it on to a report for me with the total price at the
bottom.

With these tables you can easily reduce your menu structure as Pat wrote
using ComboBoxes.

The GateType may be divided up into more pieces, eg. direction (upwards,
top,...) and form (bowing, straight, ...).


mfG
--> stefan <--

 

[Mike]

Thanks ever so much for yours and everybody elses help. There are some really
good suggestions, and the sql reports and combo suggestions have really
helped...just have to learn how to do them! The combo that is! The sql i am
Ok'ish with.

The only problem i have now with respect to this problem, is how i will put
every gate into a single table; the reason being is that every gate type uses
completely different types of steel such as flat, round, square, box etc. Not
to mention different timescales to make them, different patterns and heads
and so on. No gate uses the same type of material or size of material. There
are so many variables to consider. I orginally thought of creating a table
for each gate, but then i realised the different sizes for each gate
involved, so i thought of my current design where the raw information was
there such as all the steel we order and the prices, labour, finish and build
the gates from this. hope that makes sense!

So i would still have to at least create 31x152 (gate style x sizes) = 4712
reports. But at least i would not have to create the queries as well!

 

[Mike]

Just been re-reading your reply, how would i create a report that is flexible
enough to have any gate entered on to it and give a total for each? As i
mentioned earlier, there are so many varibles for each different gate....did
you mean one report only for which any gate could be entered?

I don't want to have to enter the details of each gate every time such as
amount of each material required etc, i just want to enter the gate name and
size opening and select the desired finish and have the database pull up a
pre-saved report with all the info on it and the price.

But the only way i can think of doing this is use the combo box option, one
for gate name - choose from the drop down, then a linked one for the size,
choose, then one for the finish and choose, then the appropriate report is
generated from the form. Or maybe i could have the button on the main menu
ask for these parameters then pull up a report?

 

[Pat Hartman]

NO - you only need to create a single report. At the time you run the
report, you specify the style and size and only that data will be selected
from the table.

 

[Pat Hartman]

If it takes 5 arguments to generate a specific report that's fine. If it
takes 10, that's probably still fine and certainly better than creating
thousands of reports and queries. You need to normalize your tables to make
sure that you are storing the data effectively.

Do some searching on cascading combos. that will lead you to posts that
explain how to use one combo to control the contents of subsequent combos.
Using a simple analogy - the country combo will control what appears in the
state combo (choose US and get 50 states, choose Canada and get 8(?)
provinces), choose the state and get just the cities in that state.

 

[Mike]

I understand where you are coming from, however, i still feel it isn't
possible to put every gate type and size into one table effectively due to
the fact that each type of gate uses completely different steel types and
there is 700 different types of steel we order alone, each gate also uses
different patterns or heads to be welded in to them and there are 4000+
different patterns etc. In addition to different timescales to make them and
so on. So i would have a table with 500+ columns or field names! To top this,
each gate and even the same type of gates are made smaller or larger to fit
various size openings. I suppose what i am saying is that a westminster gate
straight top for example can be made to 152 different sizes. This is the same
for the other 30+ types of gate each having 152 different size openings.

In addition to this the price of steel or patterns changes regularly, so the
reason i have created tables for steel, patterns and labour and so on, is so
i can update them whenever the price of 'X' changes. If each gate is in one
table and the price of say steel changes, i ould have to update all the steel
types and sizes individually. Whereas having all the steel in one table, the
patterns in another and so on, means i can update one table and everything
across the database will automatically update through one query.

I hope this makes sense also! Thanks again for all your help, perhaps i need
an expert in my area to help me face to face as it is almost impossible to
explain everything through text without actually seeing the database. Thanks
again though!

Putting things back to basics, what i want is a database that asks me when i
click a button, what gate type i want, then the size, then the opening and it
finds it for me and prices it, but is also updateable. In addition to this,
the current arrangement could potentially enable me to customize a gate,
because not all customers are happy with say the pattern in a standard
westminster gate, and with a table that has all the gates in it, the database
would be too inflexible...

Apologies for the essay! The combo box option has helped a great deal
because it will enable me to eliminate having so many sub menus.

Many thanks, any more advice is great, but i understand if this essay is
making you pull your hair out!!! Along with my limited knowledge on Access,
still learning!!!

 

[Lance]

Just because all your gates are in 1 table, doesn't mean all the data related
to them would be in that same table. You will need several tables, but only
1 query and 1 report.

The 700 types of steel.. becomes a 700 record steel table.
The 4000 types of patterns.. becomes a 4000 record pattern table.
The 152 different sizes.. becomes a 152 record size table, or an entry into
an order table... or a combination of both.

And then you link them to each other via keys.

If the price of a certain type of steel changes.. you update it ONCE in the
steel table.

 

[Pat Hartman]

Excellent, simple explanation Lance. So Mike, You can then create a table
of "stock" gates where you chose the type of steel, the pattern, etc. Or,
if you don't actually stock them pre-made, you create a custom gate for each
order by selecting its variable attributes.

Take a look at the Sears Catalog as an example. It lists, baby booties,
custom draperies, and riding mowers. Each has different attributes but they
are ALL items in the catalog table. So, I think you can manage to get all
your gates into a single table. There will most likely be related child
tables if multiple values for an item may be chosen. For example, fence
sections may vary in height so you may have to order 12 sections at 6 feet
and 10 sections at 5 feet. You couldn't put that in the gate table because
it is a repeating group and you have no way to know how many variations will
be ordered with gate.