Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Am totally stuck up! There are 2 tables : 1 for invoice information and other
table with Cancellation information related to invoice table on doc no, as
cncl table would be used when an invoice issued needs to be cancelled.

I need report where when i give month range, total transactions from invoice
table comes and total transactions from cancellation tables comes meeting
said month parameter.( Invoice table has invdate field and Cancellation has
cnclddate field besides other.) Also if i group on say products, report
should show
products Sales Cancellation Net
x
y

I have created month parameter. Also have created query with net amount. But
when i run query with join on transno(all records from invoice and only
related records from cancellation) it does'nt show credit note of those month
where invoice is out of range in query, which is deceptive. If i link tables
on month, credit note information does'nt come and net amount doesnt get
calculated. In case i use both trans no and month, it results in non
desirable results... Please advice if there is way that when i run month
parameter with months, it shows all records from both tables for month and
they are grouped and net amounts comes.

Thanks in anticipation.
 
Are you running the month parameter on a field in the main table r subtable?
It should be on the main table. If the desired data isn't showing up, you
are linking the wront fields. A good idea is to have an autonumber in the
main table as the Primary Key and a long integer assigned for the matching
records. I think the problem is in the linking or the field you are
filtering on.

God Bless,

Mark A. Sam
 
It seems to me that the description of the query and the implied
description of what you want do NOT agree.

The query says get all Invoices for a range of dates and all credit
amounts for those particular invoices.

However your description implies you are seeking something else:
"when i run query with join on transno(all records from invoice and
only
related records from cancellation) it does'nt show credit note of those
month
where invoice is out of range in query, which is deceptive."

This description seems to imply that you want all invoices for a
certain date range AND you want ALL credit amounts no matter what the
date is. (It would seem to me that that is wrong since you would/could
be adding up Credits from way way back depending on how long you keep
information in your table.) You are going to have to decide what
credits you want and possibly make a separate query for that and then
summarize it by product and then have a query for invoices alone summed
by product but selected by month and then join those two queries by
product to get what you seem to be asking for. Of course this can lead
to a negative net since you could have more credits than you have
invoices for a particular month.

Ron
 
Thanks very much for your suggestions.
Am adding Product ID in my Cancellation table too as this field was not
there in it.
If i add, i can create query on Product on Cancelltion table. Else i have to
link cancellation table through invoice table for getting product id, as only
invoice table is linked with product ID. If i link cancellation by bringing
invoice table, my result goes deceptive in case in the month range, invoice
is out of range and its associated credit note is available and is in the
month range, but credit note does'nt come at all. Please correct in case i am
wrong.
 
Dear Ron! Have linked both tables on base of product id but when i run query
row from credit note tables repeat till it fills no of invoice rows! should i
group on product id in query and then link in final query/
 
Are you linking the cancelation data only by product ID? It would seem
that it needs to be by both product ID and Invoice. I don't believe
that any summarization by product ID will help since it would include
ALL cancellations regardless of date. and what I think you want to
include is all products for invoices for a range of dates and all
cancellation amounts for those products for those invoices for that
time frame. If you create the major link by product id and then add
the condition that the invoice number on the cancelation matches the
invoice number of the selected record. That way cancelations for the
same product but for invoices that were not selected would not be
accumulated.

Be sure that the link says to use all from the invoice side and only
those from the cancelation side that match by product.

Ron
 
Sorry for putting much so question!! am Reallly stuck up!!
I would require cancellation and invoice for partiucular month irrespective
whether cancellation invoice pertains to previouos period or current period.
Basically
all Cancellation for month (irrespetive of invoice Issued )and all invoice
for month. So that even there's only cancellation for product, it should come
as negative.
1..I created 2 total queries, one on invoice and one on cancellation, but
in report, I need information as below which am not able to do. I have not
linked bot queries.
Product Invoice Amt Credit Note AMount Net Amount
x
y
z
2. I repeated steps of 1 but i linked both query on product id only in one
more query. Here cancellation is repeating of in each row. I dont know how to
build report on this.
SO have posted what alternate i have followed. pls guide me step by step Sir.


How should i show both invoice and cancellation lined up and net amounts get
calulated. If i link bot queries in one more query, i dont see desired result.

2. What happens
 
OK..... If we do this a few more times maybe we will understand each
other and I will understand what you are looking to produce.

Let me rephrase what I think you want. You want to produce a report by
product that shows the sum of all invoice amounts for a selected month
and the sum of all Credit amounts for that selected month and the net
of the two amounts.

Lets take the following example with the assumption that these are all
in the same month.
Invoice 1 Product A
Invoice 2 Product A
Invoice 2 Product B
Invoice 3 Product C
Credit 1 Product A
Credit 2 Product B
Credit 3 Product X

because of that last credit I believe you will need to create a table
to house the intermediate results

Report Table
Product # Invoice Amt Credit Amt Net Amt (This is
optional but clearer)

1) Query to empty table
2) Sum query for Invoices and append for all products (selected by
month)
Append the Product and the Invoice Amount
And the Net amount should also be loaded with the Invoice
Amount
3) You need to create a sum Query for the Credits by product - selected
by month
4) Create an append query for the table that links the table with the
step 3 query but that has a condition that the Table product id is
null. (Link criteria says All itmes in Credit Query and those from
table that match BUT there is an additional criteria on the Table
product ID field that it is null) This will add the Product X to the
table
5) Update query with Credit Query matched to Sum Table by product ID
put the Credit amount into the Credit field (You may want to
multiply it by -1 to have it look like a credit.)
(Or use formula [tblCredit] - [qryCredit Amt])
update the Net amount with formula of [net amount] - [credit
Amount]

You now have a table with your report in it and can display/report it
with whatever other queries please you.

Ron
 
I used below logic but requirement was slightly different!! It was little
simpler than below requirement. Its prefectly fine.Thanks a lot for your
help!!It was needed urgently.
 
Back
Top