How do I flag up a record that is six months old?

[Guest]

I am writing a database for a garage. I need to produce a report that shows
when a service is due, six months after a service date.

 

[Guest]

In the query criteria add to the sevice date 6 months and check if it's
smaller then the current date

Select * From TableName
Where DateAdd("m",6,[service date]) < Date()

 

[TC]

Before you even /think/ about writing your forms & reports, you should
design your table structure to record the information accurately. You
do not do this on the basis of how you want the forms & reports to
look. Instead, you do it to reflect the actual nature & relationships
of the data that you have to store.

I suggest that before you go any further, you show us the tables that
you intend to create, and the names of the fields in each table (the
types & lengths don't matter here). Also, clearly show the primary key
field(s) for each table. If you don't know what a "primary key" is, you
need to do some reading on the topic of "database normalization",
before you go any further!

HTH,
TC [MVP Access]

 

[Joseph Meehan]

Before you even /think/ about writing your forms & reports, you should
design your table structure to record the information accurately. You
do not do this on the basis of how you want the forms & reports to
look. Instead, you do it to reflect the actual nature & relationships
of the data that you have to store.

I suggest that before you go any further, you show us the tables that
you intend to create, and the names of the fields in each table (the
types & lengths don't matter here). Also, clearly show the primary key
field(s) for each table. If you don't know what a "primary key" is,
you need to do some reading on the topic of "database normalization",
before you go any further!

HTH,
TC [MVP Access]

TC is offering some very good advice. You really need to make sure you
have the foundation correct before you start building. In Access this saves
a great deal of pain later on.

 

[Guest]

In response to TC, Database fields are:-

Name Text
Address Text
Telephone Text
Make Text
Model Text
Chasis No. Text
Work Done Text
Date ShortDate

 

[Steve Schapel]

TC [MVP Access]

 

[John Vinson]

In response to TC, Database fields are:-

Name Text
Address Text
Telephone Text
Make Text
Model Text
Chasis No. Text
Work Done Text
Date ShortDate

Well... that means you must either reenter the name, address, etc.
every time a person brings in their car, or else lose the history of
previous services on the car.

I'd suggest that you use (at least) three tables:

Customers
CustomerID
LastName
FirstName
Address
Telephone

Vehicles
VIN <or an autonumber VehicleID>
Make
Model
ChassisNo
CustomerID <link to who owns it>
<other info about the vehicle>

Services
VIN <link to Vehicles>
ServiceDate <don't use Date as a fieldname>
WorkDone


You may also want to have a table of standard services - LOF,
alignment, etc. etc. - linked to the Services table, so you don't have
to retype (and misspell, all too often!) the names of the services.

John W. Vinson[MVP]

 

[Douglas J Steele]

Picky point, but it's probably better to use

Select * From TableName
Where [service date] < DateAdd("m",-6,Date())

Access is smart enough to realize that DateAdd only needs to be run once in
the query above, while it would have to be run for each row in the table in
the query below.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

In the query criteria add to the sevice date 6 months and check if it's
smaller then the current date

Select * From TableName
Where DateAdd("m",6,[service date]) < Date()

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck

I am writing a database for a garage. I need to produce a report that shows
when a service is due, six months after a service date.

 

[Douglas J Steele]

Congrats, btw

 

[Guest]

Good point, thanks

Picky point, but it's probably better to use

Select * From TableName
Where [service date] < DateAdd("m",-6,Date())

Access is smart enough to realize that DateAdd only needs to be run once in
the query above, while it would have to be run for each row in the table in
the query below.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

In the query criteria add to the sevice date 6 months and check if it's
smaller then the current date

Select * From TableName
Where DateAdd("m",6,[service date]) < Date()

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck

I am writing a database for a garage. I need to produce a report that shows
when a service is due, six months after a service date.

 

[John Vinson]

TC [MVP Access]

Agreed! Great to have you on board, Tim!

John W. Vinson[MVP]

 

[Guest]

Thankyou all very much for your help and advice. This works fine.

In the query criteria add to the sevice date 6 months and check if it's
smaller then the current date

Select * From TableName
Where DateAdd("m",6,[service date]) < Date()

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck

I am writing a database for a garage. I need to produce a report that shows
when a service is due, six months after a service date.

 

[TC]

Thanks Steve

I'll have a website up soon with some personal info.

Looking forward to meeting you all in the mvp newsgroups!

Cheers,
TC [MVP Access]

 

[TC]

Thanks John!

Looking forward to meeting you all.

TC [MVP Access]

 

[TC]

Thanks Douglas

Looking forward to meeting you all in the mvp newsgroups.

TC [MVP Access]