Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-15-2018, 11:17 AM   #1
Niranjeen
Newly Registered User
 
Join Date: Nov 2007
Posts: 24
Thanks: 4
Thanked 0 Times in 0 Posts
Niranjeen is on a distinguished road
Send a message via Skype™ to Niranjeen
Duplicate fields with unique values

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 is offline   Reply With Quote
Old 08-15-2018, 11:23 AM   #2
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 11,896
Thanks: 73
Thanked 1,962 Times in 1,910 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: Duplicate fields with unique values

niranjeen,

Research database NORMALIZATION
See this link for more info.

Good luck with your project.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is online now   Reply With Quote
Old 02-22-2019, 08:53 AM   #3
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,994
Thanks: 56
Thanked 2,247 Times in 2,156 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Duplicate fields with unique values

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

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 02-22-2019, 11:43 AM   #4
Niranjeen
Newly Registered User
 
Join Date: Nov 2007
Posts: 24
Thanks: 4
Thanked 0 Times in 0 Posts
Niranjeen is on a distinguished road
Send a message via Skype™ to Niranjeen
Re: Duplicate fields with unique values

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
Niranjeen is offline   Reply With Quote
Old 02-22-2019, 11:57 AM   #5
plog
AWF VIP
 
Join Date: May 2011
Posts: 9,252
Thanks: 10
Thanked 2,236 Times in 2,189 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Duplicate fields with unique values

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?
plog is offline   Reply With Quote
Old 02-22-2019, 12:41 PM   #6
Niranjeen
Newly Registered User
 
Join Date: Nov 2007
Posts: 24
Thanks: 4
Thanked 0 Times in 0 Posts
Niranjeen is on a distinguished road
Send a message via Skype™ to Niranjeen
Re: Duplicate fields with unique 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.
Niranjeen is offline   Reply With Quote
Old 02-22-2019, 12:51 PM   #7
plog
AWF VIP
 
Join Date: May 2011
Posts: 9,252
Thanks: 10
Thanked 2,236 Times in 2,189 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Duplicate fields with unique values

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?

plog is offline   Reply With Quote
Old 02-22-2019, 02:02 PM   #8
Niranjeen
Newly Registered User
 
Join Date: Nov 2007
Posts: 24
Thanks: 4
Thanked 0 Times in 0 Posts
Niranjeen is on a distinguished road
Send a message via Skype™ to Niranjeen
Re: Duplicate fields with unique values

Here attached is the Table structure.
Attached Files
File Type: accdb Database11.accdb (1.28 MB, 7 views)
Niranjeen is offline   Reply With Quote
Old 02-22-2019, 03:47 PM   #9
plog
AWF VIP
 
Join Date: May 2011
Posts: 9,252
Thanks: 10
Thanked 2,236 Times in 2,189 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Duplicate fields with unique values

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.
plog is offline   Reply With Quote
Old 02-22-2019, 04:25 PM   #10
Niranjeen
Newly Registered User
 
Join Date: Nov 2007
Posts: 24
Thanks: 4
Thanked 0 Times in 0 Posts
Niranjeen is on a distinguished road
Send a message via Skype™ to Niranjeen
Re: Duplicate fields with unique values

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

Niranjeen is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Merge Duplicate Values with a Unique ID accessca Queries 1 02-28-2014 08:56 AM
update query unique index contain duplicate values ss6857 Modules & VBA 0 12-15-2011 07:48 AM
Duplicate Values across unique records Klegg72 Tables 5 02-16-2011 01:32 AM
Initializing unique values for fields (Datasheet Form) jemartin80 Forms 13 01-12-2009 09:30 PM
Selecting duplicate fields from unique records in a single table dougmcc1 Queries 5 10-11-2004 08:16 AM




All times are GMT -8. The time now is 05:08 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World