Need help with multiple queries

[Guest]

I have a table with fields - Annual, Semi Annual and Two year. The user can
check appropriate one (Yes/no boxes). (I thought I need a separate table
with this info so the database would be normalized)

I am trying to create a query that will create a new field and add 1 year to
Annual, 6 Months to Semi-annual, and two years to 2 years. I have three
tables and queries with the part number data as one table, another table is
the last calibration date, the third is the frequency as described above.
How and when do I get the fields to calculate. This is the code I was going
to use, but do I put it in a different field, under the current field in the
criteria, or use a different code?? Thanks for your help.

 

[Guest]

Bonnie:

First, I would say that the design could have been done differently to give
you a normalized database. Having 3 fields for different time periods is
definitely NOT normalized.

However, that being said, I will try to answer your main question about
getting appropriate dates.

Look into using the DateAdd function

DateAdd(timeframetoaddhere, NumberOfTimeFrameToAdd, DateToAddTo)

So, for example if I wanted to display a column in my query to add a year to
a date, I would use

MyNewDateateAdd("yyyy", 1, [MyDateColumnNameHere])
--


Bob Larson
HTH

____________________________________
Access 2000, 2003, 2007, SQL Server 2000, Crystal Reports 10/XI, VB6
WinXP, and Vista

 

[Guest]

Thank you for your response. So, in this case, I should have a table for each
date? This database is for tracking calibrated tools. We have tools that are
calibrated annualy, semi-annualy, and every two years. How would you set up
the tables so it is "normalized".

Thank you again. Bonnie

Bonnie:

First, I would say that the design could have been done differently to give
you a normalized database. Having 3 fields for different time periods is
definitely NOT normalized.

However, that being said, I will try to answer your main question about
getting appropriate dates.

Look into using the DateAdd function

DateAdd(timeframetoaddhere, NumberOfTimeFrameToAdd, DateToAddTo)

So, for example if I wanted to display a column in my query to add a year to
a date, I would use

MyNewDateateAdd("yyyy", 1, [MyDateColumnNameHere])
--


Bob Larson
HTH

____________________________________
Access 2000, 2003, 2007, SQL Server 2000, Crystal Reports 10/XI, VB6
WinXP, and Vista

I have a table with fields - Annual, Semi Annual and Two year. The user can
check appropriate one (Yes/no boxes). (I thought I need a separate table
with this info so the database would be normalized)

I am trying to create a query that will create a new field and add 1 year to
Annual, 6 Months to Semi-annual, and two years to 2 years. I have three
tables and queries with the part number data as one table, another table is
the last calibration date, the third is the frequency as described above.
How and when do I get the fields to calculate. This is the code I was going
to use, but do I put it in a different field, under the current field in the
criteria, or use a different code?? Thanks for your help.

 

[Guest]

Without getting too much into this as I am swamped right now at work and
can't go into great detail at the moment, but this is kind of the idea (and
you can do a Google search on normalization for more info):

tblYourRenewals
RenewalID - Autonumber (PK)
PersonID - Long Integer (FK from Persons Table)
RenewalTypeID - Long Integer (FK from Renewal type lookup table)
RenewalDate - Date/Time (short date, date this WAS renewed, not when it will
be)

tlkupRenewalType
RenewalTypeID - Autonumber (PK)
RenewalTypeDescription - Text

tblPersons
PersonID - Autonumber (PK)
FirstName - Text
LastName - Text
MiddleInitial - Text
....other info

And so then you are able to store different types and you would then, be
able to use a query to determine the renewal type and the date they renewed
and then you can determine based on that what the next renewal date would be.

--


Bob Larson
Access World Forums Super Moderator
Utter Access VIP
____________________________________
Access 2000, 2003, 2007, SQL Server 2000, Crystal Reports 10/XI, VB6
WinXP, Vista

Thank you for your response. So, in this case, I should have a table for each
date? This database is for tracking calibrated tools. We have tools that are
calibrated annualy, semi-annualy, and every two years. How would you set up
the tables so it is "normalized".

Thank you again. Bonnie

Bonnie:

First, I would say that the design could have been done differently to give
you a normalized database. Having 3 fields for different time periods is
definitely NOT normalized.

However, that being said, I will try to answer your main question about
getting appropriate dates.

Look into using the DateAdd function

DateAdd(timeframetoaddhere, NumberOfTimeFrameToAdd, DateToAddTo)

So, for example if I wanted to display a column in my query to add a year to
a date, I would use

MyNewDateateAdd("yyyy", 1, [MyDateColumnNameHere])
--


Bob Larson
HTH

____________________________________
Access 2000, 2003, 2007, SQL Server 2000, Crystal Reports 10/XI, VB6
WinXP, and Vista

I have a table with fields - Annual, Semi Annual and Two year. The user can
check appropriate one (Yes/no boxes). (I thought I need a separate table
with this info so the database would be normalized)

I am trying to create a query that will create a new field and add 1 year to
Annual, 6 Months to Semi-annual, and two years to 2 years. I have three
tables and queries with the part number data as one table, another table is
the last calibration date, the third is the frequency as described above.
How and when do I get the fields to calculate. This is the code I was going
to use, but do I put it in a different field, under the current field in the
criteria, or use a different code?? Thanks for your help.

 

[Guest]

Thank you so much for your insight. I definately have my thought process
skewed. I will have to buy a book and try to begin to think like you gurus.
Again, thanks,

Bonnie

Without getting too much into this as I am swamped right now at work and
can't go into great detail at the moment, but this is kind of the idea (and
you can do a Google search on normalization for more info):

tblYourRenewals
RenewalID - Autonumber (PK)
PersonID - Long Integer (FK from Persons Table)
RenewalTypeID - Long Integer (FK from Renewal type lookup table)
RenewalDate - Date/Time (short date, date this WAS renewed, not when it will
be)

tlkupRenewalType
RenewalTypeID - Autonumber (PK)
RenewalTypeDescription - Text

tblPersons
PersonID - Autonumber (PK)
FirstName - Text
LastName - Text
MiddleInitial - Text
...other info

And so then you are able to store different types and you would then, be
able to use a query to determine the renewal type and the date they renewed
and then you can determine based on that what the next renewal date would be.

--


Bob Larson
Access World Forums Super Moderator
Utter Access VIP
____________________________________
Access 2000, 2003, 2007, SQL Server 2000, Crystal Reports 10/XI, VB6
WinXP, Vista

Thank you for your response. So, in this case, I should have a table for each
date? This database is for tracking calibrated tools. We have tools that are
calibrated annualy, semi-annualy, and every two years. How would you set up
the tables so it is "normalized".

Thank you again. Bonnie

Bonnie:

First, I would say that the design could have been done differently to give
you a normalized database. Having 3 fields for different time periods is
definitely NOT normalized.

However, that being said, I will try to answer your main question about
getting appropriate dates.

Look into using the DateAdd function

DateAdd(timeframetoaddhere, NumberOfTimeFrameToAdd, DateToAddTo)

So, for example if I wanted to display a column in my query to add a year to
a date, I would use

MyNewDateateAdd("yyyy", 1, [MyDateColumnNameHere])
--


Bob Larson
HTH

____________________________________
Access 2000, 2003, 2007, SQL Server 2000, Crystal Reports 10/XI, VB6
WinXP, and Vista


:

I have a table with fields - Annual, Semi Annual and Two year. The user can
check appropriate one (Yes/no boxes). (I thought I need a separate table
with this info so the database would be normalized)

I am trying to create a query that will create a new field and add 1 year to
Annual, 6 Months to Semi-annual, and two years to 2 years. I have three
tables and queries with the part number data as one table, another table is
the last calibration date, the third is the frequency as described above.
How and when do I get the fields to calculate. This is the code I was going
to use, but do I put it in a different field, under the current field in the
criteria, or use a different code?? Thanks for your help.