Dates with an IFFy situation

[Guest]

I am running a query for an A/R report (HEADACHE). The database first off
not normalized. The Primary Key would be the Inv#. There are several fields
similar, i.e., DepDate1, DepDate2, DepDate3....this would be on one record as
the customer would make several payments (not often, but possible). I have
tried everything to run a report showing ONLY deposits made for a specific
months.....BETWEEN [Key beginning Date] AND [Key Ending Date]. I use the
parameter under each depdate field as an OR. Works great with one exception,
if there is a date say dates are between 6/1/06 and 6/30/06, if there is a
deposit date in depdate 1 that is 6/1/05 it shows as DepDate2 would have a
date of 6/15/06. The customer does not want to see this 2005 date, they want
it to remain blank. I have tried everything from an IFF statement, crosstab,
I have run out of options and forums to resolve this problem.

PLEASE HELP BEFORE I BECOME COMPLETELY BALD!!

 

[Jeff Boyce]

Lena

Even though your input data is not well-normalized, there's nothing stopping
you from either parsing the data into a better table structure, or using a
normalizing (UNION) query to serve as your starting point.

You'll find it a lot easier to use Access' features/functions if you don't
feed it spreadsheet-organized data. Or you can work overtime to try to
force Access to accommodate data that doesn't match what it was created to
handle.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Thanks Jeff.

You have more or less reiterated what I have already figured out, been told
by several other programmers and other forums. Just stubborn thought I could
FORCE it to work. ) I will tell the client either accept it as is or
there will be a need to rebuild. (Database not originally built by
me....several fingers in the pie actually.)

Lena

Even though your input data is not well-normalized, there's nothing stopping
you from either parsing the data into a better table structure, or using a
normalizing (UNION) query to serve as your starting point.

You'll find it a lot easier to use Access' features/functions if you don't
feed it spreadsheet-organized data. Or you can work overtime to try to
force Access to accommodate data that doesn't match what it was created to
handle.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am running a query for an A/R report (HEADACHE). The database first off
not normalized. The Primary Key would be the Inv#. There are several
fields
similar, i.e., DepDate1, DepDate2, DepDate3....this would be on one record
as
the customer would make several payments (not often, but possible). I
have
tried everything to run a report showing ONLY deposits made for a specific
months.....BETWEEN [Key beginning Date] AND [Key Ending Date]. I use the
parameter under each depdate field as an OR. Works great with one
exception,
if there is a date say dates are between 6/1/06 and 6/30/06, if there is a
deposit date in depdate 1 that is 6/1/05 it shows as DepDate2 would have a
date of 6/15/06. The customer does not want to see this 2005 date, they
want
it to remain blank. I have tried everything from an IFF statement,
crosstab,
I have run out of options and forums to resolve this problem.

PLEASE HELP BEFORE I BECOME COMPLETELY BALD!!

 

[John Spencer]

You can use the normalizing UNION query approach.

SELECT Inv#, DepAmount1, DepDate1
FROM Table
UNION ALL
SELECT Inv#, DepAmount2, DepDate2
FROM Table
UNION ALL
SELECT Inv#, DepAmount3, DepDate3
FROM Table

Then use that as a source "Table" for your report.

If you can talk the client into rebuilding the database into a properly
normalized structure that will be a better solution.

Thanks Jeff.

You have more or less reiterated what I have already figured out, been
told
by several other programmers and other forums. Just stubborn thought I
could
FORCE it to work. ) I will tell the client either accept it as is or
there will be a need to rebuild. (Database not originally built by
me....several fingers in the pie actually.)

Lena

Even though your input data is not well-normalized, there's nothing
stopping
you from either parsing the data into a better table structure, or using
a
normalizing (UNION) query to serve as your starting point.

You'll find it a lot easier to use Access' features/functions if you
don't
feed it spreadsheet-organized data. Or you can work overtime to try to
force Access to accommodate data that doesn't match what it was created
to
handle.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am running a query for an A/R report (HEADACHE). The database first
off
not normalized. The Primary Key would be the Inv#. There are several
fields
similar, i.e., DepDate1, DepDate2, DepDate3....this would be on one
record
as
the customer would make several payments (not often, but possible). I
have
tried everything to run a report showing ONLY deposits made for a
specific
months.....BETWEEN [Key beginning Date] AND [Key Ending Date]. I use
the
parameter under each depdate field as an OR. Works great with one
exception,
if there is a date say dates are between 6/1/06 and 6/30/06, if there
is a
deposit date in depdate 1 that is 6/1/05 it shows as DepDate2 would
have a
date of 6/15/06. The customer does not want to see this 2005 date,
they
want
it to remain blank. I have tried everything from an IFF statement,
crosstab,
I have run out of options and forums to resolve this problem.

PLEASE HELP BEFORE I BECOME COMPLETELY BALD!!

 

[Jeff Boyce]

I'm not saying that the database has to be rebuilt. As John points out, a
normalizing UNION query can help you gather the data.

Another approach might be to ADD tables, and build queries that parse the
un-normalized data into a more usable form.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks Jeff.

You have more or less reiterated what I have already figured out, been
told
by several other programmers and other forums. Just stubborn thought I
could
FORCE it to work. ) I will tell the client either accept it as is or
there will be a need to rebuild. (Database not originally built by
me....several fingers in the pie actually.)

Lena

Even though your input data is not well-normalized, there's nothing
stopping
you from either parsing the data into a better table structure, or using
a
normalizing (UNION) query to serve as your starting point.

You'll find it a lot easier to use Access' features/functions if you
don't
feed it spreadsheet-organized data. Or you can work overtime to try to
force Access to accommodate data that doesn't match what it was created
to
handle.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am running a query for an A/R report (HEADACHE). The database first
off
not normalized. The Primary Key would be the Inv#. There are several
fields
similar, i.e., DepDate1, DepDate2, DepDate3....this would be on one
record
as
the customer would make several payments (not often, but possible). I
have
tried everything to run a report showing ONLY deposits made for a
specific
months.....BETWEEN [Key beginning Date] AND [Key Ending Date]. I use
the
parameter under each depdate field as an OR. Works great with one
exception,
if there is a date say dates are between 6/1/06 and 6/30/06, if there
is a
deposit date in depdate 1 that is 6/1/05 it shows as DepDate2 would
have a
date of 6/15/06. The customer does not want to see this 2005 date,
they
want
it to remain blank. I have tried everything from an IFF statement,
crosstab,
I have run out of options and forums to resolve this problem.

PLEASE HELP BEFORE I BECOME COMPLETELY BALD!!