Getting Data from 2 Queries into 1 Report

[Alison Downing via AccessMonster.com]

Hello,

I'm sure there's a simple answer to this but despite searching this site I
can't seem to find what I need, and although I think I might have some
ideas to follow up, I decided it was time to ask the experts.
I've seen many postings where the first response requests more details so
I'll try to give you everything in one hit and trust that I haven't
overdone it.

I'm run a sole-trader business in the UK, registered for VAT, trying to
build myself a simple(ish) database that will help me manage my accounts.
Every three months, I have to make a report which shows the total amount I
invoiced before tax, and the tax amount for those invoices, and the total
amount I paid out before tax and the tax amount for those payments. I've
set up two tables which record all my transactions and, from them, I have
two queries; one shows income, one shows payments as follows;

InvoiceQuery
[Date] ..... the date that the invoice was raised
[NetPrice] ..... net value of invoice before tax
[InvVATCalc] ...... calculated tax value
[Customer]
[DatePaid] .... the date the invoice was paid (because I only have to
include the total in my report if the invoice has been paid.

PaymentQuery
[Date] .... the date the payment was made
[NetCalc] .... net value of payment
[VATCalc] .... calculated TAX value

I'm trying to build a report that shows totals for;
[InvVATCalc] ... from InvoiceQuery
[VATCalc] ... from PaymentQuery
[[Net Price] ... from InvoiceQuery
[NetCalc] ... from PaymentQuery

BUT ... I need the report to only pull the records where
[InvoiceQuery.DatePaid] or [PaymentQuery.Date] are between an entered date
range.

What I can't seem to get right is how to get all 4 fields from the 2
queries onto the report. I followed Access Help instructions and got a
message that I'd chosen fields that the wizard could not connect. I've
tried building a relationship between the queries on [InvoiceQuery.DatePaid]
and [PaymentQuery.Date]but this returned me 3 times the number of records I
was expecting.

I found out how to make a 'totals' field on a report from one of the forums
and I've set up a separate form for entering the date criteria when I can
get everything else working.

Should I be putting each field in as a subreport or is there a simpler
solution?

Any advice would be helpful, I've been at this now for nearly a week and
I'd really like to move on to another area of this database .

Thanks
Alison

 

[Larry Daugherty]

Hi Alison,

You still need to communicate your table design and any relationships
you've established.

FWIW I'd rename any field that has a keyword as a name - such as DATE,
to something else such as InvoiceDate or BillDate or something like
that.

HTH
--
-Larry-
--

"Alison Downing via AccessMonster.com"

Hello,

I'm sure there's a simple answer to this but despite searching this site I
can't seem to find what I need, and although I think I might have some
ideas to follow up, I decided it was time to ask the experts.
I've seen many postings where the first response requests more details so
I'll try to give you everything in one hit and trust that I haven't
overdone it.

I'm run a sole-trader business in the UK, registered for VAT, trying to
build myself a simple(ish) database that will help me manage my accounts.
Every three months, I have to make a report which shows the total amount I
invoiced before tax, and the tax amount for those invoices, and the total
amount I paid out before tax and the tax amount for those payments. I've
set up two tables which record all my transactions and, from them, I have
two queries; one shows income, one shows payments as follows;

InvoiceQuery
[Date] ..... the date that the invoice was raised
[NetPrice] ..... net value of invoice before tax
[InvVATCalc] ...... calculated tax value
[Customer]
[DatePaid] .... the date the invoice was paid (because I only have to
include the total in my report if the invoice has been paid.

PaymentQuery
[Date] .... the date the payment was made
[NetCalc] .... net value of payment
[VATCalc] .... calculated TAX value

I'm trying to build a report that shows totals for;
[InvVATCalc] ... from InvoiceQuery
[VATCalc] ... from PaymentQuery
[[Net Price] ... from InvoiceQuery
[NetCalc] ... from PaymentQuery

BUT ... I need the report to only pull the records where
[InvoiceQuery.DatePaid] or [PaymentQuery.Date] are between an entered date
range.

What I can't seem to get right is how to get all 4 fields from the 2
queries onto the report. I followed Access Help instructions and got a
message that I'd chosen fields that the wizard could not connect. I've
tried building a relationship between the queries on [InvoiceQuery.DatePaid]
and [PaymentQuery.Date]but this returned me 3 times the number of records I
was expecting.

I found out how to make a 'totals' field on a report from one of the forums
and I've set up a separate form for entering the date criteria when I can
get everything else working.

Should I be putting each field in as a subreport or is there a simpler
solution?

Any advice would be helpful, I've been at this now for nearly a week and
I'd really like to move on to another area of this database .

Thanks
Alison

 

[Alison Downing via AccessMonster.com]

Hi Larry,

I suppose I should've guessed I'd leave something out. OK - here's the
table designs;

Table: InvoiceLog
[IDNo]
[Date] ...this is the date the invoice was raised
[Customer]
[InvoiceNo]
[NetPrice]
[DatePaid] ... the date the invoice was paid

Table: PaymentsLog
[IDNo]
[Date]
[Type] ... this is a lookup field that records different types of payments
e.g. cash, sundry, fuel
[Details] ... text field
[TotalCost]... the total payment made including tax
[VATApplicable] ... yes/no field for indicating whether tax is applicable
(because some things you buy aren't subject to tax)

I also have two forms;

Form: InvoiceLog
This has all the fields in the Invoice Log table plus two calculated fields;
[InvVATCalc]
[InvTotalCalc]
Both fields are derived from a query based on TableaymentsLog using
expressions;
"InvVATCalc: [Net Price]*0.175"
"InvTotalCalc: [Net Price]+[InvVATCalc]"

Form: PaymentLog
Again, this has all the fields in the Payment Log table plus two calculated
fields;
[NetCalc]
[VATCalc]
Again, both fields are derived from a query based on TableaymentsLog
using expressions;
"NetCalc: IIf([VAT Applicable],[Total Cost]/117.5*100,[Total Cost])"
"VATCalc: IIf([VAT Applicable],[Total Cost]-[NetCalc],0)"

I have no relationships set up for any of the tables or forms because, when
reporting the figures, Invoice VAT is completely different from Payment VAT
and I must total them separately.
The date of payment [PaymentsLog.Date] and the date the invoice was paid
[InvoiceLog.DatePaid] are relevant only for retrieving the data within a
selected time period.

I've entered three months data onto the forms and everything is working
beautifully, including the queries that pull the records I need for the
report.

Hope this helps. Just please let me know if you need any more info.
Thanks
Alison

 

[Alison Downing via AccessMonster.com]

BTW - Thanks for your advice about 'Date' fields and similar. If I rename
the field in the table, the new name updates automatically in queries,
forms and reports .... doesn't it?

 

[Larry Daugherty]

Hi Alison,

I won't be giving you the help you hoped to receive. I'm going to
make some critical observations and pass on some information. Don't
take any of it as a personal attack and please do take it all as
earnestly intended advice.

The reason people are often asked to provide the details of their
table design is that the table design is key to all that follows in
the implementation of your application. Even though I don't know the
specifics of what you're doing with your application I can see that
your over-all table design is incorrect.

"How come", you ask? 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 ?? ] You create a
one-to-many relationship between two tables by including a copy of the
Primary Key of the main/parent/One table in the secondary/child/many
table as a Foreign Key. Assuming each table will have an autonumber
Primary Key then the Foreign key field in the 'many' table is simply a
field storing a Long Integer number and having the same field name as
the Primary Key of the 'one' table. It has the sense of "I am the
child of the record in the 'one' side table with the value of ...".
Just to confuse the issue a bit more, there is no special datatype for
a Foreign Key. It is simply a meaning assigned to a field (or
possibly several fields if you're using a natural Primary Key in the
'one' table. It is generally sufficient if you simply enter the
words "Foreign Key in the description of the field in your table
design. By the way, if you are using Autonumber surrogate primary
keys, it is common to give them the table name with the two letters
"ID" tacked on the end.

How does it work? Once your tables are designed, go to the
Relationships window and Add the two tables under consideration. Draw
a line between the Primary Key in the 'one' table to the Foreign Key
in the 'many' table. Enforce Referential Integrity and enable
Cascading Deletes.

One more note about "how it works"; The parent does not maintain a
list of its children. The children identify themselves as being the
child of the parent whose Primary Key value is contained in their
Foreign Key field(s).

Oops! I've been giving you suggestions assuming that you use a
standard naming convention. It is recommended that you do so. The
Reddick naming convention is widely adopted. It's very useful to have
done so if you ever come back to your code sometime in the future.

I usually name tables "tbl" plus the singular of the name of the thing
its records describe. tblInvoice and tblPayment would cover the two
tables you've described so far.

While we're talking about your tables, tblCustomer would participate
in a one-to-many relationship with tblInvoice. The same PK & FK
relationships apply so you should be sure to include that in your
re-design and in the Relationships window referenced above: You'll
have three tables in a hierarchy.

When you re-design your tables, don't include any Lookup Fields!
They are an abomination and, while they may be very helpful in an
application that will remain forever primitive, they will lead to
troubles later (particularly noticed as queries presenting unexpected
data).

Notice I didn't say lookup tables. Lookup tables are a good thing.
They are simply regular tables that get the "lookup" name from their
intended use: to hold a list of things that other parts of your
application can lookup and have inserted into the current record, thus
avoiding typographic errors and miss-spellings. PaymentType might be
such a list.

There are several other Access related newsgroups, among them:
tablesdesign, forms, formscoding, queries, reports, gettingstarted.
There are lots of books on Access. Check them out before you buy.
There is also an excellent site filled with Access lore:
www.mvps.org/access

HTH
--
-Larry-
--

Hi Larry,

I suppose I should've guessed I'd leave something out. OK - here's the
table designs;

Table: InvoiceLog
[IDNo]
[Date] ...this is the date the invoice was raised
[Customer]
[InvoiceNo]
[NetPrice]
[DatePaid] ... the date the invoice was paid

Table: PaymentsLog
[IDNo]
[Date]
[Type] ... this is a lookup field that records different types of payments
e.g. cash, sundry, fuel
[Details] ... text field
[TotalCost]... the total payment made including tax
[VATApplicable] ... yes/no field for indicating whether tax is applicable
(because some things you buy aren't subject to tax)

I also have two forms;

Form: InvoiceLog
This has all the fields in the Invoice Log table plus two calculated fields;
[InvVATCalc]
[InvTotalCalc]
Both fields are derived from a query based on TableaymentsLog using
expressions;
"InvVATCalc: [Net Price]*0.175"
"InvTotalCalc: [Net Price]+[InvVATCalc]"

Form: PaymentLog
Again, this has all the fields in the Payment Log table plus two calculated
fields;
[NetCalc]
[VATCalc]
Again, both fields are derived from a query based on TableaymentsLog
using expressions;
"NetCalc: IIf([VAT Applicable],[Total Cost]/117.5*100,[Total Cost])"
"VATCalc: IIf([VAT Applicable],[Total Cost]-[NetCalc],0)"

I have no relationships set up for any of the tables or forms because, when
reporting the figures, Invoice VAT is completely different from Payment VAT
and I must total them separately.
The date of payment [PaymentsLog.Date] and the date the invoice was paid
[InvoiceLog.DatePaid] are relevant only for retrieving the data within a
selected time period.

I've entered three months data onto the forms and everything is working
beautifully, including the queries that pull the records I need for the
report.

Hope this helps. Just please let me know if you need any more info.
Thanks
Alison

 

[Alison Downing via AccessMonster.com]

Hi Larry,

My apologies for not responding before but I've been working away from home
for two days.
WOW!!!! One step forward, 17 steps back!!!
I can see I need to do a complete redesign of my entire database but I am
not discouraged - I'd rather do the thing right, even if it takes me
another month. I guess I'll be back with another daft question at some
point but, hopefully, there'll be a better underlying structure to work
with.

I really appreciate your advice and for taking the time to explain things
so clearly. Now I shall act on it!!

Many, many thanks
Regards
Alison

 

[Larry Daugherty]

Alison,

You're welcome. Good luck with your project.