Building my databae in Access 2013

Folks,
I am starting a database that will track my daily beginning and ending weight. In addition, there will be a sub form on each mealtime (Breakfast, Morning Snack, Lunch, Afternoon snack and Dinner. That part of the database IS not a part of this question.)
Here's my objective for now. On my form there is a date, beginning weight and ending weight. Below is the relations at this time.Date Weight relationshipWhat I am wanting to do is when I enter in a daily date, the associated record in the weight table will appear. If there is no associated record yet, I would like to create one to enter my beginning weight for that date. At the end of the day, I would call up the weight table by date and enter my ending weight for that day.
In other words enter in a date and call up an associated record from the weight table that has beginning and ending weight. If there is not a record created in the daily weight table I need to enter one. At the ending of the day I recall the weight table to record the ending days weight.
Frank FreeseAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
I don't see a reason for this to be two tables.  Logically you can have only one beginning and one ending weight for each day so combine the two tables into a single table.

DailyDateID (autonumber PK)
DailyDate (unique index)
BeginningWeight
EndingWeight


If you wanted the application to be able to track info for more than one person, you would add a person table and change this table as follows to add a compound index to implement the business rule that you can't log more than one set of weights per person per day:

DailyDateID (autonumber PK)
PersonID (unique index field 1)
DailyDate (unique index field 2)
BeginningWeight
EndingWeight
Frank FreeseAuthor Commented:
Pat,
I just realized I don't need two tables. Thanks for your help here. Great job
PatHartmanCommented:
To answer your question,
You need a search field that is separate from the bound date field.  You can use a combo if you want (sort it descending) or a text box (set it's format property to date so Access knows what type of data it should contain and also so you'll get a calendar popup if you need it)

You'll need code in the AfterUpdate event of the combo or search box to find the record.  If no record is found, you'll be on a blank record and you'll be able to add the date in the bound field and create a new record.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Frank FreeseAuthor Commented:
Thanks Pat - I've complete the first part on the above suggestion. I will open another question regarding the AfterUpdate event. OK with that?
Frank FreeseAuthor Commented:
thanks Pat - good job
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.