IsNot Null question


T

Tony Williams

How do I amend this code to check whether my field txtmonthlabel IsNot Null
and then only runs the rest of the code?
This code is on the OnClick event of a command button on a prompt form where
the user inputs a date range.
Private Sub cmdOK_Click()
Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mmmm\/yyyy\#"

strReport = "rptsense3"
strField = "txtmonthlabel"


If IsNull(Me.txtstartdate) Then
MsgBox "You must enter a start date", vbOKOnly, "Missing Start Date"
Me.txtstartdate.SetFocus
Else

If IsNull(Me.txtenddate) Then
MsgBox "You must enter an end date", vbOKOnly, "Missing End Date"
Me.txtenddate.SetFocus
Else
strWhere = strField & " Between " & Format(Me.txtstartdate,
conDateFormat) _
& " And " & Format(Me.txtenddate, conDateFormat)


If Not IsNull(Me.cmbselectcompany) Then
strWhere = strWhere & " AND cmbCompany = """ & Me.cmbselectcompany & """"
End If

DoCmd.OpenReport strReport, acViewPreview, , strWhere
End If
End If

End Sub
I only want the report to run for records in my table, tblmaintabs, where
the value of txtmonthlabel IsNot Null
Thanks
Tony
 
Ad

Advertisements

K

Keith Wilby

Tony Williams said:
How do I amend this code to check whether my field txtmonthlabel IsNot
Null and then only runs the rest of the code?

I haven't read the rest of your code but you could just wrap the whole lot
up into an if:

If Not Is Null(txtmonthlabel) Then

'Run my code

End If

HTH - Keith.
www.keithwilby.com
 
T

Tony Williams

Not quite there, probably I didn't give enough explanation.
What I am doing is working on quarterly statistics we get from external
companies. The field txtmonthlabel is the quarter to which the data relates.
Some companies send data every quarter others miss quarters. The report
needs to show only those companies who have submitted data in the quarter I
running the report against. So I thought that if the value of txtmonthlabel
was null for a particular company there would be no data and the company
wouldn't show on the report. However as I'm running the report over a date
range there may have been data in previous quarters but I only want
companies that have submitted data in this quarter. Can I do this by adding
a control on the prompt form where the user puts the date range and somehow
tie that control to equal txtmonthlabel?
Hope I've explained that.
Cheers
Tony
 
B

BruceM

A few things related to your code: In this line:
strField = "txtmonthlabel"
you are defining strField as the literal characters enclosed in the quotes.
If you mean to define the string in terms of a field or control:
strField = Me.txtmonthlabel
Same for strReport

For the rest, it is very difficult to figure out what you need. Perhaps the
user could select a quarter rather than a date range? What is the point of
the date range when what you need is information about a quarter? You say
that txtmonthlabel is the quarter, but what do you mean by that? Is it a
date, or an ordinal number (1st, 2nd, etc.)? Is the year included? Give an
example of the data actually stored in that field.
Are you using the prompt form to provide query parameters? If so, why is
the query returning records that do not meet the criteria? Does the report
include the actual statistics, or just the fact that there are statistics
for the quarter?
 
Ad

Advertisements

T

Tony Williams

What is the point of the date range when what you need is information about
a quarter?

What the report does is show the data that the company have submitted over
the date range that the user inputs. This is to check if the data that has
been submitted for that quarter is in line with previous quarters.
You say that txtmonthlabel is the quarter, but what do you mean by that?
Is it a date, or an ordinal number (1st, 2nd, etc.)? Is the year included?
Give an example of the data actually stored in that field.

The txtmonthlabel field is a date field and formatted as mmmm yyyyy so for
say the quarter ending 31st March 2006 would be shown as March 2006
Are you using the prompt form to provide query parameters? If so, why is
the query returning records that do not meet the criteria?

Yes the prompt form provides the date range for the query criteria for the
field txtmonthlabel
Does the report include the actual statistics, or just the fact that there
are statistics for the quarter?

The report shows actual statistics. However what I want is say the last 4, 5
or even 6 quarters (that's the date range variable) statistics for those
companies who have submitted statistics for this quarter. eg If I am in the
quarter March 2006 (the current value of txtmonthlabel) I want the report to
show me statistics for say March 2005, June 2005, September 2005, December
2005 and March 2006 BUT only for those companies that have submitted
statistics for March 2006. There will be companies who have submitted
statistics in other quarters but not March 2006 but I don't want to see
them.

Hope this explains it, but please stick with me and if there is anything
else I can add I am obviously happy to.

Thanks
Tony
 
B

BruceM

Your parameter could be something like:

Year([txtmonthlabel] = Year(Date()) And DatePart("q",[txtmonthlabel]) =
DatePart("q",Date) And Between [Forms]![YourForm]![StartDate] And
[Forms]![YourForm]![EndDate]

The part here that isn't so good is that the reports of data submitted
should be related to the company. In other words, there should probably be
a company table, and the quarterly data should be in a related table. If it
is set up this way, combine the two tables in a query and apply the criteria
as described above. If you need help with that you will need to describe
your table structure.

By the way, it took several posts just to clarify your needs. In future
postings (new threads in particular) you should supply enough information
that people reading the questions don't need to ask what you mean. That
uses more of everybody's time than is necessary.
 
T

Tony Williams

Thanks Bruce, sorry for the delay in replying but I'm in the UK

I have 3 tables in my table structure: tblcompany (holding company info),
tblmonth (holds the quarter dates), tblmaintabs (holds the data)
tblcompany and tblmaintabs are linked by company name and tblmonth and
tblmaintabs are linked by quarter date.

I know how to link the tables in the query but what I still haven't
understood is how does the criteria relate to the particular quarter that I
am working in? For example from my previous post if I am in the quarter
March 2006 how does the query criteria exclude companies that haven't
submitted data in that particular quarter?

Sorry if I'm missing the obvious but my 61 year old brain sometimes takes a
lot of time to grasp the obvious.

Also I take on board your comments on the lack of information in my early
postings. Big mistake to think you guys can read my mind. Do really
appreciate your help though.

Thanks again
Tony



BruceM said:
Your parameter could be something like:

Year([txtmonthlabel] = Year(Date()) And DatePart("q",[txtmonthlabel]) =
DatePart("q",Date) And Between [Forms]![YourForm]![StartDate] And
[Forms]![YourForm]![EndDate]

The part here that isn't so good is that the reports of data submitted
should be related to the company. In other words, there should probably
be a company table, and the quarterly data should be in a related table.
If it is set up this way, combine the two tables in a query and apply the
criteria as described above. If you need help with that you will need to
describe your table structure.

By the way, it took several posts just to clarify your needs. In future
postings (new threads in particular) you should supply enough information
that people reading the questions don't need to ask what you mean. That
uses more of everybody's time than is necessary.

Tony Williams said:
What the report does is show the data that the company have submitted
over the date range that the user inputs. This is to check if the data
that has been submitted for that quarter is in line with previous
quarters.


The txtmonthlabel field is a date field and formatted as mmmm yyyyy so
for say the quarter ending 31st March 2006 would be shown as March 2006


Yes the prompt form provides the date range for the query criteria for
the field txtmonthlabel


The report shows actual statistics. However what I want is say the last
4, 5 or even 6 quarters (that's the date range variable) statistics for
those companies who have submitted statistics for this quarter. eg If I
am in the quarter March 2006 (the current value of txtmonthlabel) I want
the report to show me statistics for say March 2005, June 2005, September
2005, December 2005 and March 2006 BUT only for those companies that have
submitted statistics for March 2006. There will be companies who have
submitted statistics in other quarters but not March 2006 but I don't
want to see them.

Hope this explains it, but please stick with me and if there is anything
else I can add I am obviously happy to.

Thanks
Tony
 
B

BruceM

It sounds as if you are using tblmaintabs as a junction table, since each
company will have many data (tblmaintabs) records, and each quarter will
have many data records. That reasoning is valid if you have students and
courses, for instance: each student can take many courses, and each course
can contain many students, so you need an enrollment table to resolve the
relationship. However, I'm not sure it's the best approach here, unless
there's more to tblmonth than I imagine.
From what I understand, you receive the information from a company on a
particular date. If so, DateReceived can be a field in tblmaintabs. As an
experiment, make a copy of your database (which I assume you do anyhow).
Remove all relationships, then create a tblData:

tblData
DataID (primary key, or PK)
CompanyID (foreign key, or FK)
DateReceived
TestData

This assumes CompanyID is the name of the PK field in tblCompany. Create a
one-to-many relationship between the CompanyID fields, then make a more
based on tblCompany and a subform based on tblData (autoform is handy for
these experiments). Create a few subform records for a few companies, with
dates from this quarter for some companies and not for others. For
companies with records from this quarter, create some records for previous
quarters. You can use the TestData (text) field to make notes for yourself
(e.g. Second quarter data; there are no third quarter data).

If this basic structure works, I'm afraid I need to tell you that I'm not
sure what to do next. It's easy enough to select a date range, as you know,
and you can select records from the current quarter, but what you need to do
first is to select records only from companies for whom the most recent data
received was in the current quarter. For those companies you want to see
all the records within the specified date range. However, if the most
recent data are for the past quarter, you don't want to see any data from
that company. I am intrigued by the problem, but have not been able to
devise a solution. I do know that I suggested the criteria in the last
posting based on my flawed understanding of your needs. I should have
understood better than I did based on what you wrote, but I misread it.

Unless somebody with a suggestion is monitoring this thread, your best bet
would be to start a new thread, including table structure and relationships.
The way I described tblData is a fairly conventional way of describing the
table structure. Sorry I couldn't be of more help, but I definitely want to
learn how to solve this.

Tony Williams said:
Thanks Bruce, sorry for the delay in replying but I'm in the UK

I have 3 tables in my table structure: tblcompany (holding company info),
tblmonth (holds the quarter dates), tblmaintabs (holds the data)
tblcompany and tblmaintabs are linked by company name and tblmonth and
tblmaintabs are linked by quarter date.

I know how to link the tables in the query but what I still haven't
understood is how does the criteria relate to the particular quarter that
I am working in? For example from my previous post if I am in the quarter
March 2006 how does the query criteria exclude companies that haven't
submitted data in that particular quarter?

Sorry if I'm missing the obvious but my 61 year old brain sometimes takes
a lot of time to grasp the obvious.

Also I take on board your comments on the lack of information in my early
postings. Big mistake to think you guys can read my mind. Do really
appreciate your help though.

Thanks again
Tony



BruceM said:
Your parameter could be something like:

Year([txtmonthlabel] = Year(Date()) And DatePart("q",[txtmonthlabel]) =
DatePart("q",Date) And Between [Forms]![YourForm]![StartDate] And
[Forms]![YourForm]![EndDate]

The part here that isn't so good is that the reports of data submitted
should be related to the company. In other words, there should probably
be a company table, and the quarterly data should be in a related table.
If it is set up this way, combine the two tables in a query and apply the
criteria as described above. If you need help with that you will need to
describe your table structure.

By the way, it took several posts just to clarify your needs. In future
postings (new threads in particular) you should supply enough information
that people reading the questions don't need to ask what you mean. That
uses more of everybody's time than is necessary.

Tony Williams said:
What is the point of the date range when what you need is information
about a quarter?

What the report does is show the data that the company have submitted
over the date range that the user inputs. This is to check if the data
that has been submitted for that quarter is in line with previous
quarters.

You say that txtmonthlabel is the quarter, but what do you mean by that?
Is it a date, or an ordinal number (1st, 2nd, etc.)? Is the year
included? Give an example of the data actually stored in that field.

The txtmonthlabel field is a date field and formatted as mmmm yyyyy so
for say the quarter ending 31st March 2006 would be shown as March 2006

Are you using the prompt form to provide query parameters? If so, why
is the query returning records that do not meet the criteria?

Yes the prompt form provides the date range for the query criteria for
the field txtmonthlabel

Does the report include the actual statistics, or just the fact that
there are statistics for the quarter?

The report shows actual statistics. However what I want is say the last
4, 5 or even 6 quarters (that's the date range variable) statistics for
those companies who have submitted statistics for this quarter. eg If I
am in the quarter March 2006 (the current value of txtmonthlabel) I want
the report to show me statistics for say March 2005, June 2005,
September 2005, December 2005 and March 2006 BUT only for those
companies that have submitted statistics for March 2006. There will be
companies who have submitted statistics in other quarters but not March
2006 but I don't want to see them.

Hope this explains it, but please stick with me and if there is anything
else I can add I am obviously happy to.

Thanks
Tony







A few things related to your code: In this line:
strField = "txtmonthlabel"
you are defining strField as the literal characters enclosed in the
quotes. If you mean to define the string in terms of a field or
control:
strField = Me.txtmonthlabel
Same for strReport

For the rest, it is very difficult to figure out what you need.
Perhaps the user could select a quarter rather than a date range? What
is the point of the date range when what you need is information about
a quarter? You say that txtmonthlabel is the quarter, but what do you
mean by that? Is it a date, or an ordinal number (1st, 2nd, etc.)? Is
the year included? Give an example of the data actually stored in that
field.
Are you using the prompt form to provide query parameters? If so, why
is the query returning records that do not meet the criteria? Does the
report include the actual statistics, or just the fact that there are
statistics for the quarter?

Not quite there, probably I didn't give enough explanation.
What I am doing is working on quarterly statistics we get from
external companies. The field txtmonthlabel is the quarter to which
the data relates. Some companies send data every quarter others miss
quarters. The report needs to show only those companies who have
submitted data in the quarter I running the report against. So I
thought that if the value of txtmonthlabel was null for a particular
company there would be no data and the company wouldn't show on the
report. However as I'm running the report over a date range there may
have been data in previous quarters but I only want companies that
have submitted data in this quarter. Can I do this by adding a control
on the prompt form where the user puts the date range and somehow tie
that control to equal txtmonthlabel?
Hope I've explained that.
Cheers
Tony
Technical detail for the benefit of the OP: no space in IsNull.

How do I amend this code to check whether my field txtmonthlabel
IsNot Null and then only runs the rest of the code?

I haven't read the rest of your code but you could just wrap the
whole lot up into an if:

If Not Is Null(txtmonthlabel) Then

'Run my code

End If

HTH - Keith.
www.keithwilby.com
 
T

Tony Williams

Bruce I have added my comments in your post at the relevant places, I
thought this was going to be easy!!!!
Thanks for your help maybe I'll take your advice and start another thread
with a more detailed description and see where that takes me.

Thanks again
Tony
BruceM said:
It sounds as if you are using tblmaintabs as a junction table, since each
company will have many data (tblmaintabs) records, and each quarter will
have many data records. That reasoning is valid if you have students and
courses, for instance: each student can take many courses, and each
course can contain many students, so you need an enrollment table to
resolve the relationship. However, I'm not sure it's the best approach
here, unless there's more to tblmonth than I imagine.

Yes Bruce I am using tblmaintabs as the junction table,each company has many
data (tblmaintabs) records (this is the quaterly data they submit), and each
quarter will have many data records (the table tblmonth only holds the
quarter dates)
From what I understand, you receive the information from a company on a
particular date. If so, DateReceived can be a field in tblmaintabs.

The data is not so much received on a specific date but is for a specific
quarter and there is a field in tblmaintabs which is the txtmonthlabel which
links the table to the tblmonth
As an experiment, make a copy of your database (which I assume you do
anyhow). Remove all relationships, then create a tblData:

tblData
DataID (primary key, or PK)
CompanyID (foreign key, or FK)
DateReceived
TestData

This assumes CompanyID is the name of the PK field in tblCompany. Create
a one-to-many relationship between the CompanyID fields, then make a more
based on tblCompany and a subform based on tblData (autoform is handy for
these experiments). Create a few subform records for a few companies,
with dates from this quarter for some companies and not for others. For
companies with records from this quarter, create some records for previous
quarters. You can use the TestData (text) field to make notes for
yourself (e.g. Second quarter data; there are no third quarter data).

If this basic structure works, I'm afraid I need to tell you that I'm not
sure what to do next.

Bruce, if I understand this correctly this is the structure I have
It's easy enough to select a date range, as you know, and you can select
records from the current quarter, but what you need to do first is to
select records only from companies for whom the most recent data received
was in the current quarter. For those companies you want to see all the
records within the specified date range. However, if the most recent data
are for the past quarter, you don't want to see any data from that company.

Yes that's exactly what I want, I only want to see data for companies who
ahve submitted data this quarter, I don't want to see data for the other
companies even if they have submitted data in the past.
I am intrigued by the problem, but have not been able to devise a solution.
I do know that I suggested the criteria in the last posting based on my
flawed understanding of your needs. I should have understood better than I
did based on what you wrote, but I misread it.

Unless somebody with a suggestion is monitoring this thread, your best bet
would be to start a new thread, including table structure and
relationships. The way I described tblData is a fairly conventional way of
describing the table structure. Sorry I couldn't be of more help, but I
definitely want to learn how to solve this.
Tony Williams said:
Thanks Bruce, sorry for the delay in replying but I'm in the UK

I have 3 tables in my table structure: tblcompany (holding company info),
tblmonth (holds the quarter dates), tblmaintabs (holds the data)
tblcompany and tblmaintabs are linked by company name and tblmonth and
tblmaintabs are linked by quarter date.

I know how to link the tables in the query but what I still haven't
understood is how does the criteria relate to the particular quarter that
I am working in? For example from my previous post if I am in the quarter
March 2006 how does the query criteria exclude companies that haven't
submitted data in that particular quarter?

Sorry if I'm missing the obvious but my 61 year old brain sometimes takes
a lot of time to grasp the obvious.

Also I take on board your comments on the lack of information in my early
postings. Big mistake to think you guys can read my mind. Do really
appreciate your help though.

Thanks again
Tony



BruceM said:
Your parameter could be something like:

Year([txtmonthlabel] = Year(Date()) And DatePart("q",[txtmonthlabel]) =
DatePart("q",Date) And Between [Forms]![YourForm]![StartDate] And
[Forms]![YourForm]![EndDate]

The part here that isn't so good is that the reports of data submitted
should be related to the company. In other words, there should probably
be a company table, and the quarterly data should be in a related table.
If it is set up this way, combine the two tables in a query and apply
the criteria as described above. If you need help with that you will
need to describe your table structure.

By the way, it took several posts just to clarify your needs. In future
postings (new threads in particular) you should supply enough
information that people reading the questions don't need to ask what you
mean. That uses more of everybody's time than is necessary.

What is the point of the date range when what you need is information
about a quarter?

What the report does is show the data that the company have submitted
over the date range that the user inputs. This is to check if the data
that has been submitted for that quarter is in line with previous
quarters.

You say that txtmonthlabel is the quarter, but what do you mean by
that? Is it a date, or an ordinal number (1st, 2nd, etc.)? Is the year
included? Give an example of the data actually stored in that field.

The txtmonthlabel field is a date field and formatted as mmmm yyyyy so
for say the quarter ending 31st March 2006 would be shown as March 2006

Are you using the prompt form to provide query parameters? If so, why
is the query returning records that do not meet the criteria?

Yes the prompt form provides the date range for the query criteria for
the field txtmonthlabel

Does the report include the actual statistics, or just the fact that
there are statistics for the quarter?

The report shows actual statistics. However what I want is say the last
4, 5 or even 6 quarters (that's the date range variable) statistics for
those companies who have submitted statistics for this quarter. eg If I
am in the quarter March 2006 (the current value of txtmonthlabel) I
want the report to show me statistics for say March 2005, June 2005,
September 2005, December 2005 and March 2006 BUT only for those
companies that have submitted statistics for March 2006. There will be
companies who have submitted statistics in other quarters but not March
2006 but I don't want to see them.

Hope this explains it, but please stick with me and if there is
anything else I can add I am obviously happy to.

Thanks
Tony







A few things related to your code: In this line:
strField = "txtmonthlabel"
you are defining strField as the literal characters enclosed in the
quotes. If you mean to define the string in terms of a field or
control:
strField = Me.txtmonthlabel
Same for strReport

For the rest, it is very difficult to figure out what you need.
Perhaps the user could select a quarter rather than a date range?
What is the point of the date range when what you need is information
about a quarter? You say that txtmonthlabel is the quarter, but what
do you mean by that? Is it a date, or an ordinal number (1st, 2nd,
etc.)? Is the year included? Give an example of the data actually
stored in that field.
Are you using the prompt form to provide query parameters? If so, why
is the query returning records that do not meet the criteria? Does
the report include the actual statistics, or just the fact that there
are statistics for the quarter?

Not quite there, probably I didn't give enough explanation.
What I am doing is working on quarterly statistics we get from
external companies. The field txtmonthlabel is the quarter to which
the data relates. Some companies send data every quarter others miss
quarters. The report needs to show only those companies who have
submitted data in the quarter I running the report against. So I
thought that if the value of txtmonthlabel was null for a particular
company there would be no data and the company wouldn't show on the
report. However as I'm running the report over a date range there may
have been data in previous quarters but I only want companies that
have submitted data in this quarter. Can I do this by adding a
control on the prompt form where the user puts the date range and
somehow tie that control to equal txtmonthlabel?
Hope I've explained that.
Cheers
Tony
Technical detail for the benefit of the OP: no space in IsNull.

How do I amend this code to check whether my field txtmonthlabel
IsNot Null and then only runs the rest of the code?

I haven't read the rest of your code but you could just wrap the
whole lot up into an if:

If Not Is Null(txtmonthlabel) Then

'Run my code

End If

HTH - Keith.
www.keithwilby.com
 
Ad

Advertisements

B

BruceM

Comments inline.

Tony Williams said:
Bruce I have added my comments in your post at the relevant places, I
thought this was going to be easy!!!!
Thanks for your help maybe I'll take your advice and start another thread
with a more detailed description and see where that takes me.

Thanks again
Tony


Yes Bruce I am using tblmaintabs as the junction table,each company has
many data (tblmaintabs) records (this is the quaterly data they submit),
and each quarter will have many data records (the table tblmonth only
holds the quarter dates)

I would not have a table just for quarters. I would put the quarter or the
date or something into the Data table, then filter as needed.
The data is not so much received on a specific date but is for a specific
quarter and there is a field in tblmaintabs which is the txtmonthlabel
which links the table to the tblmonth


Bruce, if I understand this correctly this is the structure I have

I have described a two-table structure, with a single one-to-many
relationship. You have described a three-table structure with a junction
table, and a one-to-many between each of the other tables and the junction
table.
Yes that's exactly what I want, I only want to see data for companies who
ahve submitted data this quarter, I don't want to see data for the other
companies even if they have submitted data in the past.

This is the part of your question that I *cannot* figure out how to solve.
I can figure out how to find records just for a particular quarter, but when
there is a specified date range I do not know how to exclude from the
recordset those companies who have not submitted data during a particular
quarter. I can almost see how to do it (by treating as inactive those
vendors who have not submitted data for the current quarter, perhaps), but I
can't get my mind around how exactly to accomplish that, if indeed it is a
valid approach. I wish I didn't have to admit I don't see the solution, but
the fact is that I don't. I will keep an eye out for a new thread on the
topic so that I too can learn from this.
I am intrigued by the problem, but have not been able to devise a
solution. I do know that I suggested the criteria in the last posting
based on my flawed understanding of your needs. I should have understood
better than I did based on what you wrote, but I misread it.

Unless somebody with a suggestion is monitoring this thread, your best
bet would be to start a new thread, including table structure and
relationships. The way I described tblData is a fairly conventional way
of describing the table structure. Sorry I couldn't be of more help, but
I definitely want to learn how to solve this.
Tony Williams said:
Thanks Bruce, sorry for the delay in replying but I'm in the UK

I have 3 tables in my table structure: tblcompany (holding company
info), tblmonth (holds the quarter dates), tblmaintabs (holds the data)
tblcompany and tblmaintabs are linked by company name and tblmonth and
tblmaintabs are linked by quarter date.

I know how to link the tables in the query but what I still haven't
understood is how does the criteria relate to the particular quarter
that I am working in? For example from my previous post if I am in the
quarter March 2006 how does the query criteria exclude companies that
haven't submitted data in that particular quarter?

Sorry if I'm missing the obvious but my 61 year old brain sometimes
takes a lot of time to grasp the obvious.

Also I take on board your comments on the lack of information in my
early postings. Big mistake to think you guys can read my mind. Do
really appreciate your help though.

Thanks again
Tony



Your parameter could be something like:

Year([txtmonthlabel] = Year(Date()) And DatePart("q",[txtmonthlabel]) =
DatePart("q",Date) And Between [Forms]![YourForm]![StartDate] And
[Forms]![YourForm]![EndDate]

The part here that isn't so good is that the reports of data submitted
should be related to the company. In other words, there should
probably be a company table, and the quarterly data should be in a
related table. If it is set up this way, combine the two tables in a
query and apply the criteria as described above. If you need help with
that you will need to describe your table structure.

By the way, it took several posts just to clarify your needs. In
future postings (new threads in particular) you should supply enough
information that people reading the questions don't need to ask what
you mean. That uses more of everybody's time than is necessary.

What is the point of the date range when what you need is information
about a quarter?

What the report does is show the data that the company have submitted
over the date range that the user inputs. This is to check if the data
that has been submitted for that quarter is in line with previous
quarters.

You say that txtmonthlabel is the quarter, but what do you mean by
that? Is it a date, or an ordinal number (1st, 2nd, etc.)? Is the
year included? Give an example of the data actually stored in that
field.

The txtmonthlabel field is a date field and formatted as mmmm yyyyy so
for say the quarter ending 31st March 2006 would be shown as March
2006

Are you using the prompt form to provide query parameters? If so,
why is the query returning records that do not meet the criteria?

Yes the prompt form provides the date range for the query criteria for
the field txtmonthlabel

Does the report include the actual statistics, or just the fact that
there are statistics for the quarter?

The report shows actual statistics. However what I want is say the
last 4, 5 or even 6 quarters (that's the date range variable)
statistics for those companies who have submitted statistics for this
quarter. eg If I am in the quarter March 2006 (the current value of
txtmonthlabel) I want the report to show me statistics for say March
2005, June 2005, September 2005, December 2005 and March 2006 BUT only
for those companies that have submitted statistics for March 2006.
There will be companies who have submitted statistics in other
quarters but not March 2006 but I don't want to see them.

Hope this explains it, but please stick with me and if there is
anything else I can add I am obviously happy to.

Thanks
Tony







A few things related to your code: In this line:
strField = "txtmonthlabel"
you are defining strField as the literal characters enclosed in the
quotes. If you mean to define the string in terms of a field or
control:
strField = Me.txtmonthlabel
Same for strReport

For the rest, it is very difficult to figure out what you need.
Perhaps the user could select a quarter rather than a date range?
What is the point of the date range when what you need is information
about a quarter? You say that txtmonthlabel is the quarter, but what
do you mean by that? Is it a date, or an ordinal number (1st, 2nd,
etc.)? Is the year included? Give an example of the data actually
stored in that field.
Are you using the prompt form to provide query parameters? If so,
why is the query returning records that do not meet the criteria?
Does the report include the actual statistics, or just the fact that
there are statistics for the quarter?

Not quite there, probably I didn't give enough explanation.
What I am doing is working on quarterly statistics we get from
external companies. The field txtmonthlabel is the quarter to which
the data relates. Some companies send data every quarter others miss
quarters. The report needs to show only those companies who have
submitted data in the quarter I running the report against. So I
thought that if the value of txtmonthlabel was null for a particular
company there would be no data and the company wouldn't show on the
report. However as I'm running the report over a date range there
may have been data in previous quarters but I only want companies
that have submitted data in this quarter. Can I do this by adding a
control on the prompt form where the user puts the date range and
somehow tie that control to equal txtmonthlabel?
Hope I've explained that.
Cheers
Tony
Technical detail for the benefit of the OP: no space in IsNull.

How do I amend this code to check whether my field txtmonthlabel
IsNot Null and then only runs the rest of the code?

I haven't read the rest of your code but you could just wrap the
whole lot up into an if:

If Not Is Null(txtmonthlabel) Then

'Run my code

End If

HTH - Keith.
www.keithwilby.com
 
T

Tony Williams

Thanks Bruce. I have a mind to try creating a query based on my tblmonth and
tblmaintabs linked on the quarter date and use the quarter date from
tblmonth as the date for my date range rather than the quarter date from the
tblmaintabs which I'm doing at the moment. If that works I'll let you know.
Right now I have to fix a whole in my lounge ceiling so I'll have a go
tomorrow or this evening. I'm not sure where you are but I'm in the UK and
it's 16.00 so I'm giving Access a rest for a couple of hours and get back to
it when I've conquered my ceiling!!
Thanks for all your help so far and watch this space!
Cheers
Tony
BruceM said:
Comments inline.

Tony Williams said:
Bruce I have added my comments in your post at the relevant places, I
thought this was going to be easy!!!!
Thanks for your help maybe I'll take your advice and start another thread
with a more detailed description and see where that takes me.

Thanks again
Tony


Yes Bruce I am using tblmaintabs as the junction table,each company has
many data (tblmaintabs) records (this is the quaterly data they submit),
and each quarter will have many data records (the table tblmonth only
holds the quarter dates)

I would not have a table just for quarters. I would put the quarter or
the date or something into the Data table, then filter as needed.
The data is not so much received on a specific date but is for a specific
quarter and there is a field in tblmaintabs which is the txtmonthlabel
which links the table to the tblmonth


Bruce, if I understand this correctly this is the structure I have

I have described a two-table structure, with a single one-to-many
relationship. You have described a three-table structure with a junction
table, and a one-to-many between each of the other tables and the junction
table.
Yes that's exactly what I want, I only want to see data for companies who
ahve submitted data this quarter, I don't want to see data for the other
companies even if they have submitted data in the past.

This is the part of your question that I *cannot* figure out how to solve.
I can figure out how to find records just for a particular quarter, but
when there is a specified date range I do not know how to exclude from the
recordset those companies who have not submitted data during a particular
quarter. I can almost see how to do it (by treating as inactive those
vendors who have not submitted data for the current quarter, perhaps), but
I can't get my mind around how exactly to accomplish that, if indeed it is
a valid approach. I wish I didn't have to admit I don't see the solution,
but the fact is that I don't. I will keep an eye out for a new thread on
the topic so that I too can learn from this.
I am intrigued by the problem, but have not been able to devise a
solution. I do know that I suggested the criteria in the last posting
based on my flawed understanding of your needs. I should have understood
better than I did based on what you wrote, but I misread it.

Unless somebody with a suggestion is monitoring this thread, your best
bet would be to start a new thread, including table structure and
relationships. The way I described tblData is a fairly conventional way
of describing the table structure. Sorry I couldn't be of more help,
but I definitely want to learn how to solve this.
Thanks Bruce, sorry for the delay in replying but I'm in the UK

I have 3 tables in my table structure: tblcompany (holding company
info), tblmonth (holds the quarter dates), tblmaintabs (holds the data)
tblcompany and tblmaintabs are linked by company name and tblmonth and
tblmaintabs are linked by quarter date.

I know how to link the tables in the query but what I still haven't
understood is how does the criteria relate to the particular quarter
that I am working in? For example from my previous post if I am in the
quarter March 2006 how does the query criteria exclude companies that
haven't submitted data in that particular quarter?

Sorry if I'm missing the obvious but my 61 year old brain sometimes
takes a lot of time to grasp the obvious.

Also I take on board your comments on the lack of information in my
early postings. Big mistake to think you guys can read my mind. Do
really appreciate your help though.

Thanks again
Tony



Your parameter could be something like:

Year([txtmonthlabel] = Year(Date()) And DatePart("q",[txtmonthlabel])
= DatePart("q",Date) And Between [Forms]![YourForm]![StartDate] And
[Forms]![YourForm]![EndDate]

The part here that isn't so good is that the reports of data submitted
should be related to the company. In other words, there should
probably be a company table, and the quarterly data should be in a
related table. If it is set up this way, combine the two tables in a
query and apply the criteria as described above. If you need help
with that you will need to describe your table structure.

By the way, it took several posts just to clarify your needs. In
future postings (new threads in particular) you should supply enough
information that people reading the questions don't need to ask what
you mean. That uses more of everybody's time than is necessary.

What is the point of the date range when what you need is
information about a quarter?

What the report does is show the data that the company have submitted
over the date range that the user inputs. This is to check if the
data that has been submitted for that quarter is in line with
previous quarters.

You say that txtmonthlabel is the quarter, but what do you mean by
that? Is it a date, or an ordinal number (1st, 2nd, etc.)? Is the
year included? Give an example of the data actually stored in that
field.

The txtmonthlabel field is a date field and formatted as mmmm yyyyy
so for say the quarter ending 31st March 2006 would be shown as March
2006

Are you using the prompt form to provide query parameters? If so,
why is the query returning records that do not meet the criteria?

Yes the prompt form provides the date range for the query criteria
for the field txtmonthlabel

Does the report include the actual statistics, or just the fact that
there are statistics for the quarter?

The report shows actual statistics. However what I want is say the
last 4, 5 or even 6 quarters (that's the date range variable)
statistics for those companies who have submitted statistics for this
quarter. eg If I am in the quarter March 2006 (the current value of
txtmonthlabel) I want the report to show me statistics for say March
2005, June 2005, September 2005, December 2005 and March 2006 BUT
only for those companies that have submitted statistics for March
2006. There will be companies who have submitted statistics in other
quarters but not March 2006 but I don't want to see them.

Hope this explains it, but please stick with me and if there is
anything else I can add I am obviously happy to.

Thanks
Tony







A few things related to your code: In this line:
strField = "txtmonthlabel"
you are defining strField as the literal characters enclosed in the
quotes. If you mean to define the string in terms of a field or
control:
strField = Me.txtmonthlabel
Same for strReport

For the rest, it is very difficult to figure out what you need.
Perhaps the user could select a quarter rather than a date range?
What is the point of the date range when what you need is
information about a quarter? You say that txtmonthlabel is the
quarter, but what do you mean by that? Is it a date, or an ordinal
number (1st, 2nd, etc.)? Is the year included? Give an example of
the data actually stored in that field.
Are you using the prompt form to provide query parameters? If so,
why is the query returning records that do not meet the criteria?
Does the report include the actual statistics, or just the fact that
there are statistics for the quarter?

Not quite there, probably I didn't give enough explanation.
What I am doing is working on quarterly statistics we get from
external companies. The field txtmonthlabel is the quarter to which
the data relates. Some companies send data every quarter others
miss quarters. The report needs to show only those companies who
have submitted data in the quarter I running the report against. So
I thought that if the value of txtmonthlabel was null for a
particular company there would be no data and the company wouldn't
show on the report. However as I'm running the report over a date
range there may have been data in previous quarters but I only want
companies that have submitted data in this quarter. Can I do this
by adding a control on the prompt form where the user puts the date
range and somehow tie that control to equal txtmonthlabel?
Hope I've explained that.
Cheers
Tony
Technical detail for the benefit of the OP: no space in IsNull.

How do I amend this code to check whether my field txtmonthlabel
IsNot Null and then only runs the rest of the code?

I haven't read the rest of your code but you could just wrap the
whole lot up into an if:

If Not Is Null(txtmonthlabel) Then

'Run my code

End If

HTH - Keith.
www.keithwilby.com
 
B

BruceM

I am on the east coast of the USA (almost in Canada), and five hours before
GMT.

A few more random thoughts:
I will refer to data submissions as data reports. Regarding the use of a
junction table, each quarter can have many data reports (one each from any
number of companies), but the reverse is not true. A data report cannot be
assoicated with more than one quarter nor with more than one company. Also,
each company can submit many data reports (one per quarter). A typical
many-to-many relationship is the aforementioned students/courses, whereby
each student can take many courses, and each course can contain many
students, so there is a many-to-many relationship between students and
courses. I don't see a similar relationship in your database. Each quarter
can contain many reports, and each report can be associated with many
quarters? No, that's not it. Each company can submit many reports (one per
quarter), and each report can be associated with many companies? That's not
it either. Each report can be associated with many companies, or each
report can be associated with many quarters? No and no. I just don't see a
many-to-many relationship here, which is why I think that setting up the
relationships as you have cannot lead you where you want to go. I really
think that the quarter information needs to be stored with the data. Then
again, I am somewhat new to this, and may be overlooking something.

Tony Williams said:
Thanks Bruce. I have a mind to try creating a query based on my tblmonth
and tblmaintabs linked on the quarter date and use the quarter date from
tblmonth as the date for my date range rather than the quarter date from
the tblmaintabs which I'm doing at the moment. If that works I'll let you
know. Right now I have to fix a whole in my lounge ceiling so I'll have a
go tomorrow or this evening. I'm not sure where you are but I'm in the UK
and it's 16.00 so I'm giving Access a rest for a couple of hours and get
back to it when I've conquered my ceiling!!
Thanks for all your help so far and watch this space!
Cheers
Tony
BruceM said:
Comments inline.

Tony Williams said:
Bruce I have added my comments in your post at the relevant places, I
thought this was going to be easy!!!!
Thanks for your help maybe I'll take your advice and start another
thread with a more detailed description and see where that takes me.

Thanks again
Tony
It sounds as if you are using tblmaintabs as a junction table, since
each company will have many data (tblmaintabs) records, and each
quarter will have many data records. That reasoning is valid if you
have students and courses, for instance: each student can take many
courses, and each course can contain many students, so you need an
enrollment table to resolve the relationship. However, I'm not sure
it's the best approach here, unless there's more to tblmonth than I
imagine.

Yes Bruce I am using tblmaintabs as the junction table,each company has
many data (tblmaintabs) records (this is the quaterly data they submit),
and each quarter will have many data records (the table tblmonth only
holds the quarter dates)

I would not have a table just for quarters. I would put the quarter or
the date or something into the Data table, then filter as needed.
From what I understand, you receive the information from a company on a
particular date. If so, DateReceived can be a field in tblmaintabs.

The data is not so much received on a specific date but is for a
specific quarter and there is a field in tblmaintabs which is the
txtmonthlabel which links the table to the tblmonth

As an experiment, make a copy of your database (which I assume you do
anyhow). Remove all relationships, then create a tblData:

tblData
DataID (primary key, or PK)
CompanyID (foreign key, or FK)
DateReceived
TestData

This assumes CompanyID is the name of the PK field in tblCompany.
Create a one-to-many relationship between the CompanyID fields, then
make a more based on tblCompany and a subform based on tblData
(autoform is handy for these experiments). Create a few subform
records for a few companies, with dates from this quarter for some
companies and not for others. For companies with records from this
quarter, create some records for previous quarters. You can use the
TestData (text) field to make notes for yourself (e.g. Second quarter
data; there are no third quarter data).

If this basic structure works, I'm afraid I need to tell you that I'm
not sure what to do next.

Bruce, if I understand this correctly this is the structure I have

I have described a two-table structure, with a single one-to-many
relationship. You have described a three-table structure with a junction
table, and a one-to-many between each of the other tables and the
junction table.
It's easy enough to select a date range, as you know, and you can select
records from the current quarter, but what you need to do first is to
select records only from companies for whom the most recent data
received was in the current quarter. For those companies you want to
see all the records within the specified date range. However, if the
most recent data are for the past quarter, you don't want to see any
data from that company.

Yes that's exactly what I want, I only want to see data for companies
who ahve submitted data this quarter, I don't want to see data for the
other companies even if they have submitted data in the past.

This is the part of your question that I *cannot* figure out how to
solve. I can figure out how to find records just for a particular
quarter, but when there is a specified date range I do not know how to
exclude from the recordset those companies who have not submitted data
during a particular quarter. I can almost see how to do it (by treating
as inactive those vendors who have not submitted data for the current
quarter, perhaps), but I can't get my mind around how exactly to
accomplish that, if indeed it is a valid approach. I wish I didn't have
to admit I don't see the solution, but the fact is that I don't. I will
keep an eye out for a new thread on the topic so that I too can learn
from this.
I am intrigued by the problem, but have not been able to devise a
solution. I do know that I suggested the criteria in the last posting
based on my flawed understanding of your needs. I should have
understood better than I did based on what you wrote, but I misread it.

Unless somebody with a suggestion is monitoring this thread, your best
bet would be to start a new thread, including table structure and
relationships. The way I described tblData is a fairly conventional way
of describing the table structure. Sorry I couldn't be of more help,
but I definitely want to learn how to solve this.

Thanks Bruce, sorry for the delay in replying but I'm in the UK

I have 3 tables in my table structure: tblcompany (holding company
info), tblmonth (holds the quarter dates), tblmaintabs (holds the
data)
tblcompany and tblmaintabs are linked by company name and tblmonth and
tblmaintabs are linked by quarter date.

I know how to link the tables in the query but what I still haven't
understood is how does the criteria relate to the particular quarter
that I am working in? For example from my previous post if I am in the
quarter March 2006 how does the query criteria exclude companies that
haven't submitted data in that particular quarter?

Sorry if I'm missing the obvious but my 61 year old brain sometimes
takes a lot of time to grasp the obvious.

Also I take on board your comments on the lack of information in my
early postings. Big mistake to think you guys can read my mind. Do
really appreciate your help though.

Thanks again
Tony



Your parameter could be something like:

Year([txtmonthlabel] = Year(Date()) And DatePart("q",[txtmonthlabel])
= DatePart("q",Date) And Between [Forms]![YourForm]![StartDate] And
[Forms]![YourForm]![EndDate]

The part here that isn't so good is that the reports of data
submitted should be related to the company. In other words, there
should probably be a company table, and the quarterly data should be
in a related table. If it is set up this way, combine the two tables
in a query and apply the criteria as described above. If you need
help with that you will need to describe your table structure.

By the way, it took several posts just to clarify your needs. In
future postings (new threads in particular) you should supply enough
information that people reading the questions don't need to ask what
you mean. That uses more of everybody's time than is necessary.

What is the point of the date range when what you need is
information about a quarter?

What the report does is show the data that the company have
submitted over the date range that the user inputs. This is to check
if the data that has been submitted for that quarter is in line with
previous quarters.

You say that txtmonthlabel is the quarter, but what do you mean by
that? Is it a date, or an ordinal number (1st, 2nd, etc.)? Is the
year included? Give an example of the data actually stored in that
field.

The txtmonthlabel field is a date field and formatted as mmmm yyyyy
so for say the quarter ending 31st March 2006 would be shown as
March 2006

Are you using the prompt form to provide query parameters? If so,
why is the query returning records that do not meet the criteria?

Yes the prompt form provides the date range for the query criteria
for the field txtmonthlabel

Does the report include the actual statistics, or just the fact that
there are statistics for the quarter?

The report shows actual statistics. However what I want is say the
last 4, 5 or even 6 quarters (that's the date range variable)
statistics for those companies who have submitted statistics for
this quarter. eg If I am in the quarter March 2006 (the current
value of txtmonthlabel) I want the report to show me statistics for
say March 2005, June 2005, September 2005, December 2005 and March
2006 BUT only for those companies that have submitted statistics for
March 2006. There will be companies who have submitted statistics in
other quarters but not March 2006 but I don't want to see them.

Hope this explains it, but please stick with me and if there is
anything else I can add I am obviously happy to.

Thanks
Tony







A few things related to your code: In this line:
strField = "txtmonthlabel"
you are defining strField as the literal characters enclosed in the
quotes. If you mean to define the string in terms of a field or
control:
strField = Me.txtmonthlabel
Same for strReport

For the rest, it is very difficult to figure out what you need.
Perhaps the user could select a quarter rather than a date range?
What is the point of the date range when what you need is
information about a quarter? You say that txtmonthlabel is the
quarter, but what do you mean by that? Is it a date, or an ordinal
number (1st, 2nd, etc.)? Is the year included? Give an example of
the data actually stored in that field.
Are you using the prompt form to provide query parameters? If so,
why is the query returning records that do not meet the criteria?
Does the report include the actual statistics, or just the fact
that there are statistics for the quarter?

Not quite there, probably I didn't give enough explanation.
What I am doing is working on quarterly statistics we get from
external companies. The field txtmonthlabel is the quarter to
which the data relates. Some companies send data every quarter
others miss quarters. The report needs to show only those
companies who have submitted data in the quarter I running the
report against. So I thought that if the value of txtmonthlabel
was null for a particular company there would be no data and the
company wouldn't show on the report. However as I'm running the
report over a date range there may have been data in previous
quarters but I only want companies that have submitted data in
this quarter. Can I do this by adding a control on the prompt form
where the user puts the date range and somehow tie that control to
equal txtmonthlabel?
Hope I've explained that.
Cheers
Tony
Technical detail for the benefit of the OP: no space in IsNull.

How do I amend this code to check whether my field
txtmonthlabel IsNot Null and then only runs the rest of the
code?

I haven't read the rest of your code but you could just wrap the
whole lot up into an if:

If Not Is Null(txtmonthlabel) Then

'Run my code

End If

HTH - Keith.
www.keithwilby.com
 
T

Tony Williams

Thanks Bruce, can I share a few random thoughts with you:

The tblmonth table holds each quarterly date but each quarter only appears
once
The tblcompany holds the name of each company and each company only appears
once
The tblmaintabs holds all the data and has many companies and many quarters
Question: Is there then a many to many relationship between the data table
and the company and quarter tables?

Each quarter can have many companies
Each company submits only one lot of data per quarter
Not every company though submits data every quarter
The data table has many quarters and many companies

The report I'm trying to produce can have
many quarters
many companies

I need to sit back and think about this. The problem is I inherited this
database and have been asked to streamline it for them. But I only do this
to keep my 61 year old brain active so I'm not really an Access
professional, BUT it's a challenge and I love challenges!
Cheers
Tony

BruceM said:
I am on the east coast of the USA (almost in Canada), and five hours before
GMT.

A few more random thoughts:
I will refer to data submissions as data reports. Regarding the use of a
junction table, each quarter can have many data reports (one each from any
number of companies), but the reverse is not true. A data report cannot
be assoicated with more than one quarter nor with more than one company.
Also, each company can submit many data reports (one per quarter). A
typical many-to-many relationship is the aforementioned students/courses,
whereby each student can take many courses, and each course can contain
many students, so there is a many-to-many relationship between students
and courses. I don't see a similar relationship in your database. Each
quarter can contain many reports, and each report can be associated with
many quarters? No, that's not it. Each company can submit many reports
(one per quarter), and each report can be associated with many companies?
That's not it either. Each report can be associated with many companies,
or each report can be associated with many quarters? No and no. I just
don't see a many-to-many relationship here, which is why I think that
setting up the relationships as you have cannot lead you where you want to
go. I really think that the quarter information needs to be stored with
the data. Then again, I am somewhat new to this, and may be overlooking
something.

Tony Williams said:
Thanks Bruce. I have a mind to try creating a query based on my tblmonth
and tblmaintabs linked on the quarter date and use the quarter date from
tblmonth as the date for my date range rather than the quarter date from
the tblmaintabs which I'm doing at the moment. If that works I'll let you
know. Right now I have to fix a whole in my lounge ceiling so I'll have a
go tomorrow or this evening. I'm not sure where you are but I'm in the UK
and it's 16.00 so I'm giving Access a rest for a couple of hours and get
back to it when I've conquered my ceiling!!
Thanks for all your help so far and watch this space!
Cheers
Tony
BruceM said:
Comments inline.

Bruce I have added my comments in your post at the relevant places, I
thought this was going to be easy!!!!
Thanks for your help maybe I'll take your advice and start another
thread with a more detailed description and see where that takes me.

Thanks again
Tony
It sounds as if you are using tblmaintabs as a junction table, since
each company will have many data (tblmaintabs) records, and each
quarter will have many data records. That reasoning is valid if you
have students and courses, for instance: each student can take many
courses, and each course can contain many students, so you need an
enrollment table to resolve the relationship. However, I'm not sure
it's the best approach here, unless there's more to tblmonth than I
imagine.

Yes Bruce I am using tblmaintabs as the junction table,each company has
many data (tblmaintabs) records (this is the quaterly data they
submit), and each quarter will have many data records (the table
tblmonth only holds the quarter dates)

I would not have a table just for quarters. I would put the quarter or
the date or something into the Data table, then filter as needed.

From what I understand, you receive the information from a company on a
particular date. If so, DateReceived can be a field in tblmaintabs.

The data is not so much received on a specific date but is for a
specific quarter and there is a field in tblmaintabs which is the
txtmonthlabel which links the table to the tblmonth

As an experiment, make a copy of your database (which I assume you do
anyhow). Remove all relationships, then create a tblData:

tblData
DataID (primary key, or PK)
CompanyID (foreign key, or FK)
DateReceived
TestData

This assumes CompanyID is the name of the PK field in tblCompany.
Create a one-to-many relationship between the CompanyID fields, then
make a more based on tblCompany and a subform based on tblData
(autoform is handy for these experiments). Create a few subform
records for a few companies, with dates from this quarter for some
companies and not for others. For companies with records from this
quarter, create some records for previous quarters. You can use the
TestData (text) field to make notes for yourself (e.g. Second quarter
data; there are no third quarter data).

If this basic structure works, I'm afraid I need to tell you that I'm
not sure what to do next.

Bruce, if I understand this correctly this is the structure I have

I have described a two-table structure, with a single one-to-many
relationship. You have described a three-table structure with a
junction table, and a one-to-many between each of the other tables and
the junction table.


It's easy enough to select a date range, as you know, and you can
select records from the current quarter, but what you need to do first
is to select records only from companies for whom the most recent data
received was in the current quarter. For those companies you want to
see all the records within the specified date range. However, if the
most recent data are for the past quarter, you don't want to see any
data from that company.

Yes that's exactly what I want, I only want to see data for companies
who ahve submitted data this quarter, I don't want to see data for the
other companies even if they have submitted data in the past.

This is the part of your question that I *cannot* figure out how to
solve. I can figure out how to find records just for a particular
quarter, but when there is a specified date range I do not know how to
exclude from the recordset those companies who have not submitted data
during a particular quarter. I can almost see how to do it (by treating
as inactive those vendors who have not submitted data for the current
quarter, perhaps), but I can't get my mind around how exactly to
accomplish that, if indeed it is a valid approach. I wish I didn't have
to admit I don't see the solution, but the fact is that I don't. I will
keep an eye out for a new thread on the topic so that I too can learn
from this.


I am intrigued by the problem, but have not been able to devise a
solution. I do know that I suggested the criteria in the last posting
based on my flawed understanding of your needs. I should have
understood better than I did based on what you wrote, but I misread it.

Unless somebody with a suggestion is monitoring this thread, your best
bet would be to start a new thread, including table structure and
relationships. The way I described tblData is a fairly conventional
way of describing the table structure. Sorry I couldn't be of more
help, but I definitely want to learn how to solve this.

Thanks Bruce, sorry for the delay in replying but I'm in the UK

I have 3 tables in my table structure: tblcompany (holding company
info), tblmonth (holds the quarter dates), tblmaintabs (holds the
data)
tblcompany and tblmaintabs are linked by company name and tblmonth
and tblmaintabs are linked by quarter date.

I know how to link the tables in the query but what I still haven't
understood is how does the criteria relate to the particular quarter
that I am working in? For example from my previous post if I am in
the quarter March 2006 how does the query criteria exclude companies
that haven't submitted data in that particular quarter?

Sorry if I'm missing the obvious but my 61 year old brain sometimes
takes a lot of time to grasp the obvious.

Also I take on board your comments on the lack of information in my
early postings. Big mistake to think you guys can read my mind. Do
really appreciate your help though.

Thanks again
Tony



Your parameter could be something like:

Year([txtmonthlabel] = Year(Date()) And
DatePart("q",[txtmonthlabel]) = DatePart("q",Date) And Between
[Forms]![YourForm]![StartDate] And [Forms]![YourForm]![EndDate]

The part here that isn't so good is that the reports of data
submitted should be related to the company. In other words, there
should probably be a company table, and the quarterly data should be
in a related table. If it is set up this way, combine the two tables
in a query and apply the criteria as described above. If you need
help with that you will need to describe your table structure.

By the way, it took several posts just to clarify your needs. In
future postings (new threads in particular) you should supply enough
information that people reading the questions don't need to ask what
you mean. That uses more of everybody's time than is necessary.

What is the point of the date range when what you need is
information about a quarter?

What the report does is show the data that the company have
submitted over the date range that the user inputs. This is to
check if the data that has been submitted for that quarter is in
line with previous quarters.

You say that txtmonthlabel is the quarter, but what do you mean by
that? Is it a date, or an ordinal number (1st, 2nd, etc.)? Is the
year included? Give an example of the data actually stored in that
field.

The txtmonthlabel field is a date field and formatted as mmmm yyyyy
so for say the quarter ending 31st March 2006 would be shown as
March 2006

Are you using the prompt form to provide query parameters? If so,
why is the query returning records that do not meet the criteria?

Yes the prompt form provides the date range for the query criteria
for the field txtmonthlabel

Does the report include the actual statistics, or just the fact
that there are statistics for the quarter?

The report shows actual statistics. However what I want is say the
last 4, 5 or even 6 quarters (that's the date range variable)
statistics for those companies who have submitted statistics for
this quarter. eg If I am in the quarter March 2006 (the current
value of txtmonthlabel) I want the report to show me statistics for
say March 2005, June 2005, September 2005, December 2005 and March
2006 BUT only for those companies that have submitted statistics
for March 2006. There will be companies who have submitted
statistics in other quarters but not March 2006 but I don't want to
see them.

Hope this explains it, but please stick with me and if there is
anything else I can add I am obviously happy to.

Thanks
Tony







A few things related to your code: In this line:
strField = "txtmonthlabel"
you are defining strField as the literal characters enclosed in
the quotes. If you mean to define the string in terms of a field
or control:
strField = Me.txtmonthlabel
Same for strReport

For the rest, it is very difficult to figure out what you need.
Perhaps the user could select a quarter rather than a date range?
What is the point of the date range when what you need is
information about a quarter? You say that txtmonthlabel is the
quarter, but what do you mean by that? Is it a date, or an ordinal
number (1st, 2nd, etc.)? Is the year included? Give an example
of the data actually stored in that field.
Are you using the prompt form to provide query parameters? If so,
why is the query returning records that do not meet the criteria?
Does the report include the actual statistics, or just the fact
that there are statistics for the quarter?

Not quite there, probably I didn't give enough explanation.
What I am doing is working on quarterly statistics we get from
external companies. The field txtmonthlabel is the quarter to
which the data relates. Some companies send data every quarter
others miss quarters. The report needs to show only those
companies who have submitted data in the quarter I running the
report against. So I thought that if the value of txtmonthlabel
was null for a particular company there would be no data and the
company wouldn't show on the report. However as I'm running the
report over a date range there may have been data in previous
quarters but I only want companies that have submitted data in
this quarter. Can I do this by adding a control on the prompt
form where the user puts the date range and somehow tie that
control to equal txtmonthlabel?
Hope I've explained that.
Cheers
Tony
Technical detail for the benefit of the OP: no space in IsNull.

How do I amend this code to check whether my field
txtmonthlabel IsNot Null and then only runs the rest of the
code?

I haven't read the rest of your code but you could just wrap
the whole lot up into an if:

If Not Is Null(txtmonthlabel) Then

'Run my code

End If

HTH - Keith.
www.keithwilby.com
 
B

BruceM

Ah, an inherited project! Those can be, um, interesting, although it can be
difficult to work with a convoluted structure. More inline.

Tony Williams said:
Thanks Bruce, can I share a few random thoughts with you:

The tblmonth table holds each quarterly date but each quarter only appears
once
The tblcompany holds the name of each company and each company only
appears once
The tblmaintabs holds all the data and has many companies and many
quarters
Question: Is there then a many to many relationship between the data table
and the company and quarter tables?

If there is a many-to-many it is between companies and quarters: Each
company can be associated with many quarters, and each quarter is associated
with many companies. The data table, then, is the only candidate to be a
junction table. The many-to-many relationship is between the tables that
are *not* the junction table.

I can't figure out how that would work in your case, though. Back to
Students/Courses, with Enrollment as the junction table. You can build a
form based on Students with a subform based on Enrollment. On the subform
you would typically have a combination box from which you could select
Courses. Having selected courses for the various students, you could make a
report based on Courses with a subform based on Enrollment to see who is in
each course. Or you could build a form based on Courses and a subform again
based on Enrollment, with a combination box on the subform to select
Students for the various courses. That seems like an unlikely approach, but
it could be done. In general, you have a main form or report based on
either Students and Courses, and in all cases a subform or subreport based
on Enrollment.

In a many-to-many situation one of the tables is relatively static. That
table contains pre-existing records (such as Courses). If you need to add
or change a Course record you need to have a separate form for managing that
information. Once the change or addition is made, that new or revised
record is available for selection on the subform.

In a training records database here each training session is described on
the main form (which is linked to a Session table), and the attendance is
listed on the subform, which is based on a junction table (Enrollment, just
as with the above). On the subform is a combo box from which the employees
who attended the training are selected. I can also run reports for each
employee to get a listing of what training they have had. The main form is
based on Employees, and the subform on Enrollment.

In your case, what could you do with a form/subform? If the main form is
based on Company and the subform on Data, would you use a combo box to
select a quarter? That means you would need to edit the Months table to add
a quarter before you could select it. Not much point to that when all you
need is a single field in the Data table to identify a date or quarter. Or
would you build a main form based on the Months table and a subform in which
you would select a company, then add the data?

The other thing I'm finding rather curious is that you are linking text
fields (apparently) between Company and Data, and date fields between Data
and Quarter. Dates make poor linking fields in most cases. For one thing,
how will you enforce their uniqueness? Do you have primary key fields?

Unless there is more to the Months table than I understand, using Data as
the junction table doesn't seem to accomplish anything that couldn't be
accomplished with Data as the child table (on the many side of a
one-to-many). Whether in this posting or a new one, it is helpful to
describe tables as I have described the hypothetical Data table, in similar
format. You don't need every field, just the key fields. For instance, for
the company table all you need is something like

tblCompany
CompanyID (autonumber primary key)
CompanyName, Address, etc.

When describing a database, include all linking (related fields).

Good luck with the database, and I hope you get that hole in the ceiling
fixed OK.
Each quarter can have many companies
Each company submits only one lot of data per quarter
Not every company though submits data every quarter
The data table has many quarters and many companies

The report I'm trying to produce can have
many quarters
many companies

I need to sit back and think about this. The problem is I inherited this
database and have been asked to streamline it for them. But I only do this
to keep my 61 year old brain active so I'm not really an Access
professional, BUT it's a challenge and I love challenges!
Cheers
Tony

BruceM said:
I am on the east coast of the USA (almost in Canada), and five hours
before GMT.

A few more random thoughts:
I will refer to data submissions as data reports. Regarding the use of a
junction table, each quarter can have many data reports (one each from
any number of companies), but the reverse is not true. A data report
cannot be assoicated with more than one quarter nor with more than one
company. Also, each company can submit many data reports (one per
quarter). A typical many-to-many relationship is the aforementioned
students/courses, whereby each student can take many courses, and each
course can contain many students, so there is a many-to-many relationship
between students and courses. I don't see a similar relationship in your
database. Each quarter can contain many reports, and each report can be
associated with many quarters? No, that's not it. Each company can
submit many reports (one per quarter), and each report can be associated
with many companies? That's not it either. Each report can be associated
with many companies, or each report can be associated with many quarters?
No and no. I just don't see a many-to-many relationship here, which is
why I think that setting up the relationships as you have cannot lead you
where you want to go. I really think that the quarter information needs
to be stored with the data. Then again, I am somewhat new to this, and
may be overlooking something.

Tony Williams said:
Thanks Bruce. I have a mind to try creating a query based on my tblmonth
and tblmaintabs linked on the quarter date and use the quarter date from
tblmonth as the date for my date range rather than the quarter date from
the tblmaintabs which I'm doing at the moment. If that works I'll let
you know. Right now I have to fix a whole in my lounge ceiling so I'll
have a go tomorrow or this evening. I'm not sure where you are but I'm
in the UK and it's 16.00 so I'm giving Access a rest for a couple of
hours and get back to it when I've conquered my ceiling!!
Thanks for all your help so far and watch this space!
Cheers
Tony
Comments inline.

Bruce I have added my comments in your post at the relevant places, I
thought this was going to be easy!!!!
Thanks for your help maybe I'll take your advice and start another
thread with a more detailed description and see where that takes me.

Thanks again
Tony
It sounds as if you are using tblmaintabs as a junction table, since
each company will have many data (tblmaintabs) records, and each
quarter will have many data records. That reasoning is valid if you
have students and courses, for instance: each student can take many
courses, and each course can contain many students, so you need an
enrollment table to resolve the relationship. However, I'm not sure
it's the best approach here, unless there's more to tblmonth than I
imagine.

Yes Bruce I am using tblmaintabs as the junction table,each company
has many data (tblmaintabs) records (this is the quaterly data they
submit), and each quarter will have many data records (the table
tblmonth only holds the quarter dates)

I would not have a table just for quarters. I would put the quarter or
the date or something into the Data table, then filter as needed.

From what I understand, you receive the information from a company on
a particular date. If so, DateReceived can be a field in tblmaintabs.

The data is not so much received on a specific date but is for a
specific quarter and there is a field in tblmaintabs which is the
txtmonthlabel which links the table to the tblmonth

As an experiment, make a copy of your database (which I assume you do
anyhow). Remove all relationships, then create a tblData:

tblData
DataID (primary key, or PK)
CompanyID (foreign key, or FK)
DateReceived
TestData

This assumes CompanyID is the name of the PK field in tblCompany.
Create a one-to-many relationship between the CompanyID fields, then
make a more based on tblCompany and a subform based on tblData
(autoform is handy for these experiments). Create a few subform
records for a few companies, with dates from this quarter for some
companies and not for others. For companies with records from this
quarter, create some records for previous quarters. You can use the
TestData (text) field to make notes for yourself (e.g. Second quarter
data; there are no third quarter data).

If this basic structure works, I'm afraid I need to tell you that I'm
not sure what to do next.

Bruce, if I understand this correctly this is the structure I have

I have described a two-table structure, with a single one-to-many
relationship. You have described a three-table structure with a
junction table, and a one-to-many between each of the other tables and
the junction table.


It's easy enough to select a date range, as you know, and you can
select records from the current quarter, but what you need to do first
is to select records only from companies for whom the most recent data
received was in the current quarter. For those companies you want to
see all the records within the specified date range. However, if the
most recent data are for the past quarter, you don't want to see any
data from that company.

Yes that's exactly what I want, I only want to see data for companies
who ahve submitted data this quarter, I don't want to see data for the
other companies even if they have submitted data in the past.

This is the part of your question that I *cannot* figure out how to
solve. I can figure out how to find records just for a particular
quarter, but when there is a specified date range I do not know how to
exclude from the recordset those companies who have not submitted data
during a particular quarter. I can almost see how to do it (by
treating as inactive those vendors who have not submitted data for the
current quarter, perhaps), but I can't get my mind around how exactly
to accomplish that, if indeed it is a valid approach. I wish I didn't
have to admit I don't see the solution, but the fact is that I don't.
I will keep an eye out for a new thread on the topic so that I too can
learn from this.


I am intrigued by the problem, but have not been able to devise a
solution. I do know that I suggested the criteria in the last posting
based on my flawed understanding of your needs. I should have
understood better than I did based on what you wrote, but I misread
it.

Unless somebody with a suggestion is monitoring this thread, your
best bet would be to start a new thread, including table structure
and relationships. The way I described tblData is a fairly
conventional way of describing the table structure. Sorry I couldn't
be of more help, but I definitely want to learn how to solve this.

Thanks Bruce, sorry for the delay in replying but I'm in the UK

I have 3 tables in my table structure: tblcompany (holding company
info), tblmonth (holds the quarter dates), tblmaintabs (holds the
data)
tblcompany and tblmaintabs are linked by company name and tblmonth
and tblmaintabs are linked by quarter date.

I know how to link the tables in the query but what I still haven't
understood is how does the criteria relate to the particular quarter
that I am working in? For example from my previous post if I am in
the quarter March 2006 how does the query criteria exclude companies
that haven't submitted data in that particular quarter?

Sorry if I'm missing the obvious but my 61 year old brain sometimes
takes a lot of time to grasp the obvious.

Also I take on board your comments on the lack of information in my
early postings. Big mistake to think you guys can read my mind. Do
really appreciate your help though.

Thanks again
Tony



Your parameter could be something like:

Year([txtmonthlabel] = Year(Date()) And
DatePart("q",[txtmonthlabel]) = DatePart("q",Date) And Between
[Forms]![YourForm]![StartDate] And [Forms]![YourForm]![EndDate]

The part here that isn't so good is that the reports of data
submitted should be related to the company. In other words, there
should probably be a company table, and the quarterly data should
be in a related table. If it is set up this way, combine the two
tables in a query and apply the criteria as described above. If
you need help with that you will need to describe your table
structure.

By the way, it took several posts just to clarify your needs. In
future postings (new threads in particular) you should supply
enough information that people reading the questions don't need to
ask what you mean. That uses more of everybody's time than is
necessary.

What is the point of the date range when what you need is
information about a quarter?

What the report does is show the data that the company have
submitted over the date range that the user inputs. This is to
check if the data that has been submitted for that quarter is in
line with previous quarters.

You say that txtmonthlabel is the quarter, but what do you mean by
that? Is it a date, or an ordinal number (1st, 2nd, etc.)? Is the
year included? Give an example of the data actually stored in that
field.

The txtmonthlabel field is a date field and formatted as mmmm
yyyyy so for say the quarter ending 31st March 2006 would be shown
as March 2006

Are you using the prompt form to provide query parameters? If
so, why is the query returning records that do not meet the
criteria?

Yes the prompt form provides the date range for the query criteria
for the field txtmonthlabel

Does the report include the actual statistics, or just the fact
that there are statistics for the quarter?

The report shows actual statistics. However what I want is say the
last 4, 5 or even 6 quarters (that's the date range variable)
statistics for those companies who have submitted statistics for
this quarter. eg If I am in the quarter March 2006 (the current
value of txtmonthlabel) I want the report to show me statistics
for say March 2005, June 2005, September 2005, December 2005 and
March 2006 BUT only for those companies that have submitted
statistics for March 2006. There will be companies who have
submitted statistics in other quarters but not March 2006 but I
don't want to see them.

Hope this explains it, but please stick with me and if there is
anything else I can add I am obviously happy to.

Thanks
Tony







A few things related to your code: In this line:
strField = "txtmonthlabel"
you are defining strField as the literal characters enclosed in
the quotes. If you mean to define the string in terms of a field
or control:
strField = Me.txtmonthlabel
Same for strReport

For the rest, it is very difficult to figure out what you need.
Perhaps the user could select a quarter rather than a date range?
What is the point of the date range when what you need is
information about a quarter? You say that txtmonthlabel is the
quarter, but what do you mean by that? Is it a date, or an
ordinal number (1st, 2nd, etc.)? Is the year included? Give an
example of the data actually stored in that field.
Are you using the prompt form to provide query parameters? If
so, why is the query returning records that do not meet the
criteria? Does the report include the actual statistics, or just
the fact that there are statistics for the quarter?

Not quite there, probably I didn't give enough explanation.
What I am doing is working on quarterly statistics we get from
external companies. The field txtmonthlabel is the quarter to
which the data relates. Some companies send data every quarter
others miss quarters. The report needs to show only those
companies who have submitted data in the quarter I running the
report against. So I thought that if the value of txtmonthlabel
was null for a particular company there would be no data and the
company wouldn't show on the report. However as I'm running the
report over a date range there may have been data in previous
quarters but I only want companies that have submitted data in
this quarter. Can I do this by adding a control on the prompt
form where the user puts the date range and somehow tie that
control to equal txtmonthlabel?
Hope I've explained that.
Cheers
Tony
Technical detail for the benefit of the OP: no space in
IsNull.

How do I amend this code to check whether my field
txtmonthlabel IsNot Null and then only runs the rest of the
code?

I haven't read the rest of your code but you could just wrap
the whole lot up into an if:

If Not Is Null(txtmonthlabel) Then

'Run my code

End If

HTH - Keith.
www.keithwilby.com
 
Ad

Advertisements

T

Tony Williams

Thanks Bruce I'm going to print out your last message and work my way
through it and maybe in a day or two post back here with some more thoughts
before I start a new thread so I would be grateful if you would keep
checking from about Wednesday next week. Monday and Tuesday I'm taking my
wife to Paris for her birthday (assuming I can get out of the country with
what's been happening over here today) and it's 18.30 + here now so I'm
packing it in for the day and going back to my ceiling
Cheers
Tony
BruceM said:
Ah, an inherited project! Those can be, um, interesting, although it can
be difficult to work with a convoluted structure. More inline.

Tony Williams said:
Thanks Bruce, can I share a few random thoughts with you:

The tblmonth table holds each quarterly date but each quarter only
appears once
The tblcompany holds the name of each company and each company only
appears once
The tblmaintabs holds all the data and has many companies and many
quarters
Question: Is there then a many to many relationship between the data
table and the company and quarter tables?

If there is a many-to-many it is between companies and quarters: Each
company can be associated with many quarters, and each quarter is
associated with many companies. The data table, then, is the only
candidate to be a junction table. The many-to-many relationship is
between the tables that are *not* the junction table.

I can't figure out how that would work in your case, though. Back to
Students/Courses, with Enrollment as the junction table. You can build a
form based on Students with a subform based on Enrollment. On the subform
you would typically have a combination box from which you could select
Courses. Having selected courses for the various students, you could make
a report based on Courses with a subform based on Enrollment to see who is
in each course. Or you could build a form based on Courses and a subform
again based on Enrollment, with a combination box on the subform to select
Students for the various courses. That seems like an unlikely approach,
but it could be done. In general, you have a main form or report based on
either Students and Courses, and in all cases a subform or subreport based
on Enrollment.

In a many-to-many situation one of the tables is relatively static. That
table contains pre-existing records (such as Courses). If you need to add
or change a Course record you need to have a separate form for managing
that information. Once the change or addition is made, that new or
revised record is available for selection on the subform.

In a training records database here each training session is described on
the main form (which is linked to a Session table), and the attendance is
listed on the subform, which is based on a junction table (Enrollment,
just as with the above). On the subform is a combo box from which the
employees who attended the training are selected. I can also run reports
for each employee to get a listing of what training they have had. The
main form is based on Employees, and the subform on Enrollment.

In your case, what could you do with a form/subform? If the main form is
based on Company and the subform on Data, would you use a combo box to
select a quarter? That means you would need to edit the Months table to
add a quarter before you could select it. Not much point to that when all
you need is a single field in the Data table to identify a date or
quarter. Or would you build a main form based on the Months table and a
subform in which you would select a company, then add the data?

The other thing I'm finding rather curious is that you are linking text
fields (apparently) between Company and Data, and date fields between Data
and Quarter. Dates make poor linking fields in most cases. For one
thing, how will you enforce their uniqueness? Do you have primary key
fields?

Unless there is more to the Months table than I understand, using Data as
the junction table doesn't seem to accomplish anything that couldn't be
accomplished with Data as the child table (on the many side of a
one-to-many). Whether in this posting or a new one, it is helpful to
describe tables as I have described the hypothetical Data table, in
similar format. You don't need every field, just the key fields. For
instance, for the company table all you need is something like

tblCompany
CompanyID (autonumber primary key)
CompanyName, Address, etc.

When describing a database, include all linking (related fields).

Good luck with the database, and I hope you get that hole in the ceiling
fixed OK.
Each quarter can have many companies
Each company submits only one lot of data per quarter
Not every company though submits data every quarter
The data table has many quarters and many companies

The report I'm trying to produce can have
many quarters
many companies

I need to sit back and think about this. The problem is I inherited this
database and have been asked to streamline it for them. But I only do
this to keep my 61 year old brain active so I'm not really an Access
professional, BUT it's a challenge and I love challenges!
Cheers
Tony

BruceM said:
I am on the east coast of the USA (almost in Canada), and five hours
before GMT.

A few more random thoughts:
I will refer to data submissions as data reports. Regarding the use of
a junction table, each quarter can have many data reports (one each from
any number of companies), but the reverse is not true. A data report
cannot be assoicated with more than one quarter nor with more than one
company. Also, each company can submit many data reports (one per
quarter). A typical many-to-many relationship is the aforementioned
students/courses, whereby each student can take many courses, and each
course can contain many students, so there is a many-to-many
relationship between students and courses. I don't see a similar
relationship in your database. Each quarter can contain many reports,
and each report can be associated with many quarters? No, that's not
it. Each company can submit many reports (one per quarter), and each
report can be associated with many companies? That's not it either.
Each report can be associated with many companies, or each report can be
associated with many quarters? No and no. I just don't see a
many-to-many relationship here, which is why I think that setting up the
relationships as you have cannot lead you where you want to go. I
really think that the quarter information needs to be stored with the
data. Then again, I am somewhat new to this, and may be overlooking
something.

Thanks Bruce. I have a mind to try creating a query based on my
tblmonth and tblmaintabs linked on the quarter date and use the quarter
date from tblmonth as the date for my date range rather than the
quarter date from the tblmaintabs which I'm doing at the moment. If
that works I'll let you know. Right now I have to fix a whole in my
lounge ceiling so I'll have a go tomorrow or this evening. I'm not sure
where you are but I'm in the UK and it's 16.00 so I'm giving Access a
rest for a couple of hours and get back to it when I've conquered my
ceiling!!
Thanks for all your help so far and watch this space!
Cheers
Tony
Comments inline.

Bruce I have added my comments in your post at the relevant places, I
thought this was going to be easy!!!!
Thanks for your help maybe I'll take your advice and start another
thread with a more detailed description and see where that takes me.

Thanks again
Tony
It sounds as if you are using tblmaintabs as a junction table, since
each company will have many data (tblmaintabs) records, and each
quarter will have many data records. That reasoning is valid if you
have students and courses, for instance: each student can take many
courses, and each course can contain many students, so you need an
enrollment table to resolve the relationship. However, I'm not sure
it's the best approach here, unless there's more to tblmonth than I
imagine.

Yes Bruce I am using tblmaintabs as the junction table,each company
has many data (tblmaintabs) records (this is the quaterly data they
submit), and each quarter will have many data records (the table
tblmonth only holds the quarter dates)

I would not have a table just for quarters. I would put the quarter
or the date or something into the Data table, then filter as needed.

From what I understand, you receive the information from a company on
a particular date. If so, DateReceived can be a field in
tblmaintabs.

The data is not so much received on a specific date but is for a
specific quarter and there is a field in tblmaintabs which is the
txtmonthlabel which links the table to the tblmonth

As an experiment, make a copy of your database (which I assume you do
anyhow). Remove all relationships, then create a tblData:

tblData
DataID (primary key, or PK)
CompanyID (foreign key, or FK)
DateReceived
TestData

This assumes CompanyID is the name of the PK field in tblCompany.
Create a one-to-many relationship between the CompanyID fields, then
make a more based on tblCompany and a subform based on tblData
(autoform is handy for these experiments). Create a few subform
records for a few companies, with dates from this quarter for some
companies and not for others. For companies with records from this
quarter, create some records for previous quarters. You can use the
TestData (text) field to make notes for yourself (e.g. Second
quarter data; there are no third quarter data).

If this basic structure works, I'm afraid I need to tell you that
I'm not sure what to do next.

Bruce, if I understand this correctly this is the structure I have

I have described a two-table structure, with a single one-to-many
relationship. You have described a three-table structure with a
junction table, and a one-to-many between each of the other tables and
the junction table.


It's easy enough to select a date range, as you know, and you can
select records from the current quarter, but what you need to do
first is to select records only from companies for whom the most
recent data received was in the current quarter. For those companies
you want to see all the records within the specified date range.
However, if the most recent data are for the past quarter, you don't
want to see any data from that company.

Yes that's exactly what I want, I only want to see data for companies
who ahve submitted data this quarter, I don't want to see data for
the other companies even if they have submitted data in the past.

This is the part of your question that I *cannot* figure out how to
solve. I can figure out how to find records just for a particular
quarter, but when there is a specified date range I do not know how to
exclude from the recordset those companies who have not submitted data
during a particular quarter. I can almost see how to do it (by
treating as inactive those vendors who have not submitted data for the
current quarter, perhaps), but I can't get my mind around how exactly
to accomplish that, if indeed it is a valid approach. I wish I didn't
have to admit I don't see the solution, but the fact is that I don't.
I will keep an eye out for a new thread on the topic so that I too can
learn from this.


I am intrigued by the problem, but have not been able to devise a
solution. I do know that I suggested the criteria in the last posting
based on my flawed understanding of your needs. I should have
understood better than I did based on what you wrote, but I misread
it.

Unless somebody with a suggestion is monitoring this thread, your
best bet would be to start a new thread, including table structure
and relationships. The way I described tblData is a fairly
conventional way of describing the table structure. Sorry I
couldn't be of more help, but I definitely want to learn how to
solve this.

Thanks Bruce, sorry for the delay in replying but I'm in the UK

I have 3 tables in my table structure: tblcompany (holding company
info), tblmonth (holds the quarter dates), tblmaintabs (holds the
data)
tblcompany and tblmaintabs are linked by company name and tblmonth
and tblmaintabs are linked by quarter date.

I know how to link the tables in the query but what I still haven't
understood is how does the criteria relate to the particular
quarter that I am working in? For example from my previous post if
I am in the quarter March 2006 how does the query criteria exclude
companies that haven't submitted data in that particular quarter?

Sorry if I'm missing the obvious but my 61 year old brain sometimes
takes a lot of time to grasp the obvious.

Also I take on board your comments on the lack of information in my
early postings. Big mistake to think you guys can read my mind. Do
really appreciate your help though.

Thanks again
Tony



Your parameter could be something like:

Year([txtmonthlabel] = Year(Date()) And
DatePart("q",[txtmonthlabel]) = DatePart("q",Date) And Between
[Forms]![YourForm]![StartDate] And [Forms]![YourForm]![EndDate]

The part here that isn't so good is that the reports of data
submitted should be related to the company. In other words, there
should probably be a company table, and the quarterly data should
be in a related table. If it is set up this way, combine the two
tables in a query and apply the criteria as described above. If
you need help with that you will need to describe your table
structure.

By the way, it took several posts just to clarify your needs. In
future postings (new threads in particular) you should supply
enough information that people reading the questions don't need to
ask what you mean. That uses more of everybody's time than is
necessary.

What is the point of the date range when what you need is
information about a quarter?

What the report does is show the data that the company have
submitted over the date range that the user inputs. This is to
check if the data that has been submitted for that quarter is in
line with previous quarters.

You say that txtmonthlabel is the quarter, but what do you mean
by that? Is it a date, or an ordinal number (1st, 2nd, etc.)? Is
the year included? Give an example of the data actually stored in
that field.

The txtmonthlabel field is a date field and formatted as mmmm
yyyyy so for say the quarter ending 31st March 2006 would be
shown as March 2006

Are you using the prompt form to provide query parameters? If
so, why is the query returning records that do not meet the
criteria?

Yes the prompt form provides the date range for the query
criteria for the field txtmonthlabel

Does the report include the actual statistics, or just the fact
that there are statistics for the quarter?

The report shows actual statistics. However what I want is say
the last 4, 5 or even 6 quarters (that's the date range variable)
statistics for those companies who have submitted statistics for
this quarter. eg If I am in the quarter March 2006 (the current
value of txtmonthlabel) I want the report to show me statistics
for say March 2005, June 2005, September 2005, December 2005 and
March 2006 BUT only for those companies that have submitted
statistics for March 2006. There will be companies who have
submitted statistics in other quarters but not March 2006 but I
don't want to see them.

Hope this explains it, but please stick with me and if there is
anything else I can add I am obviously happy to.

Thanks
Tony







A few things related to your code: In this line:
strField = "txtmonthlabel"
you are defining strField as the literal characters enclosed in
the quotes. If you mean to define the string in terms of a field
or control:
strField = Me.txtmonthlabel
Same for strReport

For the rest, it is very difficult to figure out what you need.
Perhaps the user could select a quarter rather than a date
range? What is the point of the date range when what you need is
information about a quarter? You say that txtmonthlabel is the
quarter, but what do you mean by that? Is it a date, or an
ordinal number (1st, 2nd, etc.)? Is the year included? Give an
example of the data actually stored in that field.
Are you using the prompt form to provide query parameters? If
so, why is the query returning records that do not meet the
criteria? Does the report include the actual statistics, or just
the fact that there are statistics for the quarter?

Not quite there, probably I didn't give enough explanation.
What I am doing is working on quarterly statistics we get from
external companies. The field txtmonthlabel is the quarter to
which the data relates. Some companies send data every quarter
others miss quarters. The report needs to show only those
companies who have submitted data in the quarter I running the
report against. So I thought that if the value of txtmonthlabel
was null for a particular company there would be no data and
the company wouldn't show on the report. However as I'm running
the report over a date range there may have been data in
previous quarters but I only want companies that have submitted
data in this quarter. Can I do this by adding a control on the
prompt form where the user puts the date range and somehow tie
that control to equal txtmonthlabel?
Hope I've explained that.
Cheers
Tony
Technical detail for the benefit of the OP: no space in
IsNull.

How do I amend this code to check whether my field
txtmonthlabel IsNot Null and then only runs the rest of the
code?

I haven't read the rest of your code but you could just wrap
the whole lot up into an if:

If Not Is Null(txtmonthlabel) Then

'Run my code

End If

HTH - Keith.
www.keithwilby.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top