Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-20-2019, 05:55 AM   #1
Craig6
Newly Registered User
 
Join Date: Jun 2019
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Craig6 is on a distinguished road
Primary key in table

Ok, so this is my first post - hopefully I'm in the right place.
My DB form is for logging support emails. I have a primary key 1,2,3 etc and also a field where I want a reference to the support i.e. 2019-1-R
The 2019 is the date, 1 is the primary key and R is from a combo box on the form. The date is also on the form.
I have read about a few ways to do this and at the moment I have something similar to this in the control source of the text box;

= "[DateReceived] & "-" & [SupportID] & "-" & [SupportType]

This works as it puts what i want in the text box on the form however I can see that it doesn't populate that result in the table.

Am I going about this the right way. I did read about an update query but I could only get this to work when I updated it for all records i.e. it didn't seem to just automatically update after each record was created.

Hope that all makes sense. Thanks in advance.

Craig6 is offline   Reply With Quote
Old 06-20-2019, 06:37 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,665
Thanks: 38
Thanked 891 Times in 874 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Primary key in table

Hi. So, your table already has [DateReceived], [SupportID], and [SupportType], right? If so, then you don't need [DateReceived] & "-" & [SupportID] & "-" & [SupportType] in the table too because you can always just use the above expression in your form, report, or query to get the same value and you will never have missing values this way. If you really like to see them in the table, I suppose you could create a Calculated Column instead of a Text column and use the same expression as above. Cheers!
__________________
Just my 2 cents...

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.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 06-20-2019, 08:13 AM   #3
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,107
Thanks: 81
Thanked 1,601 Times in 1,485 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Primary key in table

To add another opinion in the mix, since we very often recommend use of queries rather than direct table usage anyway, and you have those items in a table, leave them separate in the table but together in the query.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 06-20-2019, 10:05 AM   #4
Craig6
Newly Registered User
 
Join Date: Jun 2019
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Craig6 is on a distinguished road
Re: Primary key in table

TheDBguy, thatís right all those are in the same table. The expression is just on a form which gives me what I want. This leaves the field in the table empty - is that right? I thought the answer to the expression would fill in the field in the same table.
The_Doc_Man, I tried that but couldnít get the field on my form to update the query.
Maybe Iím overthinking it.


Thanks both
Craig6 is offline   Reply With Quote
Old 06-20-2019, 10:17 AM   #5
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,665
Thanks: 38
Thanked 891 Times in 874 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Primary key in table

Quote:
Originally Posted by Craig6 View Post
TheDBguy, thatís right all those are in the same table. The expression is just on a form which gives me what I want. This leaves the field in the table empty - is that right? I thought the answer to the expression would fill in the field in the same table.
The_Doc_Man, I tried that but couldnít get the field on my form to update the query.
Maybe Iím overthinking it.

Thanks both
Hi. Typically, it is against Normalization Rules to store calculated values because once the source data changes, your stored calculated value becomes invalid. Hence, calculated values risk your data integrity. The new Calculated Column, I think, was created as a workaround for this rule. It's always been common/best practice to simply do the calculation in a query or wherever/whenever it is needed.

Good luck with your project.
__________________
Just my 2 cents...

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.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 06-21-2019, 07:03 AM   #6
Craig6
Newly Registered User
 
Join Date: Jun 2019
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Craig6 is on a distinguished road
Re: Primary key in table

Sorry, I think it's the way I've worded it. I don't want to put a calculation in the table. I just thought that if i put the expression into my form in a new field [ReferenceNo] then the answer would also save into my table. But i can see that it doesn't. I suppose it doesn't matter as long as the result (2019-1-R) appears on my form. I just hope that i can use that data for searches in the future;

= "[DateReceived] & "-" & [SupportID] & "-" & [SupportType]
Craig6 is offline   Reply With Quote
Old 06-21-2019, 07:05 AM   #7
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,665
Thanks: 38
Thanked 891 Times in 874 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Primary key in table

Quote:
Originally Posted by Craig6 View Post
Sorry, I think it's the way I've worded it. I don't want to put a calculation in the table. I just thought that if i put the expression into my form in a new field [ReferenceNo] then the answer would also save into my table. But i can see that it doesn't. I suppose it doesn't matter as long as the result (2019-1-R) appears on my form. I just hope that i can use that data for searches in the future;

= "[DateReceived] & "-" & [SupportID] & "-" & [SupportType]
Yes, you can use it in searches too, if you put the expression in a query instead of the form. Cheers!

__________________
Just my 2 cents...

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.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 06-21-2019, 07:29 AM   #8
Craig6
Newly Registered User
 
Join Date: Jun 2019
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Craig6 is on a distinguished road
Re: Primary key in table

Ahh, so I should put that expression into a query instead of the control source of the field on the form? When i did that before i couldn't get it to update the field. Can you point me in the right direction?

The plan is to add a record, choose a support type, pick a date and then my reference will update based on the data previously selected. I only need this as I can't give my colleague an ID no. (primary key) because it won't mean anything to them or me later on down the line.

Sorry if this is doing your heads in. I just want to make sure i start off right.
Attached Images
File Type: jpg Annotation 2019-06-21 162435.JPG (16.1 KB, 13 views)
Craig6 is offline   Reply With Quote
Old 06-21-2019, 07:45 AM   #9
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,665
Thanks: 38
Thanked 891 Times in 874 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Primary key in table

Quote:
Originally Posted by Craig6 View Post
Ahh, so I should put that expression into a query instead of the control source of the field on the form? When i did that before i couldn't get it to update the field. Can you point me in the right direction?

The plan is to add a record, choose a support type, pick a date and then my reference will update based on the data previously selected. I only need this as I can't give my colleague an ID no. (primary key) because it won't mean anything to them or me later on down the line.

Sorry if this is doing your heads in. I just want to make sure i start off right.
Hi. Whether you put the expression in a query or the form, you shouldn't/wouldn't be able to edit/update "it." So, I am not sure what happened with what you tried before. In any case, this is the correct way to do it. Namely, add the calculated columns in your query, and you can refer to them in your forms and report (and even recordsets). Cheers!
__________________
Just my 2 cents...

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.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 06-21-2019, 11:08 AM   #10
plog
AWF VIP
 
Join Date: May 2011
Posts: 9,312
Thanks: 10
Thanked 2,262 Times in 2,214 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Primary key in table

Stop with the coding for a minute and understand the big idea that the posters thus far have been trying to convey:

Your idea of an ID will not be stored in this database

Let that sink in for a minute. You can still accomplish your goal, just not in the manner you have set in your mind.

Your ID is comprised of 3 pieces of data that are stored in your database. You will not create a 4th field in a table to hold the ID you have envisioned, instead you will build a query and that ID field you envision will live there. Your idea of an ID is but a calculation, so when you need it, you will let Access calculate it.

When you need it on a form, you will use the 3 fields it is made of to calculate it and display it to the user. When you do so, that value will not be saved to any table. So, if your form properly displays the ID you envision, you are done, that form does nothing more with your ID than calculate and display it.
plog is offline   Reply With Quote
Old 06-21-2019, 12:39 PM   #11
Craig6
Newly Registered User
 
Join Date: Jun 2019
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Craig6 is on a distinguished road
Re: Primary key in table

Thanks Plog.
I needed it spelling out. I understand now.
Thanks all.

Craig6 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
Primary key in a table serves as a link to more than one table vyvy Tables 2 08-24-2011 06:30 AM
Using a table without a primary key? dmckie250 Tables 6 10-22-2010 06:06 PM
Automatically Adding a Record from a primary table to a secondary table VictorG Tables 5 08-24-2009 10:47 AM
How can i pass data from primary table to another table? kekewong Tables 1 04-14-2008 04:05 AM
[SOLVED] updating primary key values in one table using another table michaeluk Forms 0 12-13-2005 01:12 PM




All times are GMT -8. The time now is 08:07 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