Referencing a background table

[Fee08]

Hi, I am having problems in getting data to fall into a particular date range.
I initially tried to query on the date entered however, because we don't use
calendar months this proved challenging when working with progressive
quarterly and yearly figures.
I have now created a table that contains every day and it's corresponding
month and quarter. What I would like to happen, is that when the user inputs
the date, in the background the corresponding table is referenced. I will
then be able to sort by date and the reference fields which will allow me to
group.
Any thoughts???
Fee.

 

[Tom Wickerath]

What I would like to happen, is that when the user inputs

the date, in the background the corresponding table is referenced.

Are you using different tables to store similar data? It sounds like you
might have different tables for each quarter. If this is true, then your
database design is not correct. All data of this nature should go into a
single table.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[George]

Okay, welots of questions to sort out before someone can help you. Here are
a few starters.

1: What does it mean to say that you are "having problems in getting data
to fall into a particular date range."
2: If you don't use calendar months, what time periods do you use?
3a: Is it correct to assume that each record, when entered, includes a value
in a field called "DateEntered"?
3b: If so, what is the format of that value? Is it a date, e.g. 4//28/2008?
4: You reference " in the background the corresponding table is referenced",
which implies to me, at least, you have more than one table with similar
data. If that is true, it raises a series of additional questions about the
table design in this database. What tables are being referenced and why more
than one?

WIth a better picture of the database, a useful answer might be forthcoming.

Best of luck with this project.

George

 

[Fee08]

Hi George,
To answer your questions as succintly as possible.
1. The data needs to be grouped on the 29th of one month to the 28th of the
next over a quarter or year broken down by month.
2. The month periods work on the 29th of one month to the 28th of the next.
3. I have to pull data from 2 tables. Both tables have a DateEntered field
with the format set to ShortDate
4. The data has been broken into 5 tables.
TblProperty - contains the property details
TblContract - contains the contract details
TblListings - contains the listing details
TblCommissions - contains the commission %
TblRep - contains the sales representatives.

Tbl Property has a one to many relationship with tblcotnract and tbllistings
TblCommissions has a one to many relationship with tblcontract
tblRep has a a one to many relationship with tblcommissions and tbl listings
These tables are joined by a property table. Over time, one property may
have many listings and many contracts.

What I really want to be able to do is to create a report that requires
total bonusable dollars (commissions) to display a summary $ figure by month
(29th to 28th) and then underneath this figure a running sum of the past 12
months (29th to 28th) on a progressive basis.
E.G.
May 08 $10000
12 Mths to May 08 $150000

As a side note, I haven't been able to get the running sum function to sum
for the past 12 months on a progressive basis even when I use a normal
calendar month.

The report is based on a query which is:
[Repname] - The name of the rep who has listed or sold the property.
[U/C] - This is a Yes/No field - and the query will only pull the records
that have this field as yes.
[U/CDate] - This is the date when commission becomes payable to the rep.
[RepList$] - This is a calculated field that works out the bonusable $'s for
the listing of a property
[RepSold$] - This is a calculated field that works out the bonusable $'s for
the sale of a property
[Bonus$] - This field adds [RepList$] & [RepSold$]
I've separated the List and Sold formula because it makes it easier to read
for me.

The info is pulled from three tables.
tblRep - contains the rep's name and is linked via a 1 - many relationship
to tblCommissions.
tblCommission - contains the purchase price ([contractprice]) of the
property, the % rate of commission + any deductions required and is the
figure that all formulas are based on.
tblContract - contains the fields [U/C] set to Yes and [U/Cdate] set to
between date A and date B as the conditions of the query.

At this stage I am totally lost as to what to do. Any help would be
appreciated.