Date Table with Check Condition

[Gus]

Hi!

Thanks for taking the time to help. I appreciate it.

I have a DB that I am modifying I have added a table that is called
(tbl_Special_Billing_Requirements) it contains the following attributes:

User_ID, SP_num, Agr_Num, Vend_Code, Special_Billing_Requirements, Cycle.

I need to have a way to allow Users to add up to 4 years with each year
having 4 dates identified to belong to that year. I need to allow them the
capability to check or specify when a date for a certain year has been
completed.

I also need to be able to pull this data out in a report to show either what
has been completed and not. My question is what would be the best way to
structure (tbl_Special_Billing_Requirements) and the table or tables
capturing the date information.

I would really appreciate some help.

Thanks.

 

[KARL DEWEY]

I need to have a way to allow Users to add up to 4 years with each year
having 4 dates identified to belong to that year. ..... My question is what
would be the best way to structure (tbl_Special_Billing_Requirements) and the
table or tables
capturing the date information.
It sounds like you want a second table to contain 16 records per entry in
tbl_Special_Billing_Requirements. Is this correct?

What data besides a date will the 16 records contain?

Which of these fields - User_ID, SP_num, Agr_Num, Vend_Code - will the date
records be related?

 

[Jamie Collins]

I need to have a way to allow Users to add up to 4 years with each year
having 4 dates identified to belong to that year. I need to allow them the
capability to check or specify when a date for a certain year has been
completed.

ANSI-92 Query Mode syntax:

CREATE TABLE Users
(
user_ID VARCHAR(10) NOT NULL UNIQUE
)
;
CREATE TABLE UsersSpecialYears
(
user_ID VARCHAR(10) NOT NULL
REFERENCES Users (user_ID),
year_start_date DATETIME NOT NULL,
CONSTRAINT special_year_start_date__must_be_year_start
CHECK (year_start_date =
DATESERIAL(DATEPART('YYYY', year_start_date), 1, 1)),
UNIQUE (year_start_date, user_ID),
seq INTEGER NOT NULL,
CONSTRAINT special_year_seq__value_range
CHECK (seq BETWEEN 1 AND 4),
UNIQUE (user_ID, seq)
)
;
CREATE TABLE UsersSpecialOccasions
(
user_ID VARCHAR(10) NOT NULL
REFERENCES Users (user_ID),
year_start_date DATETIME NOT NULL,
FOREIGN KEY (year_start_date, user_ID)
REFERENCES UsersSpecialYears (year_start_date, user_ID),
occasions_date DATETIME NOT NULL,
CONSTRAINT special_occasion_date__no_time_element
CHECK (occasions_date = DATEVALUE(occasions_date)),
CONSTRAINT special_occasion_date__within_year
CHECK (DATEDIFF('YYYY', year_start_date, occasions_date) = 0),
UNIQUE (user_ID, occasions_date),
seq INTEGER NOT NULL,
CONSTRAINT special_occasion_seq__value_range
CHECK (seq BETWEEN 1 AND 4),
UNIQUE (year_start_date, user_ID, seq)
)
;
CREATE TABLE UsersSpecialOccasionsCompleted
(
user_ID VARCHAR(10) NOT NULL,
occasions_date DATETIME NOT NULL,
UNIQUE (user_ID, occasions_date),
FOREIGN KEY (user_ID, occasions_date)
REFERENCES UsersSpecialOccasions (user_ID, occasions_date)
)
;

Jamie.

--

 

[Gus]

Hi!

Thanks. I think I should give you some more information, so that you can
have a better idea of what I am trying to accomplish. Users (tblUser(UserID,
User_FName,User_LName, Email, Team) enter information into the
tblSpecial_Billing( User_ID, SP_Num, Agreement_Num, Vendor Code,
Special_Requirement, Cycle ).

I need my new table to capture tbl(Date, Year, Completed) iformation. For
example a user can have one agreement with up tp 4 years and each year may
have a date tied to it (e.g agr# 1321212 : (2007 10/27/2007), (2008
10/28/2008), 2009 09/27/2009), 2010 11/14/2010. I need to have each of these
dates tied to a checkbox that tells when one date and year has been completed.

It would be ideal if the database allowed even more than this amount of
years and dates. I am thinking I have a many to many relationship in my
tables special billing and date/years table.

Thanks. Hope this gives you more background and allowes you to further your
guidance and direction.

Thank you.

 

[KARL DEWEY]

It would be ideal if the database allowed even more than this amount of
years and dates.
You are thinking in spreadsheet terms. Your new table should look like this
---
tbl_Billing_Dates ----
Billing_DateID - autonumber
Agreement_Num - text or number to match that of tblSpecial_Billing table
Billing_Date - DateTime
Complete - Yes/No

Use form/subform Master/Child linked on Agreement_Num field.

 

[Gus]

Thanks. I created my table and did some testing and it seems to be working, I
also created a form and a subform and it is able to track many years and
dates as well as whether one, many or all have been completed. I thought
about using the Agr_Num , but because of how the DB works I was running into
some problems.

I really appreciate your help. I was over thinking my new table
tblBilling_Year_Date( Year_Date_ID, Year, Date, Completed, SP_Num).

Thank you.