Database problem -

[Fostertrident]

I am currently trying to build a database - this database is for groups we work with around the country. the number of groups in place will always be a maximum of 152. These groups will have had different people running them over the years. So I hoped to make a database where you could choose from a drop down one of the 152 records and then choose which year i.e 2013/14 2014/15 2016/17 etc and then you'd have a tabbed form with contact details/approval status/etc

Now, I've managed to get the combo box to access the right record for the contact details, but for the approval status this entails data entry and I can't get it to write any data to the appropriate record - it states the record set is unupdateable.

There are Three Tables:

1) RAW - this is where the contact details are accessed
2) approval - this is a table with all the 152 areas listed and with a succession of Yes/No fields etc
3) area list -which is what the Combo box to navigate the records looks at

I created a query and made sure there are relationships so the areas are the common key across the records, when I write into the approval table I can see the result when I look at the record on the form I created, but I can't seem to add data via the form to write to the Approval table, what might I be doing wrong, is what I'm trying to do even possible?


Many thanks, this is my first post, so please do let me know if I've asked in an unhelpful way.


Looking forward to hearing from you.

[orange]

What is the "business" involved? What is the subject matter? i see Group, Location, Year, Approval --but nothing to relate them to one another.

[Fostertrident]

The thing that links all the three tables is "area" there are 152 areas, so when I click on a area in the combo box, that brings up the contact details of that area, I then want to click on the approval tab and when I'm told this has been approved enter a date which should write to the approval table, but that's not happening a the moment.

[aytee111]

The form being "unupdatable" is due to the record source being based on a query which doesn't allow updating (some queries are like that). Usually a data entry form is based on one table, with comboboxes when selections are required (such as contact details). These are then bound to the ID.

Note that in creating an Access database one needs to first design the table structure. A common error is to design the forms and then try and make the tables fit in with that design. Hence our need to know the underlying structure, then we can base our help on the foundation that has been laid.

[Fostertrident]

The table structure would be:

RAW - this is data coming from a web form which we will import - this is where contact details for each area will come from.

Approval - this is where we will have some manual entry, where a user will need to add dates when things have been approved and marked as ready for payment.

THe form will sometimes need to have elements referenced from RAW and fields from the Approval table for data entry. I would want the end user to navigate all records using a form, that's what I thought access (and all databases could do) is referencing from one table and inputting new data into another not going to be possible?

[aytee111]

Approval is the output - what data do you need from previous records? The contact ID will be a combobox based on table RAW. You could have a cascading combobox where they first select the area then a contact in that area. Navigating thru the records is usually done via a combobox and filter - they select a record based on some description or something that will identify that record and then you filter the form to that record. You could also have a search feature, a textbox for instance. There are many ways of doing this.

[orange]

Fostertrident,

Here's a link to information that could be quite useful to you re database planning and design.

Good luck.

[Fostertrident]

Thanks, feel a bit deflated by some of the previous messages as I'm not quite sure why my plan is flawed. Hopefully the resources you've kindly linked to will shine some light on it.

[Fostertrident]

Thinking more about it, I suppose what I need is a work flow database, we process 152 applications from the same 152 areas, yet the contact details change each year. The approval part of the database is where an end user marks certain elements as complete, with some automation occurring when certain things are checked, such as an email/etc.

[aytee111]

So you will need a table for areas, a table for contacts, a lookup table that carries the area ID, the contact ID and the from/to dates. The element I assume is what is approved and will be on another table, which will carry the approval info.