Newbie, dying a death - please, someone..save me! (1 Viewer)

[adamwilson13]

Dear all, I know I risk getting the vitual 'hand-slap' and a telling off about how this should be in thread x,y or z, OR that I am breaking forum rules...I know many of you will be rolling your eyes into the back of my head at a perceived lack of etiquette, or knowledge etc, etc. .......

But I just need help! Please, anyone! I am very new to Access, but generally very technical - I am a graphic designer and low level web builder, so am not averse to slightly more technically demanding applications, or software etc.

That said, Access is killing me!

I will try to explain what I need to achieve, and if anyone can help - that would be most appreciated....and if not, I'll get there, hopefully....one day. I just have time against me as this is a piece of work I have been drafted with, but given practically no time to do it. Easy, I thought. How hard could it be - I wrongly assumed!

Anyway, in brief: I work for a university, in their contracts department.

I need to have a form that updates the relevant tables (behind the scenes) that allow users to see the various other universities that we have alliances with. Then also in the same form (sub form) see what current jobs are active and then again within yet another sub form, see all the relevant information related to each active job.

I have created the universities (Partner_Institutes) which contains a field for their name, country and whether they are 'New or Existing'. The form I created marries up with this well and is working a charm, showing all the demo data that I have created. Yay!

Then it gets trickier. Under each Partner institute we may have a a number of 'Planned_Action'. This could be things like: New Contract / Renewal Contract / Conversion Contract' etc. At any one time it needs to be possible to have more than one active 'Planned_Action' under each partner. I have created a table just for this and have this field set as a combo list which users choose from.

Then, for each 'Planned_Action' the user needs to see a whole host of other fields such as has all the relevant paperwork been submitted, when was the draft contract sent/returned etc, etc. I have created another table for this with lots of fields of various types (dates, combo lists, etc).

The trouble I have is that I have absolutely no idea how to link them all and to show multiple planned actions under the one partner, and then have each planned action show it's own status in the other fields. I have tried a few things and everything falls flat on it's face.

Any kind of input based on what I have said would really help. I have a basic grasp of queries.

 

[Minty]

Welcome to AWF!
I think before anyone will offer sensible help we will need to see your table structure and the relationships (If any) that you have set up. A picture paints a thousand words so a picture of them from the relationships window would be great.

If you haven't got the data storage layout correct everything after it becomes very hard to achieve, so instead of learning to ride on a unicycle lets go for the bike with stabilisers to get you going.

 

[jdraw]

Spend 30-60 minutes and work through this tutorial from RogersAccessLibrary.

He lays out a problem with a clear set of requirements, then leads you through a process to transform that "problem statement" to a data model that supports the stated "business". The data model consists of tables and relationships and serves as a blueprint to building the database. What you learn can be used with any database.

Getting your tables and relationships designed and tested to meet your requirements is critical to a database application.

Good luck.

 

[adamwilson13]

Hi all and thanks for the responses so soon. I have attached some screens. Please note that after trying many walkthoughs, and tutorials etc, I simply deleted all relationships and gave up!

Please note the far right hand table still has some of the fields that I have separated out into the other tables, but I left them in just incase I may have needed them for any of the links....

 

[jdraw]

With Access you'll find that by using a naming convention that does not allow embedded spaces in field and object names will reduce syntax errors and frustration. Use alphabetics and underscore(_) only.

You are not the first person to start with a form??? That may give you some general guidance to user interface design, BUT it is not where experienced developer start.

Spend 30-45 minutes on the tutorial I suggested previously.

 

[adamwilson13]

Thanks again for the response.

If time was a luxury and they let me sit down and watch a video at work then I would most definitely dive straight in, but I probably will not be able to do this in work hours, and use a mac at home so am a bit stuck.

I think they will have to accept that this will take me some time and will have to wait until i have the pre-requisite knowledge required to carry out the task.

 

[gemma-the-husky]

@Adam

the problem is that access is just not, well, accessible, in the way that excel is. A relatively uneducated user can make acceptable spreadsheets.

A database has a much steeper learning curve. There is no easy way you can jump right in without a lot of understanding.

What you have with access is in fact:
a series of spreadsheet tabs, where rows in one spreadsheet tab are linked to rows in other spreadsheet tabs by data common to certain columns.

In order to achieve this, every column as stipulated as being of a particular data type.

most importantly, the excel trick of being able to refer to the previous row in a spreadsheet is just not available. There is no previous or next record. You manage data by considering a "set" of data in its entirety. Any logic in your spreadsheet referring to other rows is no longer valid. You need
to consider how to solve your requirements in other ways.

It may not sound it, but because this set up is so powerful, users can cause problems by mishandling data. You can produce acceptable spreadsheets without using macros or code. You cannot possibly do this in access. You need code, and a lot of it. At least half the code we write (maybe more) is defensive, and is designed to limit what the users can do. Generally we remove the ability of all users to change the database logic.

Effectively, consider a spreadsheet where virtually every cell is locked, and instead you have to give the users a series of buttons to enable them to manage data in carefully controlled ways. This is effectively what access does.

To do this effectively, you need to go back to step 1, and analyse your data into the appropriate tables (spreadsheet tabs) and columns.

sorry not to be so helpful, but your bosses just can't throw this at you, and expect you to be able to handle it.

 

[adamwilson13]

completely understand and appreciate you taking the time. There is no quick fix, and further understanding will only benefit me. They shall have to wait!

 

[Minty]

I'm afraid in this instance you do need to get some planning in place first. Doing this now will not take long, but will make a working system. Your current data structure is miles out of a normalised layout and will give you no end of problems.

Take ten minutes to read about normalisation - get some paper and a pen and draw out your needs. You currently have a spreadsheet layout plonked straight into a table. http://allenbrowne.com/binary/Access_Basics_Crystal_080220_Chapter_03.pdf

As an example of a few things to avoid and redesign now;
You have lookup fields in your tables avoid like the plague - http://access.mvps.org/access/lookupfields.htm

Your Ready reckoner table has a whole host of fields that look like they will be storing dates that some sort of actions has taken place. You should have a table associated with the main table that has a couple of fields in it - Action, ActionDate. The list of actions would be another table here you can add as many actions as you need you pick the one you want store it and the date.

This may all sound a bit harsh and like you are being told to go to the naughty step, but it's not - it's advice that will mean you make a system that will work and scale in the long term, with all the benefits that comes with that.

 

[adamwilson13]

thank you this is so helpful! Not feeling like I am being told off! I fully accept that I understand very little in the area.

This gives me a great starting point! Thank you