Adding data in fields together

  • Thread starter Kristina H via AccessMonster.com
  • Start date
K

Kristina H via AccessMonster.com

Hi there,

I am trying to make a referral database for my boss.

I have two tables REFERRALS and CODES.

In REFERRALS, the table contains surname,name, date (that referral was sent),
and CODES contains codes that have corresponding timeframes (in days) like 7
days, 14 days etc. Eg. Code 11, FirstReportDue = 7, 2ndReportDue = 14 and
so on

Now what i want to do is make a calculation for an InitialReportDue field
store the result of Referrals.Date and the corresponding timeframes (eg
InitialReportDue = Referral.Date + Codes.1stReportDue). How on earth do i do
this? I really don't want to resort to VBA code seeing as i have the tables
set up already. Any help would be great. More than happy to chat via msn.I
need major help with this.

Krissy
 
A

Amy Blankenship

You can embed VBA code in Query Expressions, so in the Select row, you could
put

InitialReportDue: DateAdd("d", Code. 1stReportDue, Referral.Date)

You can use the Format function to further format the column if you want a
different date format.

HTH;

Amy
 
J

John Vinson

Hi there,

I am trying to make a referral database for my boss.

I have two tables REFERRALS and CODES.

In REFERRALS, the table contains surname,name, date (that referral was sent),
and CODES contains codes that have corresponding timeframes (in days) like 7
days, 14 days etc. Eg. Code 11, FirstReportDue = 7, 2ndReportDue = 14 and
so on

Now what i want to do is make a calculation for an InitialReportDue field
store the result of Referrals.Date and the corresponding timeframes (eg
InitialReportDue = Referral.Date + Codes.1stReportDue). How on earth do i do
this? I really don't want to resort to VBA code seeing as i have the tables
set up already. Any help would be great. More than happy to chat via msn.I
need major help with this.

Krissy
Click to expand...

If you have fields named FirstReportDue, 2ndReportDue, and so on, then
your table structure is incorrect. You're storing data in fieldnames.

I would say that you seem to have a one (referral) to many (reports)
relationship. A better structure would be to have TWO tables:

Referrals
ReferralID Autonumber Primary Key
Surname ' don't use Name as a fieldname, it's reserved
Forename
ReferralDate ' don't use Date either, it's also reserved
<etc>

Reports
ReportID Autonumber Primary Key
ReferralID Long Integer <link to Referrals>
Code

You can use the DateAdd() function to add the timeframe to the date:

SELECT Referrals.*, DateAdd("d", [Codes].[Timeframe], [ReferralDate])
FROM (Referrals INNER JOIN Reports ON Reports.ReferralID =
Reports.ReportID) INNER JOIN Codes ON Codes.CODE = Reports.Code
WHERE DateAdd("d", [Codes].[Timeframe], [ReferralDate]) BETWEEN Date()
AND DateAdd("d", 7, Date())

to show all reports due during the upcoming week.

John W. Vinson[MVP]
 
Top