Help with database/form design

Topic

New Reply

Hi everyone,

I need a hand in designing the forms and possibly table structure for a new database.

Basically:

[*]A measure has one or more versions
[*]Each version has one or more options
[*]Each option has costings against it

A costing is made up of:

[*]A year (and possibly month and year)
[*]A business area
[*]A ‘sub output’

Each costing has figures against it for a number of costing descriptions which can be either employee (FTE) or supplier related
For FTE, the overall figures are then allocated percentages to various employee grades (I have not yet built this into the database)

I’ve attached a figure of how I’ve setup the tables

I’ve also attached a screenshot of their current costing ‘tool’, which is an excel spreadsheet. Basically I’m trying to recreate this spreadsheet in an Access form as closely as possible. So in the spreadsheet, each business area is allocated a block of rows. Eg, Business Area 1 is allocated the pink set of rows, Business Area 2 is allocated the aqua set of rows and so on. FTE costs are just a number (Eg, the number of employees) and supplier cost has a number of items and also a dollar cost. The tabs down the bottom represent the different sub-outputs

I’ve started designing a form but am now stuck because it’s not working how I’d like and so I thought I’d get some input/suggestions.

Cheers,
28831-MeasureRelationship28832-MeasureTool

Reply | Quote

Replies

Hi Jason

While spreadsheets and databases have a lot in commen – they both dabble with data – the difference in processing will nearly always create 2 very different solutions.
your relationship diagram is quite hard to read – lines overlapping and one-many relationship lines going both left to rigt and vice-versa. Stick to putting the [one] on the left and the [many] on the right – or at least be consistant if you reverse it. This creates a visual data flow – makes the patterns easier to see.

OK – so here comes the questions . .
how many records do you expect in each of the tables?
how often would each table be updated?
which tables have records added – which have records changed.

your small reference tables make the relationship diagram look complicated but when correctly created will provide consistant data for – i presume – combo boxes in the form. if you draw out the main tables – sketch them on paper – and omit the reference tables for now – you should see the natural hierachy created by the one-many relationships. – Use this as your starting point for building your forms – the XLS sceenshot would be more like an output report – not an input form.

i assume that only data for the current year would be added so a data input screen would most likely only have a measure/version as a header record and maybe a subform with the costing data. How these are finally structured is very dependant on how data gets entered – obvious statement – but that detail is not covered in you post.
Finding a local access developer to help could be a good idea – as this level of development is very difficult in this environment – that said – it is a great place to get some good tips and solve individual problems.

Your relationships diagram does show that you have an understanding of how to put structures together – thats a very good start.

One very neat table I create for every project I do is my TYPES table

I call it t_TYPES . . my syntax carries over from 70s Cobol programming
it has a key field
TY_KEY . . number . . i pre-fill the table with about 50 records – numbered 0-50
the key field is common to each of the other fields.
but the other fields are independant of each other.
these fields are the “lookup” values . . like your “option status”
I would call it
TY_OPTION_STATUS Text/Number as required
For each other reference table you have I would create a seperate field
then you end up with one table –

( I use a standard – pre-built form – copied into each project to update the data – saves a lot of development time)

taking a pair of fields – one is always the TY_KEY and a value field you have the equivalent of your individual reference table.
so all your little reference table get neatly packaged into one . .
some caveats . . the relational integrity is not a tight as your schema . . and it really only works where a reletively small number of constant – or very slow changing values exist for each field. never delete any records – adding is ok. when using fields in a comb list – use NotNull as a criteria to elliminate blank values.

hope this helps – at least a little.
cheers
paul g

Reply | Quote

One very neat table I create for every project I do is my TYPES table

Paul

Can you have another go at explaining what this table is for. I just don’t follow.
Are you saying that you consoldiate all the various lookup tables into this one table?
Can you post an example perhaps.

Reply | Quote

Hi John
I have attached a sample DB. ( i think – new to doing this )
It contains a sample types table – 2 sample queries showing how each column operates independently and the maintenance form. I find this to be very useful in eliminating lots of small tables which are only used to fill combo boxes etc.
Feel free to use it if you like . .

cheers PaulG

Reply | Quote

Basically:

[*]A measure has one or more versions
[*]Each version has one or more options
[*]Each option has costings against it

As you have used a plural for costings, this seems to mean: Each option has one or more costing against it
Or does it mean :Each option has a costing against it.

It is an important question, because each time you say : “for one of these I need multiples of those” you add an extra “level” to a database and that has a big impact on form design.

If a form shows a single record for top level table, it can have a subform showing multiple records at the next level. But if a form is a continuous form (i.e. it shows multiple records on the screen at the same time) you can’t have another subform within it.

So you could have a form that showed you a measure and its various versions. But you can’t then add the display of multiple options for each version, and neither can you then add another subform to show multiple costings.

Now there is a trick or two that lets you extend this a little bit, but not twice.

Reply | Quote

Thanks Paul

That worked.

I have the habit of putting each lookup in its own table, so this would be quite a change for me.
How do you handle referential integrity with the relationships? I could ensure that the value used points to a value in t_Types, but not necessarily to one that has been used for the current purpose?

Is there a reason that the values used within each type don’t just fill from the top? Jobs for instance? Have you perhaps deleted values?

Reply | Quote

Hi John
I mentioned earlier that the “referential integrity” is not as tight as when using seperate tables . . but i deal with that by tightening up the way types table is updated and how the combo boxes get populated.

essentially – if integrity is very important then keep the data seperate – its a bit of a trade-off really.

the gaps may have been left intentionally to allow other values in the future. – my rule is to not delete data values – maybe change them indicate they are defunct.

also – because the FK value can only be entered by using a combo box – only valid entries can be made. ( my rule of data entry only occurs in forms)

if the FK value is zero – it usually displays a TBA . . indicating a selection needs to be made.

if you tested the form – you would notice that it has allowdelete = no and allowaddition = no. records can only be added – programatically. this ensures record integrity . . but as we all know if someone wants to stuff things up . . they will.

hope jason is getting some benefit from this discussion – feel like we have hyjacked his post . .

cheers Paul G

Reply | Quote