Table Relationships - Baffled

[Minty]

Ok - Firstly you have used lookup fields in your tables, your FOS (supervisor?) for instance in your Learners table.
I would strongly recommend getting rid of these for a large number of reasons many mentioned here http://access.mvps.org/access/lookupfields.htm
Just use your FOST table, don't add it as a look up to your table.
Get a naming convention in place and stick with it. Remove all the spaces and non alphanumeric characters - they will cause you additional typing and syntax errors going forwards. Putting a letter of on the end of things won't help you identify them very easily long term. Again have a read and understand of http://access.mvps.org/access/general/gen0012.htm

I'll have a further look later when time permits.
I'm also still not sure of your actual data layout. You seem to have quite a lot of unrelated data from my quick look through things.

 

[jdraw]

xyba,
In addition to Minty's advice:

If you have never worked through one of the tutorials on RogersAccessLibrary, I recommend you do --even if only as a refresher. It will take 30-45 minutes but you will learn the importance of a database design that meets your business requirements.

Most of the issues we see in the forum can often be attributed to problems with design of tables and relationships.
It is important to get your business facts organized and use them to identify and design/model your tables and relationships. You can test your evolving model and make changes before you have invested days/weeks/months into a "hastily put together database".

I recommend you work through 1 or 2 of the tutorials below. They do come with solutions, but to learn and experience the process you should work through the tutorials.

Class info
Consolidated widgets

Pay particular attention to how he uses a complete, but simple, description of the business.

Good luck with your project.

 

[xyba]

Ok - Firstly you have used lookup fields in your tables, your FOS (supervisor?) for instance in your Learners table.
I would strongly recommend getting rid of these for a large number of reasons many mentioned here http://access.mvps.org/access/lookupfields.htm
Just use your FOST table, don't add it as a look up to your table.
Get a naming convention in place and stick with it. Remove all the spaces and non alphanumeric characters - they will cause you additional typing and syntax errors going forwards. Putting a letter of on the end of things won't help you identify them very easily long term. Again have a read and understand of http://access.mvps.org/access/general/gen0012.htm

Thanks Minty. Those links were very useful. I've removed the lookup (didn't realise it was there) and renamed the forms and tables as suggested which makes a lot of sense. I've also removed the event coding to clean each form up a bit for now. I've also ungrouped the components for now and the attached is what I'm left with.

I'll have a further look later when time permits.
I'm also still not sure of your actual data layout. You seem to have quite a lot of unrelated data from my quick look through things.

Which data are you unsure of?

I'm grateful for any help you can give me as I am stuck right now.

 

[xyba]

xyba,
In addition to Minty's advice:

I recommend you work through 1 or 2 of the tutorials below. They do come with solutions, but to learn and experience the process you should work through the tutorials.

Class info
Consolidated widgets

Pay particular attention to how he uses a complete, but simple, description of the business.

Good luck with your project.

Thanks jdraw. I've downloaded these and will take a look when I get time.

 

[xyba]

Ok - Firstly you have used lookup fields in your tables, your FOS (supervisor?) for instance in your Learners table.
I would strongly recommend getting rid of these for a large number of reasons many mentioned here http://access.mvps.org/access/lookupfields.htm
Just use your FOST table, don't add it as a look up to your table.
Get a naming convention in place and stick with it. Remove all the spaces and non alphanumeric characters - they will cause you additional typing and syntax errors going forwards. Putting a letter of on the end of things won't help you identify them very easily long term. Again have a read and understand of http://access.mvps.org/access/general/gen0012.htm

I'll have a further look later when time permits.
I'm also still not sure of your actual data layout. You seem to have quite a lot of unrelated data from my quick look through things.

Been thinking, should I have a table for each referral and have a sub form for each of these tables in frmApprentice or would this not be good normalisation practice? I suppose it would make frmApprentice look a bit of a mess with 6 subforms too?

 

[Minty]

No that isn't correct - Sorry I've been really busy with the paying job this week so not had the opportunity to dig into your system at all so far. I will look as soon as I get the opportunity.

 

[xyba]

No that isn't correct - Sorry I've been really busy with the paying job this week so not had the opportunity to dig into your system at all so far. I will look as soon as I get the opportunity.

I thought it wouldn't be a good solution tbh.
I understand, whatever help you can give when you have time will be appreciated thank you.


Sent from my iPhone using Tapatalk