C
Chris Calamera via AccessMonster.com
All,
I am trying to find a solution for transferring the process of a monthly
report to a new system that is structured a lot different than its
predecessor. My situation involves the following:
I have 3 tables: Invoice, Circuit Amount, and Telephone Amount. The
invoice table has an InvoiceID that ties into the Circuit Amount and
Telephone Amount tables in a one-to-many scenario. The key fields for my
query are "bill date" in the invoice table, “charge amount” in the other
two tables and “circuit number”, also found in the other two tables – the
invoice table is here just for the bill date field.
What I am currently doing is taking the circuit amount and telephone amount
tables separately and running a query with a group by clause to get each
individual circuit number and the sum of their charge amounts. So far this
has worked, but the new system has not been filled with monthly invoices
yet, so this solution will only work until june when the invoices turn over
for the second month of billing.
What I need to do is devise a query or code of some sort that will run this
group by query with its costs for the prior 30 days up until the date of
the macro it is tied to begins to run. Example:
(the following data will be based on the “Bill Date” in the system)
If I run the report May 1st, it will pick up all data where the “Bill Date”
is on May 1st.
If I run the report May 8th, it will pick up all data where the “Bill Date”
is on May 1st through May 7th.
If I run the report May 15th, it will pick up all data where the “Bill
Date” is on May 1st through May 15th.
If I run the report May 22nd, it will pick up all data where the “Bill
Date” is on May 1st through May 22nd.
If I run the report May 29th, it will pick up all data where the “Bill
Date” is on May 1st through May 29th.
If I run the report June 5th, it will pick up all data where the “Bill
Date” is on May 6st through May 31st and then June 1st to June 5th.
If I run the report June 12th, it will pick up all data where the “Bill
Date” is on May 13th through May 31st and then June 1st to June 12th.
What would be the best way of going about this? Right now I have this set
up:
SELECT BILLTAMER_MIE_CKT_AMT.EC_CKT_ID, Sum(BILLTAMER_MIE_CKT_AMT.CHG_AMT)
AS SumOfCHG_AMT
FROM BILLTAMER_MIE_CKT_AMT
GROUP BY BILLTAMER_MIE_CKT_AMT.EC_CKT_ID;
This will pick up all the data in the system, but once we cross over to the
second round of billing next month, these numbers will not be accurate. I
need to know how to query this data correctly for the weekly reports that
will follow the data needs of the above description.
Any assistance would be greatly appreciated – your site has been nothing
but helpful to me these past 6 months with my projects and I hope someone
can help me out with this. If any more information is necessary to post,
please do let me know.
Thanks in advance.
Chris
I am trying to find a solution for transferring the process of a monthly
report to a new system that is structured a lot different than its
predecessor. My situation involves the following:
I have 3 tables: Invoice, Circuit Amount, and Telephone Amount. The
invoice table has an InvoiceID that ties into the Circuit Amount and
Telephone Amount tables in a one-to-many scenario. The key fields for my
query are "bill date" in the invoice table, “charge amount” in the other
two tables and “circuit number”, also found in the other two tables – the
invoice table is here just for the bill date field.
What I am currently doing is taking the circuit amount and telephone amount
tables separately and running a query with a group by clause to get each
individual circuit number and the sum of their charge amounts. So far this
has worked, but the new system has not been filled with monthly invoices
yet, so this solution will only work until june when the invoices turn over
for the second month of billing.
What I need to do is devise a query or code of some sort that will run this
group by query with its costs for the prior 30 days up until the date of
the macro it is tied to begins to run. Example:
(the following data will be based on the “Bill Date” in the system)
If I run the report May 1st, it will pick up all data where the “Bill Date”
is on May 1st.
If I run the report May 8th, it will pick up all data where the “Bill Date”
is on May 1st through May 7th.
If I run the report May 15th, it will pick up all data where the “Bill
Date” is on May 1st through May 15th.
If I run the report May 22nd, it will pick up all data where the “Bill
Date” is on May 1st through May 22nd.
If I run the report May 29th, it will pick up all data where the “Bill
Date” is on May 1st through May 29th.
If I run the report June 5th, it will pick up all data where the “Bill
Date” is on May 6st through May 31st and then June 1st to June 5th.
If I run the report June 12th, it will pick up all data where the “Bill
Date” is on May 13th through May 31st and then June 1st to June 12th.
What would be the best way of going about this? Right now I have this set
up:
SELECT BILLTAMER_MIE_CKT_AMT.EC_CKT_ID, Sum(BILLTAMER_MIE_CKT_AMT.CHG_AMT)
AS SumOfCHG_AMT
FROM BILLTAMER_MIE_CKT_AMT
GROUP BY BILLTAMER_MIE_CKT_AMT.EC_CKT_ID;
This will pick up all the data in the system, but once we cross over to the
second round of billing next month, these numbers will not be accurate. I
need to know how to query this data correctly for the weekly reports that
will follow the data needs of the above description.
Any assistance would be greatly appreciated – your site has been nothing
but helpful to me these past 6 months with my projects and I hope someone
can help me out with this. If any more information is necessary to post,
please do let me know.
Thanks in advance.
Chris