UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Primary Keys, Data Validation And Repeating Values, Access 2016    
 
   
brastedhouse
post Feb 10 2019, 10:52 PM
Post#1



Posts: 55
Joined: 16-March 15
From: Chautauqua, NY


Hello all,

I need help with the following problem.

I am creating a database to record events. Each event has an event number (#) and an event date. The event number is sequential and is unique for a year. Then it restarts on January 1 of the next year. The date can repeat in a year as there can be more than one event on a given date. I need to figure out a way to have users enter a unique new event number each time they create an event and then be able to enter the date of the event.

So:
#1 January 2, 2017
#2 January 3, 2017
#3 January 5, 2017
#4 January 5, 2017
#5 February 5, 2017
#6 March 7, 2017
#7 March 7, 2017
#1 February 22, 2018
#2 February 28, 2018
#3 March 12, 2018
#4 March 12 2018
#5 April 12, 2018
#1 January 1,2019
and so on . . .

Using an autonumber field does nothing to help with the data validation issue. Or is there a way to code the entry of the event # so that it cannot repeat in any given year and then us an autonumber field? I have no experience in using data validation,but i suspect this is case where it might help?.

I would love any suggestions anyone might have.

Best, Scott
Go to the top of the page
 
MadPiet
post Feb 10 2019, 11:25 PM
Post#2



Posts: 2,828
Joined: 27-February 09



Save yourself the trouble and create a standard autonumber primary key and then use DMAX() to get the "next" value for the Year whatever. It's for presentation, and wide primary keys are a huge hassle when you want to create child records.
Go to the top of the page
 
projecttoday
post Feb 10 2019, 11:29 PM
Post#3


UtterAccess VIP
Posts: 10,605
Joined: 10-February 04
From: South Charleston, WV


Not sure I understand what you're saying but Access allows you to create a multi-field index (or a single-field index for that matter) which is unique. That means the user is not allowed to enter any combination of values which is already in the table.

--------------------
Robert Crouser
Go to the top of the page
 
brastedhouse
post Feb 10 2019, 11:54 PM
Post#4



Posts: 55
Joined: 16-March 15
From: Chautauqua, NY


Hi, thanks for the reply. Can I use DMAX to not allow the user to use the last number? I also thought about asking if there is a way to code to always choose the next number in sequence for a year. Can DMAX do this? The year will duplicate but the event # cannot duplicate in any particular year. Thanks, Scott
Go to the top of the page
 
nvogel
post Feb 11 2019, 05:20 AM
Post#5



Posts: 911
Joined: 26-January 14
From: London, UK


I think you should add the year as a separate column (yr in the following example) and make it part of a composite key.

CREATE TABLE tbl
(dt DATE NOT NULL,
yr INTEGER NOT NULL,
num INTEGER NOT NULL,
PRIMARY KEY (num,yr));

You can also add a constraint to make sure that yr specifies the same year as dt:

ALTER TABLE tbl ADD CONSTRAINT ck1 CHECK (yr = YEAR(dt));

Sadly Access's ACE engine only lets you add check constraints like that if you do it through DAO (go figure!). That's a pretty lousy feature of ACE but I highly recommend you avoid ACE altogether if you can. Use a standard SQL DBMS instead and the above will work just fine.
Go to the top of the page
 
cheekybuddha
post Feb 11 2019, 05:34 AM
Post#6


UtterAccess VIP
Posts: 10,908
Joined: 6-December 03
From: Telegraph Hill


>> I think you should add the year as a separate column (yr in the following example) and make it part of a composite key. <<

This would be ideal for a computed column

--------------------


Regards,

David Marten
Go to the top of the page
 
nvogel
post Feb 11 2019, 05:42 AM
Post#7



Posts: 911
Joined: 26-January 14
From: London, UK


With ACE, can you use a computed column as part of a unique/constraint index? I don't think that's possible in ACE but for other DBMSs it may be.
Go to the top of the page
 
cheekybuddha
post Feb 11 2019, 06:18 AM
Post#8


UtterAccess VIP
Posts: 10,908
Joined: 6-December 03
From: Telegraph Hill


AFAIU, computed columns can be indexed. Whether they can be used as part of a composite index, I'm not sure ...

--------------------


Regards,

David Marten
Go to the top of the page
 
brastedhouse
post Feb 11 2019, 05:52 PM
Post#9



Posts: 55
Joined: 16-March 15
From: Chautauqua, NY


what if i were to use say, the 2 digit year (that would be fine for 81 more years) and a sequential # as the event # and keep the date field and use an autonumber field for the pk? would that seem like the easiest solution and it avoids creating a separate year column which essentially creates duplicate data by adding the year and then the date to the same table. So:

eventID (record #/autonumber PK) 1
eventnumber (short text ) 19-1 indexed, no duplicates
date (long date) Saturday, February 9, 2019

2
19-2
Saturday,February 10, 2019

3
19-3
Saturday, February 10, 2019

4
19-4
Sunday, February 11, 2019

I do need to keep this as compliant as possible with mySQL (or similar SQL DBMS) because the client has expressed an interest in creating a web based version of this database in the future. So I would have to be able to convert it as seamlessly and easily as possible. No lookup tables, no multiple value fields, etc.

Right now they are set on Access.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    21st February 2019 - 11:04 PM