OH! I Give Up

[Guest]

I tried & tried & tried & now am TIRED.

I have a access database consisting of 3 Tables.

1 - Order table has 1 field Job Number which is the primary key.
2 - Booking table which has 4 fields. Booking Id which is the primary key,
Job Number which is the foreign key from Ordes table, Month & Value.
3 - Invoice table which has 4 fields. Invoice Number which is the primary
key, Job Number which is the foreign key from Ordes table, Month & value.

1 order can have many bookings & 1 order can have many invoices.

How do i join them? Order.Job Number to Booking.Job Number & Order.Job
Number to Invoice.Job Number.

When I make a quary to know of all the orders with their booking & invoice,
the result set is absurd which just gives me (i think) a result set of all
possible data.

(e-mail address removed)

 

[Douglas J Steele]

The problem you're having is that there's no connection between the Invoice
and Booking tables, therefore when you join the 3 tables together, you get a
cartesian product for Booking and Invoice.

Unless you can find some way to connect those two tables, I don't think
you'll be able to do a 3 way join. If the relationship between Bookings and
Invoices is many-to-many, can you introduce a 4th table that resolves that
many-to-many?

 

[Allen Browne]

From what you tell us, you have:
- a one-to-many relation between Order and Booking, and
- a one-to-many relation between Order and Invoice.
If so, you will probably interface that with a main form bound to the Order
table, and two subforms bound to the Booking and Invoice table respectively.
(If screen space is a problem you could put these subforms in the pages of a
tab control.)

What we don't know is whether this is the best design possible for your
data. It seems that one order results in several bookings over the months.
Does each *booking* result in a separate invoice, in which case the invoices
should relate to the Booking table and not to the Order?

Are the invoices for the same client specified in the Order table? Or can
one order result in invoices to different clients?

Do you need an invoice to be able to cover multiple orders (e.g. if a client
places multiple orders in one billing period)?

Or are you just having difficulties with trying to combine your 3 tables in
one query? Certainly, if you combine all 3 tables as they are currently set
up, you will end up with duplicated amounts where an order has multiple
bookings and multiple invoices.

BTW, have you actually created the relations between the tables by choosing
Relationships on the Tools menu?

 

[Guest]

Dear All,

1 - There is a connection between the Invoice and Booking tables but I do
not know how to implement it which results in a cartesian product.

2 - I am not sure If the relationship between Booking & Invoice is
many-to-many. 1 booking can have many invoices, many bookings can have 1
invoice & many bookings can have many invoices. If I make a junction table to
resolve the many-to-many, it will be impossible to select job number, than
booking Id no & invoice no to fill the 4rth table.

3 - "Does each *booking* result in a separate invoice, in which case the
invoices
should relate to the Booking table and not to the Order?"

What happens is that we get an order of 50,000. We make a booking of 50,000.
We issue one invoice for 10,000, another invoice for 15,000, another for
20,000 & than for 10,000. So bookings = 50k while invoice is 10+15+20+10 =
55. So we need to make an additional booking of 5k to balance the two. We may
also book & bill i.e, we book for wmount when invoices are raised. We may
also de-book.

4 - "Are the invoices for the same client specified in the Order table? Or
can
one order result in invoices to different clients?"

No, one order can only have one client & this client is specified in the
Order table.

5 - "Do you need an invoice to be able to cover multiple orders (e.g. if a
client
places multiple orders in one billing period)?"

No, one order can have many invoices BUT one invoice can not have many orders.

6 - "BTW, have you actually created the relations between the tables by
choosing
Relationships on the Tools menu?"

Yes i created the relations between the tables. Actualt it gets created
automaticaly when you insert the lookup column. The relation is

Booking table has Job number from Order table as lookup column. A one to
many relationship.

Invoice table has Job number from Order table as lookup column. A one to
many relationship.

Regards

Jawad

 

[Allen Browne]

In your example, an order included a booking for 50k. You need to create
multiple invoices for the *booking*, including being able to calculate the
uninvoiced quantity (e.g. 5k of the booking are still uninvoiced.) To do
that, it seems imperative to know which booking the invoice covers.

That suggests an InvoiceDetail table.
The one invoice can have many line items.
Each line item addresses an item from a booking, so InvoiceDetail has a
BookingID foreign key.

It bothers me that - at least in theory - the bookings covered in the
InvoiceDetail could come from different orders, so I question whether the
Invoice.JobNumber foreign key is valid.

You have a far better grasp of your specific data than I can have from a
quick description, but I hope that is helpful. Your real question was about
the cartesian product. If you have created the relations, Access should
automatically create the joins for you in a query (unless you edited the
relations to specify no join.) If it did not, you can create the join in the
query design window, by dragging the primary key field (Job Number) from
Orders and dropping it onto the foreign key (Job Number in the other table.)
After that you should no longer get the Cartesian Product.

You may need to adjust the query to get outer joins (double-click the join
line in query design.) You may also need to work with just the Orders and
one other table (i.e. Booking or Invoice but not both.)

On a side issue, you may be aware that there are many of us who hate that
lookup wizard in table design:
http://www.mvps.org/access/lookupfields.htm

HTH

 

[Guest]

Dear Allen,

It's about 2 weeks and I have come no closer to solving the problem. Let me
make an attempt to explain the data. What I think, I am doing wrong is my
Data Modeling is hierarchal & not relational.

1 - Lets say we get an order from a Customer of USD. 50,000. So now we have
an Order table as follows.

ORDER TABLE
Customer Id Job Number
ARTC 6SA-5-SA-001

The job number is the primary key and is unique. It is assigned to orders
whenever we get an order from a customer. It can be also called as order
number (which is generally how it is done) just like an invoice number or
purchase order number.

2 - Now we go to the Booking Table, Select the Job Number which is a foreign
key & is a lookup column from the Order table just like the Customer Id can
be a lookup column from a table called Customer in the Order Table. So the
Booking table will look like this.

BOOKING TABLE
Booking Id Job Number Month Value
Bok1 6SA-5-SA-001 Jan 05 50,000

3 - This done; now we raise invoices.

INVOICE TABLE
Invoice No Job Number Date Value
5-5001 6SA-5-SA-001 20 Jan 05 10,000
5-5006 6SA-5-SA-001 15 Feb 05 20,000
5-5010 6SA-5-SA-001 25 Mar 05 10,000

Joins Are:
1 - Orders Table. Job Number Field (Primary Key) to Booking Table. Job
Number Field (Foreign Key)
2 - Orders Table. Job Number Field (Primary Key) to Invoice Table. Job
Number field (Foreign Key)

At this point, Booking = 50,000 while Invoice is 40,000. So backlog is
10,000. Now if we issue another invoice

INVOICE TABLE
Invoice No Job Number Date Value
5-5001 6SA-5-SA-001 20 Jan 05 10,000
5-5006 6SA-5-SA-001 15 Feb 05 20,000
5-5010 6SA-5-SA-001 25 Mar 05 10,000
5-5015 6SA-5-SA-001 30 Dec 05 15,000 (This new invoice
Issued)

We have booking 50,000 & invoice at 55,000. So we have exceeded our booking
by 5,000 for which we need to make another booking of 5,000 to bring the
backlog to zero as

BOOKING TABLE
Booking Id Job Number Month Value
Bok1 6SA-5-SA-001 Jan 05 50,000
Bok2 6SA-5-SA-001 Dec 05 5,000

3 - Now we make a SQL Query As

SELECT Order.JobNumber, Booking.BookingId, Booking.Month, Booking.Value AS
Booking_Value, Invoice.InvoiceNo, Invoice.Date, Invoice.Value AS Invoice_Value
FROM ([Order] INNER JOIN Invoice ON Order.JobNumber = Invoice.JobNumber)
INNER JOIN Booking ON Order.JobNumber = Booking.JobNumber;

Which gives a data/result set of total 8 records as

Job Number BookingID Month Booking_Value Invoice No Invoice Date Invoice
Value
6SA-5-SA-001 Bok1 Dec-05 5000 5-5006 15-Feb-05
20000
6SA-5-SA-001 Bok2 Dec-05 5000 5-5001 20-Jan-05
10000
6SA-5-SA-001 Bok2 Dec-05 5000 5-5015 30-Dec-05
15000
6SA-5-SA-001 Bok2 Dec-05 5000 5-5010 25-Mar-05
10000
6SA-5-SA-001 Bok1 Jan-05 50000 5-5006 15-Feb-05
20000
6SA-5-SA-001 Bok1 Jan-05 50000 5-5001 20-Jan-05
10000
6SA-5-SA-001 Bok1 Jan-05 50000 5-5015 30-Dec-05
15000
6SA-5-SA-001 Bok1 Jan-05 50000 5-5010 25-Mar-05
10000

This data/result set shows total bookings as 220,000 & Invoice at 110,000
which is totally wrong as Booking & Invoice is at 55,000 each. It just gives
every possible way (a cross or Cartesian product) where the Bok1 & Bok2 is
repeated many times with the values as well as the invoice numbers are
repeated many times with their values.

There is no question as to the relationship as the relationship exists. I
have made sure. Surely there is a relationship between booking table &
invoice table which is many to many. If I make a junction table, it is near
impossible and a manual task to pick the job number first, than the booking
Id & then the Invoice No (all 3 foreign keys) as shown

JobNumber BookingId InvoiceId
6SA-5-SA-001 Bok1 5-5001 Bok1 value 50k & Inv
value 10k
6SA-5-SA-001 Bok1 5-5006 Invoice Value 20k
6SA-5-SA-001 Bok1 5-5010 Invoice Value 10k
6SA-5-SA-001 Bok1 & Bok2 5-5015 Bok2 value 5k & inv value 15k

Note tha lst record which relates to Bok1 & Bok2.

What is required is

CustomerId JobNumber Month BokValue InvNo InvDate InvValue
ARTC 6SA-5-SA-001 Jan-05 50000 5-5001 20-Jan-05 10000
Dec-05 5000 5-5006 15-Feb-05
15000
5-5010
25-Mar-05 10000
5-5015
30-Dec-05 15000

Now this is the data/result set which makes sense. Once it is taken to the
Pivot table, it will return reports with logical data but it also has some
shortages. The CustomerId needs to be repeated if I want to know total
bookings by this customer & total invoices by this customer, which the above
will not give.

I tried a SQL specific query (UNION) to join the booking & Invoice table but
the nos. of fields as well as the filed names needs to be same. So that also
does not work. I tried to connect the job number from the booking table to
the Invoice table rather than the job number from the order table to the
invoice table, but that also results in the same data.

About lookup columns. If I don’t add lookup column in the order table for
customer, how can I select a customer in the order table.

It’s long & time consuming BUT after 2 weeks I am desperate.

Regards

(e-mail address removed)
======================

 

[Allen Browne]

Okay, I don't pretend to understand all of that (particularly where extra
bookings are created beyond the original order), but I think the key issue
is the way the values are repeating when there are multiple bookings and
invoices, so I will concentrate on that aspect. Let me know if that misses
the mark with what you are wanting to achieve.

There are several ways to avoid the inflated totals. They all involve not
having both the related related tables in the query at the same time.

Solution 1: Subquery
This solution uses a join between Order and Booking to get the total booking
for the order. It then uses a subquery to get the total invoices for the
query.
Advantage: Shows the correct totals in one go.
Disadvantage: Lists the totals only, not all the details.

SELECT Order.[Job Number],
Order.[Customer ID],
Sum(Booking.Value) AS BookingValue,
(SELECT Sum(Invoice.Value) AS SumOfValue
FROM Invoice
WHERE Invoice.[Job Number] = Order.[Job Number])
AS InvoiceValue
FROM Order INNER JOIN Booking
ON Order.[Job Number] = Booking.[Job Number]
GROUP BY Order.[Job Number],
Order.[Customer ID];

Solution 2: Subreport
This solution uses a query into Order and Booking, as the source for a
report. The report has a group footer for [Job Number], and in that section
you include a subreport that shows the Invoice amounts for the Job Number.
Advantage: Lists each line of the Booking and each line of the Invoice, but
still gives the right totals.
Disadvantage: Does not do the entire job in a query (if that's important.)

Solution 3: Stacked queries
You could create a query into Booking that will GROUP BY Job Number and sum
the value. Then another query into Invoice that will GROUP BY Job Number and
sum the value. Then use both these as source "tables" along with the
original Order table.

Please post back if that's not what you need to achieve.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Dear Allen,

It's about 2 weeks and I have come no closer to solving the problem. Let
me
make an attempt to explain the data. What I think, I am doing wrong is my
Data Modeling is hierarchal & not relational.

1 - Lets say we get an order from a Customer of USD. 50,000. So now we
have
an Order table as follows.

ORDER TABLE
Customer Id Job Number
ARTC 6SA-5-SA-001

The job number is the primary key and is unique. It is assigned to orders
whenever we get an order from a customer. It can be also called as order
number (which is generally how it is done) just like an invoice number or
purchase order number.

2 - Now we go to the Booking Table, Select the Job Number which is a
foreign
key & is a lookup column from the Order table just like the Customer Id
can
be a lookup column from a table called Customer in the Order Table. So the
Booking table will look like this.

BOOKING TABLE
Booking Id Job Number Month Value
Bok1 6SA-5-SA-001 Jan 05 50,000

3 - This done; now we raise invoices.

INVOICE TABLE
Invoice No Job Number Date Value
5-5001 6SA-5-SA-001 20 Jan 05 10,000
5-5006 6SA-5-SA-001 15 Feb 05 20,000
5-5010 6SA-5-SA-001 25 Mar 05 10,000

Joins Are:
1 - Orders Table. Job Number Field (Primary Key) to Booking Table. Job
Number Field (Foreign Key)
2 - Orders Table. Job Number Field (Primary Key) to Invoice Table. Job
Number field (Foreign Key)

At this point, Booking = 50,000 while Invoice is 40,000. So backlog is
10,000. Now if we issue another invoice

INVOICE TABLE
Invoice No Job Number Date Value
5-5001 6SA-5-SA-001 20 Jan 05 10,000
5-5006 6SA-5-SA-001 15 Feb 05 20,000
5-5010 6SA-5-SA-001 25 Mar 05 10,000
5-5015 6SA-5-SA-001 30 Dec 05 15,000 (This new invoice
Issued)

We have booking 50,000 & invoice at 55,000. So we have exceeded our
booking
by 5,000 for which we need to make another booking of 5,000 to bring the
backlog to zero as

BOOKING TABLE
Booking Id Job Number Month Value
Bok1 6SA-5-SA-001 Jan 05 50,000
Bok2 6SA-5-SA-001 Dec 05 5,000

3 - Now we make a SQL Query As

SELECT Order.JobNumber, Booking.BookingId, Booking.Month, Booking.Value AS
Booking_Value, Invoice.InvoiceNo, Invoice.Date, Invoice.Value AS
Invoice_Value
FROM ([Order] INNER JOIN Invoice ON Order.JobNumber = Invoice.JobNumber)
INNER JOIN Booking ON Order.JobNumber = Booking.JobNumber;

Which gives a data/result set of total 8 records as

Job Number BookingID Month Booking_Value Invoice No Invoice Date
Invoice
Value
6SA-5-SA-001 Bok1 Dec-05 5000 5-5006 15-Feb-05
20000
6SA-5-SA-001 Bok2 Dec-05 5000 5-5001 20-Jan-05
10000
6SA-5-SA-001 Bok2 Dec-05 5000 5-5015 30-Dec-05
15000
6SA-5-SA-001 Bok2 Dec-05 5000 5-5010 25-Mar-05
10000
6SA-5-SA-001 Bok1 Jan-05 50000 5-5006 15-Feb-05
20000
6SA-5-SA-001 Bok1 Jan-05 50000 5-5001 20-Jan-05
10000
6SA-5-SA-001 Bok1 Jan-05 50000 5-5015 30-Dec-05
15000
6SA-5-SA-001 Bok1 Jan-05 50000 5-5010 25-Mar-05
10000

This data/result set shows total bookings as 220,000 & Invoice at 110,000
which is totally wrong as Booking & Invoice is at 55,000 each. It just
gives
every possible way (a cross or Cartesian product) where the Bok1 & Bok2 is
repeated many times with the values as well as the invoice numbers are
repeated many times with their values.

There is no question as to the relationship as the relationship exists. I
have made sure. Surely there is a relationship between booking table &
invoice table which is many to many. If I make a junction table, it is
near
impossible and a manual task to pick the job number first, than the
booking
Id & then the Invoice No (all 3 foreign keys) as shown

JobNumber BookingId InvoiceId
6SA-5-SA-001 Bok1 5-5001 Bok1 value 50k & Inv
value 10k
6SA-5-SA-001 Bok1 5-5006 Invoice Value 20k
6SA-5-SA-001 Bok1 5-5010 Invoice Value 10k
6SA-5-SA-001 Bok1 & Bok2 5-5015 Bok2 value 5k & inv value
15k

Note tha lst record which relates to Bok1 & Bok2.

What is required is

CustomerId JobNumber Month BokValue InvNo InvDate InvValue
ARTC 6SA-5-SA-001 Jan-05 50000 5-5001 20-Jan-05 10000
Dec-05 5000 5-5006 15-Feb-05
15000
5-5010
25-Mar-05 10000
5-5015
30-Dec-05 15000

Now this is the data/result set which makes sense. Once it is taken to the
Pivot table, it will return reports with logical data but it also has some
shortages. The CustomerId needs to be repeated if I want to know total
bookings by this customer & total invoices by this customer, which the
above
will not give.

I tried a SQL specific query (UNION) to join the booking & Invoice table
but
the nos. of fields as well as the filed names needs to be same. So that
also
does not work. I tried to connect the job number from the booking table to
the Invoice table rather than the job number from the order table to the
invoice table, but that also results in the same data.

About lookup columns. If I don't add lookup column in the order table for
customer, how can I select a customer in the order table.

It's long & time consuming BUT after 2 weeks I am desperate.

Regards

(e-mail address removed)
======================

In your example, an order included a booking for 50k. You need to create
multiple invoices for the *booking*, including being able to calculate
the
uninvoiced quantity (e.g. 5k of the booking are still uninvoiced.) To do
that, it seems imperative to know which booking the invoice covers.

That suggests an InvoiceDetail table.
The one invoice can have many line items.
Each line item addresses an item from a booking, so InvoiceDetail has a
BookingID foreign key.

It bothers me that - at least in theory - the bookings covered in the
InvoiceDetail could come from different orders, so I question whether the
Invoice.JobNumber foreign key is valid.

You have a far better grasp of your specific data than I can have from a
quick description, but I hope that is helpful. Your real question was
about
the cartesian product. If you have created the relations, Access should
automatically create the joins for you in a query (unless you edited the
relations to specify no join.) If it did not, you can create the join in
the
query design window, by dragging the primary key field (Job Number) from
Orders and dropping it onto the foreign key (Job Number in the other
table.)
After that you should no longer get the Cartesian Product.

You may need to adjust the query to get outer joins (double-click the
join
line in query design.) You may also need to work with just the Orders and
one other table (i.e. Booking or Invoice but not both.)

On a side issue, you may be aware that there are many of us who hate that
lookup wizard in table design:
http://www.mvps.org/access/lookupfields.htm

HTH

 

[Guest]

Dear Allen,

Sales = Orders = Bookings
Revenue = Invoices

A company named IME has two values by which it operates.

First
Q - IME, What are your total sales for the financial year 2005?
A - It is 50,000 (in this case where we only have one order from one customer)

Second
Q - IME, What are your total revenue for the financial year 2005?
A - It is 55,000.

Q - How can you have total sales at 50,000 & revenue 55,000? How can you
have invoiced an extra 5,000 for which you have no order.

To answer this, the Customer sends or we demand an extra Order for 5,000 or
revised Order of 55,000. Thus make additional booking & OrderS will be =
Revenue

What is required of the query is to list all orders with most of their
details such as Customer, End User, Site, Description, Country, Division,
Category, Department AND Also how much value was booked in which months +
Invoice No. Invoice Date, Invoice Value and than a calculated field which
subtrcats each orders's bookings from each order's invoices & shows which
jobs have how much backlog.

If i create a table in a list type format

CustomerId JobNumber Type Date No Value
ARTC 6SA-5-SA-001 Order 01-Jan-05 Nill 50,000
ARTC 6SA-5-SA-001 Order 31-Dec-05 Nill 5,000
ARTC 6SA-5-SA-001 Invoice 20-Jan-05 5-5001 10,000
ARTC 6SA-5-SA-001 Invoice 25-Feb-05 5-5006 20,000
ARTC 6SA-5-SA-001 Invoice 25-Mar-05 5-5010 10,000
ARTC 6SA-5-SA-001 Invoice 30-Dec-05 5-5015 15,000

Now I can have a piviotable as

CustomerId Job No Month
Jan-05 Feb-05 Mar-05 Dec-05

Ord/Inv Ord/Inv Ord/Inv Ord/Inv

ARTC 6SA-5-SA-001 50/10 0/20 0/10 5/15

"They all involve not having both the related related tables in the query at
the same time"

For this kind of Query I need & must have all related tables Else how can I
make a Backlog report.

Regards

Jawad

===============

Okay, I don't pretend to understand all of that (particularly where extra
bookings are created beyond the original order), but I think the key issue
is the way the values are repeating when there are multiple bookings and
invoices, so I will concentrate on that aspect. Let me know if that misses
the mark with what you are wanting to achieve.

There are several ways to avoid the inflated totals. They all involve not
having both the related related tables in the query at the same time.

Solution 1: Subquery
This solution uses a join between Order and Booking to get the total booking
for the order. It then uses a subquery to get the total invoices for the
query.
Advantage: Shows the correct totals in one go.
Disadvantage: Lists the totals only, not all the details.

SELECT Order.[Job Number],
Order.[Customer ID],
Sum(Booking.Value) AS BookingValue,
(SELECT Sum(Invoice.Value) AS SumOfValue
FROM Invoice
WHERE Invoice.[Job Number] = Order.[Job Number])
AS InvoiceValue
FROM Order INNER JOIN Booking
ON Order.[Job Number] = Booking.[Job Number]
GROUP BY Order.[Job Number],
Order.[Customer ID];

Solution 2: Subreport
This solution uses a query into Order and Booking, as the source for a
report. The report has a group footer for [Job Number], and in that section
you include a subreport that shows the Invoice amounts for the Job Number.
Advantage: Lists each line of the Booking and each line of the Invoice, but
still gives the right totals.
Disadvantage: Does not do the entire job in a query (if that's important.)

Solution 3: Stacked queries
You could create a query into Booking that will GROUP BY Job Number and sum
the value. Then another query into Invoice that will GROUP BY Job Number and
sum the value. Then use both these as source "tables" along with the
original Order table.

Please post back if that's not what you need to achieve.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Dear Allen,

It's about 2 weeks and I have come no closer to solving the problem. Let
me
make an attempt to explain the data. What I think, I am doing wrong is my
Data Modeling is hierarchal & not relational.

1 - Lets say we get an order from a Customer of USD. 50,000. So now we
have
an Order table as follows.

ORDER TABLE
Customer Id Job Number
ARTC 6SA-5-SA-001

The job number is the primary key and is unique. It is assigned to orders
whenever we get an order from a customer. It can be also called as order
number (which is generally how it is done) just like an invoice number or
purchase order number.

2 - Now we go to the Booking Table, Select the Job Number which is a
foreign
key & is a lookup column from the Order table just like the Customer Id
can
be a lookup column from a table called Customer in the Order Table. So the
Booking table will look like this.

BOOKING TABLE
Booking Id Job Number Month Value
Bok1 6SA-5-SA-001 Jan 05 50,000

3 - This done; now we raise invoices.

INVOICE TABLE
Invoice No Job Number Date Value
5-5001 6SA-5-SA-001 20 Jan 05 10,000
5-5006 6SA-5-SA-001 15 Feb 05 20,000
5-5010 6SA-5-SA-001 25 Mar 05 10,000

Joins Are:
1 - Orders Table. Job Number Field (Primary Key) to Booking Table. Job
Number Field (Foreign Key)
2 - Orders Table. Job Number Field (Primary Key) to Invoice Table. Job
Number field (Foreign Key)

At this point, Booking = 50,000 while Invoice is 40,000. So backlog is
10,000. Now if we issue another invoice

INVOICE TABLE
Invoice No Job Number Date Value
5-5001 6SA-5-SA-001 20 Jan 05 10,000
5-5006 6SA-5-SA-001 15 Feb 05 20,000
5-5010 6SA-5-SA-001 25 Mar 05 10,000
5-5015 6SA-5-SA-001 30 Dec 05 15,000 (This new invoice
Issued)

We have booking 50,000 & invoice at 55,000. So we have exceeded our
booking
by 5,000 for which we need to make another booking of 5,000 to bring the
backlog to zero as

BOOKING TABLE
Booking Id Job Number Month Value
Bok1 6SA-5-SA-001 Jan 05 50,000
Bok2 6SA-5-SA-001 Dec 05 5,000

3 - Now we make a SQL Query As

SELECT Order.JobNumber, Booking.BookingId, Booking.Month, Booking.Value AS
Booking_Value, Invoice.InvoiceNo, Invoice.Date, Invoice.Value AS
Invoice_Value
FROM ([Order] INNER JOIN Invoice ON Order.JobNumber = Invoice.JobNumber)
INNER JOIN Booking ON Order.JobNumber = Booking.JobNumber;

Which gives a data/result set of total 8 records as

Job Number BookingID Month Booking_Value Invoice No Invoice Date
Invoice
Value
6SA-5-SA-001 Bok1 Dec-05 5000 5-5006 15-Feb-05
20000
6SA-5-SA-001 Bok2 Dec-05 5000 5-5001 20-Jan-05
10000
6SA-5-SA-001 Bok2 Dec-05 5000 5-5015 30-Dec-05
15000
6SA-5-SA-001 Bok2 Dec-05 5000 5-5010 25-Mar-05
10000
6SA-5-SA-001 Bok1 Jan-05 50000 5-5006 15-Feb-05
20000
6SA-5-SA-001 Bok1 Jan-05 50000 5-5001 20-Jan-05
10000
6SA-5-SA-001 Bok1 Jan-05 50000 5-5015 30-Dec-05
15000
6SA-5-SA-001 Bok1 Jan-05 50000 5-5010 25-Mar-05
10000

This data/result set shows total bookings as 220,000 & Invoice at 110,000
which is totally wrong as Booking & Invoice is at 55,000 each. It just
gives
every possible way (a cross or Cartesian product) where the Bok1 & Bok2 is
repeated many times with the values as well as the invoice numbers are
repeated many times with their values.

There is no question as to the relationship as the relationship exists. I
have made sure. Surely there is a relationship between booking table &
invoice table which is many to many. If I make a junction table, it is
near
impossible and a manual task to pick the job number first, than the
booking
Id & then the Invoice No (all 3 foreign keys) as shown

JobNumber BookingId InvoiceId
6SA-5-SA-001 Bok1 5-5001 Bok1 value 50k & Inv
value 10k
6SA-5-SA-001 Bok1 5-5006 Invoice Value 20k
6SA-5-SA-001 Bok1 5-5010 Invoice Value 10k
6SA-5-SA-001 Bok1 & Bok2 5-5015 Bok2 value 5k & inv value
15k

Note tha lst record which relates to Bok1 & Bok2.

What is required is

CustomerId JobNumber Month BokValue InvNo InvDate InvValue
ARTC 6SA-5-SA-001 Jan-05 50000 5-5001 20-Jan-05 10000
Dec-05 5000 5-5006 15-Feb-05
15000
5-5010
25-Mar-05 10000
5-5015
30-Dec-05 15000

Now this is the data/result set which makes sense. Once it is taken to the
Pivot table, it will return reports with logical data but it also has some
shortages. The CustomerId needs to be repeated if I want to know total
bookings by this customer & total invoices by this customer, which the
above
will not give.

I tried a SQL specific query (UNION) to join the booking & Invoice table
but
the nos. of fields as well as the filed names needs to be same. So that
also
does not work. I tried to connect the job number from the booking table to
the Invoice table rather than the job number from the order table to the
invoice table, but that also results in the same data.

About lookup columns. If I don't add lookup column in the order table for
customer, how can I select a customer in the order table.

It's long & time consuming BUT after 2 weeks I am desperate.

Regards

(e-mail address removed)
======================

In your example, an order included a booking for 50k. You need to create
multiple invoices for the *booking*, including being able to calculate
the
uninvoiced quantity (e.g. 5k of the booking are still uninvoiced.) To do
that, it seems imperative to know which booking the invoice covers.

That suggests an InvoiceDetail table.
The one invoice can have many line items.
Each line item addresses an item from a booking, so InvoiceDetail has a
BookingID foreign key.

It bothers me that - at least in theory - the bookings covered in the
InvoiceDetail could come from different orders, so I question whether the
Invoice.JobNumber foreign key is valid.

You have a far better grasp of your specific data than I can have from a
quick description, but I hope that is helpful. Your real question was
about
the cartesian product. If you have created the relations, Access should
automatically create the joins for you in a query (unless you edited the
relations to specify no join.) If it did not, you can create the join in
the
query design window, by dragging the primary key field (Job Number) from
Orders and dropping it onto the foreign key (Job Number in the other
table.)
After that you should no longer get the Cartesian Product.

You may need to adjust the query to get outer joins (double-click the
join
line in query design.) You may also need to work with just the Orders and
one other table (i.e. Booking or Invoice but not both.)

On a side issue, you may be aware that there are many of us who hate that
lookup wizard in table design:
http://www.mvps.org/access/lookupfields.htm

HTH


1 - There is a connection between the Invoice and Booking tables but I
do
not know how to implement it which results in a cartesian product.

2 - I am not sure If the relationship between Booking & Invoice is
many-to-many. 1 booking can have many invoices, many bookings can have
1
invoice & many bookings can have many invoices. If I make a junction
table
to
resolve the many-to-many, it will be impossible to select job number,
than
booking Id no & invoice no to fill the 4rth table.

3 - "Does each *booking* result in a separate invoice, in which case
the
invoices
should relate to the Booking table and not to the Order?"

What happens is that we get an order of 50,000. We make a booking of
50,000.
We issue one invoice for 10,000, another invoice for 15,000, another
for
20,000 & than for 10,000. So bookings = 50k while invoice is
10+15+20+10 =
55. So we need to make an additional booking of 5k to balance the two.
We
may
also book & bill i.e, we book for wmount when invoices are raised. We
may
also de-book.

4 - "Are the invoices for the same client specified in the Order table?
Or
can
one order result in invoices to different clients?"

No, one order can only have one client & this client is specified in
the
Order table.

5 - "Do you need an invoice to be able to cover multiple orders (e.g.
if a
client
places multiple orders in one billing period)?"

No, one order can have many invoices BUT one invoice can not have many
orders.

6 - "BTW, have you actually created the relations between the tables by

 

[Allen Browne]

You can use a UNION query to combine the data in the same columns (by
aliasing the fields), or in different columns (by specifying Nulls for the
unused columns.

Same columns:
===========
SELECT Booking.JobNumber,
Booking.BookingID AS ID,
"Booking" AS EntryType,
Booking.JobMonth AS EntryDate,
Booking.BookingValue AS EntryValue,
Booking.BookingValue AS Net
FROM Booking
UNION ALL
SELECT Invoice.JobNumber,
Invoice.InvoiceNo AS ID,
"Invoice" AS EntryType,
Invoice.InvoiceDate AS EntryDate,
Invoice.InvoiceValue AS EntryValue,
-[InvoiceValue] AS Net
FROM Invoice;

Different columns:
=============
SELECT Booking.JobNumber,
Booking.JobMonth,
Booking.BookingValue,
Null AS InvoiceNo,
IIf(True,Null,#1/1/1900#) AS InvoiceDate,
IIf(True,Null,0) AS InvoiceValue
FROM Booking
UNION ALL
SELECT Invoice.JobNumber,
IIf(True,Null,#1/1/1900#) AS JobMonth,
IIf(True,Null,0) AS BookingValue,
Invoice.InvoiceNo,
Invoice.InvoiceDate,
Invoice.InvoiceValue
FROM Invoice;

The first one assumes that JobMonth is actually a date/time field (which
will be essential if you plan to merge the data by date). It also includes a
literal field to indicate whether the entry is a booking or invoice, and
illustrates how to get a net balance figure (last column.)

The second one puts the data into different columns by filling the remaining
columns with Null. If a column is all null in the first SELECT, JET is
unable to determine the data type, and so assumes text. We work around that
with the IIf() construct, which always return Null (because True is always
True), but the alternative gives JET the intended data type.

As you know, the real problem with the data is that there is no relationship
between bookings and invoices. For any given JobNumber, it is therefore
meaningful only to talk about the sum of bookings compared to the sum of
invoices.

Hopefully the UNION queries will get you out of trouble.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Dear Allen,

It's about 2 weeks and I have come no closer to solving the problem. Let
me
make an attempt to explain the data. What I think, I am doing wrong is my
Data Modeling is hierarchal & not relational.

1 - Lets say we get an order from a Customer of USD. 50,000. So now we
have
an Order table as follows.

ORDER TABLE
Customer Id Job Number
ARTC 6SA-5-SA-001

The job number is the primary key and is unique. It is assigned to orders
whenever we get an order from a customer. It can be also called as order
number (which is generally how it is done) just like an invoice number or
purchase order number.

2 - Now we go to the Booking Table, Select the Job Number which is a
foreign
key & is a lookup column from the Order table just like the Customer Id
can
be a lookup column from a table called Customer in the Order Table. So the
Booking table will look like this.

BOOKING TABLE
Booking Id Job Number Month Value
Bok1 6SA-5-SA-001 Jan 05 50,000

3 - This done; now we raise invoices.

INVOICE TABLE
Invoice No Job Number Date Value
5-5001 6SA-5-SA-001 20 Jan 05 10,000
5-5006 6SA-5-SA-001 15 Feb 05 20,000
5-5010 6SA-5-SA-001 25 Mar 05 10,000

Joins Are:
1 - Orders Table. Job Number Field (Primary Key) to Booking Table. Job
Number Field (Foreign Key)
2 - Orders Table. Job Number Field (Primary Key) to Invoice Table. Job
Number field (Foreign Key)

At this point, Booking = 50,000 while Invoice is 40,000. So backlog is
10,000. Now if we issue another invoice

INVOICE TABLE
Invoice No Job Number Date Value
5-5001 6SA-5-SA-001 20 Jan 05 10,000
5-5006 6SA-5-SA-001 15 Feb 05 20,000
5-5010 6SA-5-SA-001 25 Mar 05 10,000
5-5015 6SA-5-SA-001 30 Dec 05 15,000 (This new invoice
Issued)

We have booking 50,000 & invoice at 55,000. So we have exceeded our
booking
by 5,000 for which we need to make another booking of 5,000 to bring the
backlog to zero as

BOOKING TABLE
Booking Id Job Number Month Value
Bok1 6SA-5-SA-001 Jan 05 50,000
Bok2 6SA-5-SA-001 Dec 05 5,000

3 - Now we make a SQL Query As

SELECT Order.JobNumber, Booking.BookingId, Booking.Month, Booking.Value AS
Booking_Value, Invoice.InvoiceNo, Invoice.Date, Invoice.Value AS
Invoice_Value
FROM ([Order] INNER JOIN Invoice ON Order.JobNumber = Invoice.JobNumber)
INNER JOIN Booking ON Order.JobNumber = Booking.JobNumber;

Which gives a data/result set of total 8 records as

Job Number BookingID Month Booking_Value Invoice No Invoice Date
Invoice
Value
6SA-5-SA-001 Bok1 Dec-05 5000 5-5006 15-Feb-05
20000
6SA-5-SA-001 Bok2 Dec-05 5000 5-5001 20-Jan-05
10000
6SA-5-SA-001 Bok2 Dec-05 5000 5-5015 30-Dec-05
15000
6SA-5-SA-001 Bok2 Dec-05 5000 5-5010 25-Mar-05
10000
6SA-5-SA-001 Bok1 Jan-05 50000 5-5006 15-Feb-05
20000
6SA-5-SA-001 Bok1 Jan-05 50000 5-5001 20-Jan-05
10000
6SA-5-SA-001 Bok1 Jan-05 50000 5-5015 30-Dec-05
15000
6SA-5-SA-001 Bok1 Jan-05 50000 5-5010 25-Mar-05
10000

This data/result set shows total bookings as 220,000 & Invoice at 110,000
which is totally wrong as Booking & Invoice is at 55,000 each. It just
gives
every possible way (a cross or Cartesian product) where the Bok1 & Bok2 is
repeated many times with the values as well as the invoice numbers are
repeated many times with their values.

There is no question as to the relationship as the relationship exists. I
have made sure. Surely there is a relationship between booking table &
invoice table which is many to many. If I make a junction table, it is
near
impossible and a manual task to pick the job number first, than the
booking
Id & then the Invoice No (all 3 foreign keys) as shown

JobNumber BookingId InvoiceId
6SA-5-SA-001 Bok1 5-5001 Bok1 value 50k & Inv
value 10k
6SA-5-SA-001 Bok1 5-5006 Invoice Value 20k
6SA-5-SA-001 Bok1 5-5010 Invoice Value 10k
6SA-5-SA-001 Bok1 & Bok2 5-5015 Bok2 value 5k & inv value
15k

Note tha lst record which relates to Bok1 & Bok2.

What is required is

CustomerId JobNumber Month BokValue InvNo InvDate InvValue
ARTC 6SA-5-SA-001 Jan-05 50000 5-5001 20-Jan-05 10000
Dec-05 5000 5-5006 15-Feb-05
15000
5-5010
25-Mar-05 10000
5-5015
30-Dec-05 15000

Now this is the data/result set which makes sense. Once it is taken to the
Pivot table, it will return reports with logical data but it also has some
shortages. The CustomerId needs to be repeated if I want to know total
bookings by this customer & total invoices by this customer, which the
above
will not give.

I tried a SQL specific query (UNION) to join the booking & Invoice table
but
the nos. of fields as well as the filed names needs to be same. So that
also
does not work. I tried to connect the job number from the booking table to
the Invoice table rather than the job number from the order table to the
invoice table, but that also results in the same data.

About lookup columns. If I don't add lookup column in the order table for
customer, how can I select a customer in the order table.

It's long & time consuming BUT after 2 weeks I am desperate.

Regards

(e-mail address removed)
======================

In your example, an order included a booking for 50k. You need to create
multiple invoices for the *booking*, including being able to calculate
the
uninvoiced quantity (e.g. 5k of the booking are still uninvoiced.) To do
that, it seems imperative to know which booking the invoice covers.

That suggests an InvoiceDetail table.
The one invoice can have many line items.
Each line item addresses an item from a booking, so InvoiceDetail has a
BookingID foreign key.

It bothers me that - at least in theory - the bookings covered in the
InvoiceDetail could come from different orders, so I question whether the
Invoice.JobNumber foreign key is valid.

You have a far better grasp of your specific data than I can have from a
quick description, but I hope that is helpful. Your real question was
about
the cartesian product. If you have created the relations, Access should
automatically create the joins for you in a query (unless you edited the
relations to specify no join.) If it did not, you can create the join in
the
query design window, by dragging the primary key field (Job Number) from
Orders and dropping it onto the foreign key (Job Number in the other
table.)
After that you should no longer get the Cartesian Product.

You may need to adjust the query to get outer joins (double-click the
join
line in query design.) You may also need to work with just the Orders and
one other table (i.e. Booking or Invoice but not both.)

On a side issue, you may be aware that there are many of us who hate that
lookup wizard in table design:
http://www.mvps.org/access/lookupfields.htm

HTH