MS Access- Query with Date Math

[mpfohl]

I could use some help. I'm using MS Access 2003

Long story short, I would like to create a query that displays only
bills that were introduced and passed in less than 2 months (Passed
date - Introduced date = less than 2 months).

Here's the details:
I have a table with the following columns: Bill Name, Action, and Date.
For example:

AB1, Introduced, 1/5/06
AB2, Introduced, 1/5/06
AB1, Passed, 3/5/06
etc.

Thus, there can be multiple entries per each bill. There will at least
be an 'introduced' action, but not all bills also have a 'passed'
action. I realize it would be easier to just have one record per bill
and have one column say 'introduced date' and another column for
'passed date', but my current setup is what is necissary for the rest
of my relational database to work so I need to figure out how to work
with what I have.

As for how the query displays the results, I don't care if it would
display just one record per bill (just displaying AB1, indicating it
matches my criteria). Or, if it is easier, the query results could
stay in the table's format above by showing two records per one bill so
it would look like:
AB1, Introduced, 1/5/06
AB1, Passed, 3/5/06

I basically just want to filter out bills that did not get passed in
less than 2 months.

I hope that all makes sense, any ideas?

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Perhaps:

SELECT I.[Bill Name], P.[Action], P.[Date]
FROM Bills AS I INNER JOIN Bills AS P ON I.[Bill Name] = P.[Bill Name]
WHERE P.Action = 'Passed'
AND I.Action = 'Introduced'
AND P.[Date] BETWEEN I.[Date] And DateAdd("m",2, I.[Date])-1

The last line checks to see if the bill passed within 2 months. You may
have to set the -1 to -2 to get the correct date range (less than 2
months).
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBREkY4IechKqOuFEgEQJbhQCcDOE70/MTEYtlazg9SAo8y7pnfloAoLJo
XxNVOlntefwuB8g0rF+fvBd3
=39+6
-----END PGP SIGNATURE-----

 

[Guest]

The basic layout of your query looks good. There is one problem; however,
and that is the calculation of the number of months. using months, you can
get a false result. For example:
datediff("m",#4/1/2006#,#6/30/2006#) will return 2, but it is actually 90
days.
I would suggest getting an business rule from the user that specifies a
number of days rather than a number of months and use the days in the
calculation.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Perhaps:

SELECT I.[Bill Name], P.[Action], P.[Date]
FROM Bills AS I INNER JOIN Bills AS P ON I.[Bill Name] = P.[Bill Name]
WHERE P.Action = 'Passed'
AND I.Action = 'Introduced'
AND P.[Date] BETWEEN I.[Date] And DateAdd("m",2, I.[Date])-1

The last line checks to see if the bill passed within 2 months. You may
have to set the -1 to -2 to get the correct date range (less than 2
months).
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBREkY4IechKqOuFEgEQJbhQCcDOE70/MTEYtlazg9SAo8y7pnfloAoLJo
XxNVOlntefwuB8g0rF+fvBd3
=39+6
-----END PGP SIGNATURE-----

I could use some help. I'm using MS Access 2003

Long story short, I would like to create a query that displays only
bills that were introduced and passed in less than 2 months (Passed
date - Introduced date = less than 2 months).

Here's the details:
I have a table with the following columns: Bill Name, Action, and Date.
For example:

AB1, Introduced, 1/5/06
AB2, Introduced, 1/5/06
AB1, Passed, 3/5/06
etc.

Thus, there can be multiple entries per each bill. There will at least
be an 'introduced' action, but not all bills also have a 'passed'
action. I realize it would be easier to just have one record per bill
and have one column say 'introduced date' and another column for
'passed date', but my current setup is what is necissary for the rest
of my relational database to work so I need to figure out how to work
with what I have.

As for how the query displays the results, I don't care if it would
display just one record per bill (just displaying AB1, indicating it
matches my criteria). Or, if it is easier, the query results could
stay in the table's format above by showing two records per one bill so
it would look like:
AB1, Introduced, 1/5/06
AB1, Passed, 3/5/06

I basically just want to filter out bills that did not get passed in
less than 2 months.

 

[mpfohl]

Very good point, though to be honest right now I don't need perfect
precision, having it be a rough '2 months' is enough for me.

One other problem in adapting your code though, and it is my fault. I
simplified the description of my table think i'd be able to adapt what
you sent but looks like i've managed to confuse myself and i'm not sure
how to adapt you code.

The column I called "Bill Name" was may attempt to combine the 3
columns needed to uniquely identify a bill.
It is BillType, BillNumber, SessionType. So my table really looks
like:

AB, 1, Regular, Introduced, 1/5/06
AB, 2, Regular, Introduced, 1/5/06
AB, 1, Regular, Passed, 3/5/06
etc.

So I need to tell the query that the first three columns need to be the
same before it tries to look for 'introduced' or 'passed'

Thanks again for the help.

 

[Guest]

In reality, you made it more difficult. I suggest you revert to the previous
layout.

 

[mpfohl]

I agree, it is more difficult, but that is how the data is sent to me,
unfortunatly.

 

[Guest]

You can combine fields in a query, but now I don't know on which fields you
are joining the two tables. It is imortant to know that to make it work.

 

[mpfohl]

There is only one table, "CurrentLeg"

The fields are: BillType, BillNumber, SessionType, Action, and Date.

The table has the following three fields set as primary keys BillType,
BillNumber, and SessionType. Thus, only when all three of these fields
are the same between records, can we say the two records are related.

Does that help?

 

[Guest]

Try this

SELECT I.[Bill Type], I.[Billl Nulber], P.[Action], P.[Date]
FROM Bills AS I INNER JOIN Bills AS P ON I.[Bill Type] = P.[Bill Type] AND
I.[Bill Number] = P.[Bill Number]
WHERE P.Action = 'Passed'
AND I.Action = 'Introduced'
AND P.[Date] BETWEEN I.[Date] And DateAdd("m",2, I.[Date])-1

 

[mpfohl]

Thanks everyone, she's cook'n now!