Duplicate fields with unique values (1 Viewer)

I have a table with a set of fields that repeat in some record, and , other field have unique data on it.

I would like to find a way on the Forms to display(data entry locked) the Repeating data and list each of the unique data as separate textboxes.

Can somebody please help me find an easy way to solve this?

Table:
Data1 data2 data3 data4
Eats Fruits apples 101
Eats Fruits grapes 102
Eats Vegetables beans 111
Eats Vegetables peas 112
Drinks Cold orange 201
Drinks Cold soda 202
Drinks Hot coco 211
Drinks Hot tea 212


Thank you

niranjeen,

Research database NORMALIZATION
See this link for more info.

Good luck with your project.

what is the rule you have, based on the data you presented, for having duplicate.

The Data1 and Data2 repeat in many cases, but Data3 and Data4 are more or less unique.

Consider each row is a record. Data1, Data2 & Data3 are text while Data4 is a number

Big picture this for us:

1. What is the purpose of your database? What real world entities does this model? Do not use any database jargon in this answer, pretend you are telling a 8 year old what it is you do.

2. What is the point of the form? You've said what you want it to display, now tell us what larger issue this serves.

3. Show us what you want the form to show. You've given us data from your table, now using that as the source data show how/what the form should display. How many input areas? What kind? What values?

Thanks for your input.

Yes, there is more to what is said here.

The database hold an awful amount of information on projects with activities and milestones.

The projects have many activities and activities have many milestones, up until here it's doable. The monkey ranch here is each milestone will have multiple financial transactions. The financial transaction only relate to the milestone.

I had a way to sub table the entire structure until milestone, but when got to the financial transactions needed to record the transaction and should also add(summed) up to activity and then to project level. Here is where I need help.

In a financial table the project, activity and milestone have to be identified with a total. So I can pull totals from each record to add them up level by level.

Thanks in advance.

Pretty sure jdraw nailed it. I think what you posted about initially is the tip of the iceberg all relating to a poor table structure.

Can you post a copy of your database?

Here attached is the Table structure.

Yeah, big issues. You need to fix your tables before you move on to forms. In fact, forms are the last thing to work on. After you get your tables set up you should work on Reports to make sure you can get the data out of your database like you need.

Read up on normalization (https://en.wikipedia.org/wiki/Database_normalization). That's the process of properly structuring your tables. I see a lot of things wrong with your tables, I will hit on the big items:

Improper Use of Primary keys. Primary keys (e.g. ProjectName.ID, ProjActivity.WAID, ProjDetials.PMID) are used to connect tables. The primary key of one table becomes a foreign key in another table, thus linking records in the two together. You have primary keys in your table (as listed above), but you are not using them. You have ProjName as the link between ProjectName and ProjActivity, that is incorrect. There should be no ProjName field in ProjActivity, instead you should have a ProjID field in ProjActivity and store the ID value from ProjectName there. You did this with ProjDetials as well.

Storing Values in Field Names. In Funding you have a ton of fields storing forecast data (e.g. 0FY01Q1Forecast) which should not be in that table. They should be in a whole new table set up to accomodate the data. Data needs to be accomodated vertically (with more rows) and not horizontally (with more columns). Every piece of data that is jammed in those field names need to go into their own records of this new table.

You've got 12 fields for that forecast data. All of them come out and go into a new table with just 5 fields:

Forecast
ForecastID, autonumber, primary key
TransID, number, foreign key to Funding.TransID
ForecastFY, number, this will hold the fiscal year value that is currently in the field name
ForecastQuarter, number, this will hold the quarter value that is currently in the field name
ForecastValue, Currency, this will hold the actual value stored in the field right now

So instead of 1 record with 12 columns for your forecast data, you will have 12 records in this new table to accomodate it.

You've stored values in field names in quite a few places (TotHr_Qtr4, 1stFundDt, etc.)

So, read up on normalization and do it to your data. Once done, complete the Relationship tool again and post back here your new database.

Wow, that's a lot of redo. I'll get back on Sunday afternoon. Thanks again.