Rad Second Access Project to CRUSH!

[isladogs]

Disagree.

Forms that allow data changes (add/edit/delete) should be based on tables, not queries. 1 form, 1 table. This allows you to know exactly where the data on that form is going and always ensures the form can manipulate the underlying data

Sorry but I disagree with the above.
Whilst I can totally understand why Plog is saying that, I regularly work with forms used for editing data that are based on several tables.
As long as they are designed well, it isn't a problem.

Clearly you need to make sure the query isn't read only!
The more tables there are, the more likely it becomes read only.
But if it is read only, by definition you can't edit the underlying tables anyway

 

[Pat Hartman]

Forms should always be bound to queries whether they require multi-table joins or are just single table. I realize that not every application will be upsized to SQL Server or other RDBMS but binding forms to tables is one of the things that make DBA's hate Access with a purple passion. An access form bound to a table will quietly suck down every single row of the bound table 10 records at a time untill they are all local. Keep in mind that every copy of the app is doing the same thing. This puts a huge burden on the server that is completely unnecessary. So, start with designing for the future and benefit today. Use a query with a where clause that selects the minimum number of rows and columns to satisfy the requirement. No user actually needs to scroll through 50,000 records so don't waste the bandwidth to bring them all down. Better to run successive select queries to bring down one record at a time for updating.

 

[Mark_]

tgg1, one of the two items you listed up top is

I have one end user that wants to see the data with the "teams" current qualifications per "employee", as well as all information in Table 2,3,4,5 associated to that team.

Have you mocked up what that report should look like yet? If not, I'd get that user to spell out in detail exactly what they are looking for.

Many times an end user will assume you "know what they want". This can cause major headaches if you didn't design for some of their requirements from the start. Look to see if there is anything in your data that would be missing that is required to build their report. Also look to see if you will need to add more tables/relationships to support their reporting needs.

The last thing you want to have happen is you deliver them what you think they asked for and they say "But this doesn't show XYZ"... and you realize that will take a month to put in.