Getting Data from 2 Queries into 1 Report (Update)

[Alison Downing via AccessMonster.com]

Hi Larry,

I've started a new thread as the last one was getting rather long - I trust
this is acceptable?
Of course, as soon as I read your post and sent off my response, I went
straight onto Access to start putting your advice into practice.

But .... At the risk of being a complete dummy, I'm possibly more confused
now than before but I'm hoping this is because I have not explained the
purpose of the two tables as clearly as maybe I might have done.

In your response; you said
" ... Because you should be relating the Invoice and
the Payment in a one-to-many relationship. [ By the way, since you've
named your tables ...Log I infer that this is a parallel application
to something else that generates your actual invoice and that you may
be entering your payments received manually or ?? ]"

The key bit is "payment received".
My 'Payments' table does not represent payments received against invoices
(if it did, your comments make perfectly good sense to me and I understand
exactly what you are saying) but the 'Payments' table is actually a record
of the expenses incurred in my business. I log 'payments' from the
receipts I get when I buy things like fuel or stationery, which are totally
separate from invoices, with each cost being a stand-alone record.

Larry, I'm at a loss to understand how I can relate the two tables except,
perhaps, by the 'date' field.
When I first started building the db, I did try to get everything on one
table, with a field that indicated whether the transaction type was an
'invoice' or a 'payment' but got really hung up because I needed to
calculate 'invoices' from the value before tax and 'payments from the value
after tax.

I apologise for throwing this straight back to you. Hope you get me back on
the right track.

Thanks
Alison

 

[David Seeto via AccessMonster.com]

Hello Alison,

I had to track down your previous post at
http://www.accessmonster.com/Uwe/Forum.aspx/access-
gettingstarted/10289/Getting-Data-from-2-Queries-into-1-Report so I could
make sense of this.

It sounds like to me that there isn't any inherent relationship between
your Invoice tables and your Payments table - in that case, to put them
both onto a report, you should use a UNION query instead of an inner join.

To do this, both queries need to have the same number of columns. Try:

InvoiceQuery
[Date] -> rename to [InvoiceDate]
[NetPrice]
[InvVATCalc]
[Customer]
[DatePaid]
0 as [NetCalc]
0 as [VATCalc]

PaymentQuery
Null as [InvoiceDate]
0 as [NetPrice]
0 as [InvVATCalc]
"N/A" as [Customer]
[Date] --> rename to [DatePaid]
[NetCalc]
[VATCalc]

You can then build ReportQuery as:
SELECT * from InvoiceQuery
UNION SELECT * from PaymentQuery

And you should be able to apply your criteria to the [DatePaid] column in
this query.

Note - the column names will be those of the first query mentioned
(InvoiceQuery in this case) - the columns from the second query will be
assigned to those names left to right (ie. DON'T put [DatePaid] as the
first column in PaymentQuery, or it will appear in the [InvoiceDate] column
of the union query).

Let us know how it goes...

 

[Alison Downing via AccessMonster.com]

Hi David,

Thanks for your response. I see exactly where you are trying to take me
here - funnily enough, I'd had a go with a Union Query previously but I
hadn't managed to get all the columns I needed into both queries.

I've built the queries that you suggest and I must still be doing something
wrong as I'm getting three times the number of records that I would expect
from the union query.

Can we take this in simple stages please?

When I run the InvoiceQuery I get thousands of records (I was expecting 30!!
)

Can you please explain exactly what I need to enter into the last two
columns of [InvoiceQuery] as I don't understand exactly what you mean by "0
as [NetCalc]". Access didn't recognise the syntax when I typed it so I
tried "0=[NetCalc] but I guess that was very wrong.

I'm determined to get the hang of this eventually!!!

Regards
Alison

 

[David Seeto via AccessMonster.com]

Sorry, I've been working with stored procedures lately and was using SQL
syntax instead Access query design...

[NetCalc] and [VATCalc] are columns that contain data applicable to
Payments and not to Invoices, but we need to include them in InvoiceQuery
so we get the right columns when we build the Union query. As the figures
aren't applicable to Invoices, we will put the constant value 0 for both of
them. In the Access Query Designer, what you want to put in "Field:" for
the two columns is:
NetCalc: 0
VATCalc: 0

What we are doing here is creating an expression - the text on the left of
the colon is the name of the expression (which will become the name of the
column) and the actual expression is to the right of the colon - in this
case, a constant value of 0.

You'll notice that if you put in Field: the value 0, that Access
automatically puts Expr1: in front of it - what's happened here is that
Access doesn't recognise 0 as the name of a field in one of the query's
tables, so it assumes that it's an expression and gives it a default name.
You can change the name of the expression without changing the expression
itself by changing the Expr1 text.

-------------
As to InvoiceQuery returning thousands of records instead of 30, that won't
have anything to with the columns, but more likely with the way that you've
joined the underlying tables together. To debug this, you'll have to post
the table and field names, as well as explain how you are (and how you'd
like to) joining them together...

I'm not in the office over the weekend or on Monday (Anzac Day public
holiday here in Australia), but will check in on this thread from Tuesday
onwards... sorry if that timing is a problem for you.


Cheers,
David

 

[Alison Downing via AccessMonster.com]

Hi David,

Hope you've had a great weekend!! I can certainly wait a few days for
clear, helpful and easy-to-follow advice like yours.
Your reply was brilliant and my queries are working perfectly now after I
completely rebuilt them from scratch.

I now need to show the results on a report, using some date parameters.
I've put the date parameters in the base queries as follows;

Query1: InvoiceQueryforVATForm
[InvoiceDate]
[NetPrice]
InvVATCalc]
[DatePaid]Between [Start Date[ And [End Date]
[NetCalc]=0
[VATCalc]=0

Query2: PaymentQueryforVATReport
[InvoiceDate]=Null
[NetPrice]=0
[VATCalc]=0
[PaymentDate]Between [Start Date] And [End Date]
[NetCalc]
[VATCalc]

I've put both these into a Union Query as you suggested;

Query3:VATReportQuery
SELECT * FROM InvoiceQueryforVATReport
UNION ALL SELECT * FROM PaymentQueryforVATReport;

(I had to 'Union All' to get all the records in from the PaymentQuery)

When I run all three queries individually, they all return exactly the data
I would expect - yippee!! (I understand your point about relationships on
the tables but, at the moment, I have no relationships at all in the db so
I guess the ‘thousands of records’ problem was somewhere in the original
queries that I’ve now rebuilt).

The challenge I have now is trying to get everything onto the report. I've
built a report from the union query 'VATReportQuery' using the following
fields;
[ DatePaid]
[NetPrice]
[InvVATCalc]
[NetCalc]
[VATCalc]
This report asks for the Start Date and End Date but then only returns the
records that are generated from my first query 'InvoiceQueryforVATForm'.
It doesn’t seem to be picking up any of the records from
‘PaymentQueryforVATReport’ although the union query runs perfectly well.
I generated the report using the wizard; should I be using design view to
build it, or have I missed something really obvious?

Many Thanks
Alison

 

[David Seeto via AccessMonster.com]

Hi Alison,

Glad you sorted out the number of records issue - and you didn't even need
my help after all!

If all of your queries run fine individually, I don't know why the report
doesn't bring back the required records as well: I tried setting up
something similar in a test database, and it worked OK. Some of the things
you could try, though are:

* Instead of having the [Start Date] and [End Date] parameters in the base
queries InvoiceQueryforVATForm and PaymentQueryforVATReport, you could put
them into VATReportQuery like so:
SELECT * FROM InvoiceQueryforVATReport
WHERE [DatePaid] between [Start Date] and [End Date]
UNION ALL SELECT * FROM PaymentQueryforVATReport
WHERE [DatePaid] between [Start Date] and [End Date];

Like using them in the underlying queries, you need to make sure that the
parameters [Start Date] and [End Date] are spelled exactly the same way. My
preference would probably be to:

* Create another Query on top of VATReportQuery (say,
VATReportQueryParameterised) like so:
SELECT * FROM VATReportQuery
WHERE [DatePaid] between [Start Date] and [End Date]

Unlike the UNION query, you can create this using the Query Designer rather
than having to mess around in SQL. It's not as efficient as your original
queries (since Access will retrieve all records and then omit some of them,
instead of just retrieving fewer records in the first place), but I don't
think this is going to be an issue with the volumes you've got, and I think
it's easier to understand.

If the last idea above doesn't work either, then there maybe your problem
is something to do with the data types or contents of the date columns in
your two underlying tables - let me know how it goes...

Cheers,
David

 

[Alison Downing via AccessMonster.com]

David,
You are a gem!!! Your idea of putting the start date and end date into the
union query has worked a treat and I now have a union query that returns
does exactly what it says on the tin!! and the preliminary design for the
report is looking really good;

I have one last parameter that I need to add in and I'm hoping I can also
do it in the union query. Some of the entries in my 'PaymentsLog' do not
have a VAT calculation as they are exempt from tax (How much do I hate the
UK tax system?) and I need to extract these from my union query. I
couldn't take them out in the VATReportInvoiceQuery or VATReportPayment
Query because I have a Yes/No field in my 'PaymentLog' table (which has no
matching field with 'InvoiceLog') that indicates whether VAT is applicable
and I still need the basic figure from the 'PaymentLog' for my accounts.

I believe the key to this is that, where there is no VAT applicable, the
[VATCalc] field in VATReportPaymentQuery returns a value of 0. I have
tried adding an extra parameter line to my union query but I think I am
using the wrong words as I either get a 'syntax' error message or the union
query does not give me any records at all from VATReportPaymentQuery.

My existing union query is;

SELECT * FROM VATReportInvoiceQuery
WHERE [DatePaid] between [Start Date] And [End Date]
UNION ALL SELECT * FROM VATReportPaymentQuery
WHERE [DatePaid] between [Start Date] And [End Date]

I think I need something along the lines of;
AND[VATCalc]>0
at the end.

I apologise for throwing a spanner in the works at this late stage - it
wasn't until I ran the report and checked it against my manual calculations
that I realised I needed to extract zero VAT records.

Can you advise me if this is the right way to go about this, and if so, how
I should define this additional parameter.

Many Thanks
Alison

 

[David Seeto via AccessMonster.com]

Hello again Alison,

I don't think I'm quite following what you need to do here - you say you
need to "extract" "the entries in my 'PaymentsLog' [that] do not
have a VAT calculation" - does that mean you want to exclude them ie. you
don't want those records to appear in your report?

I'd have thought so, but you go on to say that "I still need the basic
figure from the 'PaymentLog' for my accounts".

You can leave the PaymentsLog table alone, but I would have thought you
could modify PaymentQueryforVATReport so that it had returned only those
records where [VATapplicable] = Yes.

I can see your concerns about messing up PaymentQueryforVATReport for the
UNION query, but you can limit the records by the contents of a column that
you don't display. In SQL , just don't include the column in the SELECT
clause, or through the Query Designer uncheck the "Show" checkbox for that
column after you've doule clicked on it andentered the criteria...

 

[Alison Downing via AccessMonster.com]

Hello David,

I'm sorry that I didn't explain things very well, but you were right in
your assumption that I needed to exclude records without VAT from the
report. Your advice to modify the PaymentQueryForVATReport has worked
perfectly and I now have exactly the report that I was trying for - all
thanks to you!

I cannot thank you enough for your time and your clear, easy to understand
explanations which have guided me through step-by-step.

The database, which I was building for a friend of mine, is now complete
..... now, I start work on my own one which, although similar, will have
some different calculations and fields pertinent to my own business. Thanks
to you, I feel much more confident about building my own one and,
hopefully, will sail through it. (Although it's good to know that help is
available if I need).

With my best regards
Alison

 

[David Seeto via AccessMonster.com]

Glad to have been of help Alison - you've been a terrific listener and made
it easy to help you

Personally, I tend to scan for things in these forums every few days and
see if I can help those people who haven't had any replies yet... I don't
really read anything that has a reply, since I assume that someone else is
helping them out already... just something to keep in mind if you're trying
to get my specific attention, but I'm sure that others will be able to
answer any questions you do have.

All the best, and it's been a pleasure,
David... ?8^)