Need Help with Form (2 Viewers)

[jlarks]

Hi,

I created a for using a table with 75 fields. I then created a form based on the table, then added a button to replicate form entries for updating new data. The problem is, I only new to create new data, such as meeting details i.e., date, attendance, notes. However, all data for all entries replicates, which causes duplicated records. How can I prevent this.

 

[plog]

I created a for using a table with 75 fields.

That's a lot of fields for a table that is properly set up. Also, "replicating data" is another sign of things not set up properly. If you have a bunch of data that is common and related, then perhaps you need another table just for that data with another table for the data that is different.

If your tables aren't set up properly, you're just wasting time building forms or reports. So, let's get your tables right first, then handle form issues.
Can you list all the fields and their data types?

 

[June7]

Provide your code.

Suggest you just provide database for analysis. Follow instructions at bottom of my post.

 

[The_Doc_Man]

@jlarks - based on your description, you need to do some serious reading on the topic of Database Normalization. This will help you understand how to avoid duplication and when/how to embrace it. You can look up "Normalization" in from here since we ARE a database forum. If you want to look at other, more general sites, remember to look for "Database Normalization" because in the general web, "Normalization" applies to math, chemistry, medicine, statistics, diplomacy, and databases ... several different topics. IF you go to the general web, the first couple of articles should come from .EDU domains. When you get more comfortable, you can tackle the .COM domain sites. Not because of .COM sites being easier or harder to understand, but because the .COM sites USUALLY have something to sell you and will be biased towards their product.

 

[jlarks]

Greetings and thank you all for your responses and instructions and advice, which I will follow. However, I will attach photos of the table files and form that I designed. I know that I should be using relationships, but I am having difficulty with setting them up. Please advise!

 

[plog]

First and foremost review this:

Use Snipping Tool to capture screenshots - Microsoft Support

Learn how to use Snipping Tool to capture a screenshot, or snip, of any object on your screen, and then annotate, save, or share the image.

support.microsoft.com

Second, why do you have 2 tables with the exact same structure? That's a big error. Instead, all that data just needs to go into one table.

Third, and least important, no special characters in field or table names. No colons, no slashes, no hashes, not even spaces. Only use alphanumeric characters and underscores in naming things in Access. Just helps you avoid headaches later on.

Fourth, I don't see any glaring errors with your data, but you've only showed me less than 1/3 of your fields. And I have no idea what this database is for.

So, can you go into Design view of your table, use the sniping tool as described above and capture all your field names and their datatypes? Also give me 2 paragraphs about your database. Paragraph 1 is about what it is your organization does--no database jargon. Pretend its career day at an elementary school and just explain to me what you do. Paragraph 2 can contain a little database jargon and should explain what it is this database will help you accomplish within your organization.

Ok, reread that last paragraph closely--I expect 3 things from you in the next post.

 

[jlarks]

I am having difficulties responding, I am receiving error messages saying that my response looks like spam.

I've used the clipping tool per your request, but I cannot send them at the moment, so I've attached screenshots of the files. Second, I do have all the data on one table, but as I've said in my original post, I've added a "duplicate" button on the footer or the form, which replicates ALL data entry and I don't need the "Prover Detail" repeated every time the form is used for new data entry, just for reference. Third, I'll revise the fields and Table Names, but that's going to take awhile and I have to present this for review in two hours. Fourth, I provide federal funding at the state level for programs that provide opioid use prevention treatment. I am using this database to monitor applications for funding, award amounts, and spending.

 

[mike60smart]

I am having difficulties responding, I am receiving error messages saying that my response looks like spam.

I've used the clipping tool per your request, but I cannot send them at the moment, so I've attached screenshots of the files. Second, I do have all the data on one table, but as I've said in my original post, I've added a "duplicate" button on the footer or the form, which replicates ALL data entry and I don't need the "Prover Detail" repeated every time the form is used for new data entry, just for reference. Third, I'll revise the fields and Table Names, but that's going to take awhile and I have to present this for review in two hours. Fourth, I provide federal funding at the state level for programs that provide opioid use prevention treatment. I am using this database to monitor applications for funding, award amounts, and spending.

It would be easier if you can upload a copy of the database with a few random records.

 

[LarryE]

You are a long long way from being able to present this to anyone for any reason and if you continue to use ACCESS as you would a spreadsheet, believe me, you will have nothing but problems. That said, based upon the fields you have in your tables and what you have presented on your form, as shown:

1. You have multiple Provider Types
2. Each Provider Type may have multiple Providers
3. Each Provider may have multiple Grants
4. Each Grant may have one or more Contract Date Ranges, Government Mandated Performance Requirements
5. Each Contract Date Range may have multiple Participant Meeting Dates, and Budgets/Invoices/Payments

Designing an ACCESS application always follows the business workflow. So if what I have described even remotely is close to correct, then at the very least you will need:

1. A ProviderType table holding all information about each type of provider
2. A Provider table holding all information about each individual provider
3. A Grant table holding all individual grant information
4. A Contract table holding contract date ranges, contract number etc.
5. A ParticipantMeeting table to hold all information about each meeting
6. A Budget table to hold all budget, invoicing and payment information

Relationships are then created between tables using Primary Keys and Foreign Keys.
As others have said, learn what data normalization means and how to create relationships. You are building a house, and the table design and relationship design is the foundation. You know what happens to houses built with faulty foundations.

 

[GaP42]

Before jumping in perhaps we need to understand what the OP was seeking - It is quite likely that this is essentially a form to show the status of a Program/Project at a meeting - essentially a report to a meeting. It is unlikely that the OP has or requires a full-fledged application that enables maintenance in their db application of all aspects of the project and associated meetings.
While the fundamental issue appears to be the ability to bring forward the previous meeting details to a new record for editing and publishing, the extent of what is specific to a meeting as opposed to the project need to be clarified.
Taking @LarryE 's suggestion around normalisation, the OP needs to make decisions about what data belongs to what tables that the OP/team have ownership of and will maintain.
eg I am not convinced that the Performance Requirements require a separate table if they are simply a recognition of the status of specific Performance Requirement at a specific meeting: eg the number of interviews scheduled/expected, conducted etc. Yes they could be allocated to a dedicated table, however they appear to be values dependent on the meeting/month
Similarly it does not appear that the full budget / payments are managed thorough this application: it may only be the current status and report by exception
And given attendance is a set of names typed in a text field, there is no concept of Contacts, meeting/project roles and need for an attendance table
However only the OP can advise and decide on the specific business needs and limitations.

 

[LarryE]

I know that I should be using relationships, but I am having difficulty with setting them up. Please advise!

The OP asked for assistance, and I gave it. I was trying to advance an idea more than anything else using his own field names so he might be able relate to what I was saying better. I don't know much about grants but I just used common sense based upon the forms. I also hope the OP can clear some of it up so a proper design can be completed.

 

[Pat Hartman]

@GaP42 The "form" the OP posted should be a "report" if it is not used for data entry. If it is used for data entry, then Larry has provided excellent advice on how to proceed. And I will add that each of those new ancillary tables will require a subform or perhaps a popup form to view/enter the data.

 

[GaP42]

Perhaps I was not clear enough in explaining this above @Pat Hartman - I think LarryE's posts about the relationship and normalising would be obviously useful to the OP to consider more deeply (I am not saying do not normalize). That is an appropriate activity, especially from what the OP has posted to date.
However when looking at the screen shots of the form posted by the OP there appear to be some items that are gathered together for reporting to a meeting. It is data entry and therefore a form, not a report.
For instance, the Government Performance and Results Act section could be supported by a number of tables to record and report the expected and actual interviews conducted in a period, including who was interviewed, who conducted the interview, any outcomes etc. Or it could simply be that the OP (or whoever uses the app) each month prior to the meeting would gather the data to enter as answers to the specific questions (as these obviously are part of the regular review of project progress) asking those responsible for supplying the answers. Even here there may be some simple ancillary tables to support the submission of interview data by month.
This is essentially a scope question for the development effort - which we have no idea of - it is really up to the OP to clarify. Given the apparently naive underlying data structure from the OP I suspect it might be more or less an attempt to have a personal desktop tool to gather and then report this standard data for multiple projects, and not an enterprise-level development. I am advocating caution that we do not overcook the OP and suggest a level of complexity that the OP cannot / has not the resources to maintain.

To apply normalisation to a set of data items you need to understand the real scope and gather the data requirements and processes to enable entry, reporting and management - but you know that.