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
