Query Question

[Thomas Morrison]

I'm new to Access, and having tried in vain to solve the following problem,
I would now be grateful for some help.

Situation:

1. Three tables exist (amoung others)
tblInvoices
tblOrderHeader
tblOrderDetail

2. A 1 to 1 relationship exists between tblInvoices & tblOrderHeader, on
field OrderRef

3. A 1 t many relationship exists between tblOrderHeader and tblOrderDetail
on field OrderRef

Problem:
I want to create a query which lists invoice numbers, and the total amount
for the invoices. At the moment, the invoice number may be listed numerous
times, depending on how many items appear in the tblOrderDetail for that
invoice number.

Many thanks

Thomas

[John Winterbottom]

Re: Query Question

"Thomas Morrison" <thomas.morriso n@btbroadband.c om> wrote in message
news:caevpn$i6q $1@hercules.bti nternet.com...[color=blue]
> I'm new to Access, and having tried in vain to solve the following[/color]
problem,[color=blue]
> I would now be grateful for some help.
>
> Situation:
>
> 1. Three tables exist (amoung others)
> tblInvoices
> tblOrderHeader
> tblOrderDetail
>
> 2. A 1 to 1 relationship exists between tblInvoices & tblOrderHeader, on
> field OrderRef
>
> 3. A 1 t many relationship exists between tblOrderHeader and[/color]
tblOrderDetail[color=blue]
> on field OrderRef
>
> Problem:
> I want to create a query which lists invoice numbers, and the total amount
> for the invoices. At the moment, the invoice number may be listed numerous
> times, depending on how many items appear in the tblOrderDetail for that
> invoice number.
>[/color]

maybe something like this:

select i.InvoiceNumber , sum(d.orderAmou nt) as orderTotal
from tblInvoices as i left outer join tblOrderDetail as d on i.OrderRef =
d.OrderRef
group by i.InvoiceNumber
order by i.InvoiceNumber

[Thomas Morrison]

Re: Query Question

John

Many thanks for this note. I'll try out your suggestion.

I'm new to Access (with no programing or coding background), and get the
feeling I'm going to enjoy it.

Regards

Thomas

"John Winterbottom" <assaynet@hotma il.com> wrote in message
news:2j0oc0Fqp1 8mU1@uni-berlin.de...[color=blue]
> "Thomas Morrison" <thomas.morriso n@btbroadband.c om> wrote in message
> news:caevpn$i6q $1@hercules.bti nternet.com...[color=green]
> > I'm new to Access, and having tried in vain to solve the following[/color]
> problem,[color=green]
> > I would now be grateful for some help.
> >
> > Situation:
> >
> > 1. Three tables exist (amoung others)
> > tblInvoices
> > tblOrderHeader
> > tblOrderDetail
> >
> > 2. A 1 to 1 relationship exists between tblInvoices & tblOrderHeader, on
> > field OrderRef
> >
> > 3. A 1 t many relationship exists between tblOrderHeader and[/color]
> tblOrderDetail[color=green]
> > on field OrderRef
> >
> > Problem:
> > I want to create a query which lists invoice numbers, and the total[/color][/color]
amount[color=blue][color=green]
> > for the invoices. At the moment, the invoice number may be listed[/color][/color]
numerous[color=blue][color=green]
> > times, depending on how many items appear in the tblOrderDetail for that
> > invoice number.
> >[/color]
>
> maybe something like this:
>
> select i.InvoiceNumber , sum(d.orderAmou nt) as orderTotal
> from tblInvoices as i left outer join tblOrderDetail as d on i.OrderRef =
> d.OrderRef
> group by i.InvoiceNumber
> order by i.InvoiceNumber
>
>
>
>
>
>
>
>
>
>[/color]