Many to Many Relationship

[Crystal K]

Please Help! (Working in Access 2000 file format)

Scenario: The store gets invoices. The main office processes invoices. I
want to find out three things-

1) the invoices the main office processed that the store received.
2) the invoices the store got that the main office didn't process.
3) the invoices the main office processed that the store didn't receive.

I think I have a many to many relationship and understands that I need a
junction table in Access. Basically I have three tables. This is the setup:

Table Name: Store
Column Name: StoreID (autonumber, pk)
InvoiceNumber (text)
InvoiceDate

Table Name: MainOffice
Column Name: MainOfficeID (autonumber, pk)
InvoiceNumPro (text)
InvoiceDatePro

TableName: StoreAndMainOffice
Column Name: StoreID (number, pk)
MainOfficeID (number, pk)

I created a one to many relationship from Store to StoreAndMainOffice, and
from MainOffice to StoreAndMainOffice. Thus hoping to have created a correct
junction table setup. Let me know if this is incorrect.

I have data for the Store table and the MainOffice table that I want to
import in (basically from an excel document). And the information is already
imported. How do I get Access to populate the StoreAndMainOffice table now
that the Store table and Main Office Table is populated? And what are the
queries that I need to get the results to the three things that I want from
this database, or technically item 2 and 3, since item 1 is answered by the
junction table. Feel free to past the SQL of the query so that I can copy and
paste it to my database.

Thanks in advance for any assistance!

 

[Steve Schapel]

Crystal,

Maybe someone will understand the scenario you have described, but I'm
afraid I don't. Are you dealing with different organisations, or are
all these Stores and MainOffices within the same organisation? Where do
the Invoices come from? I assume you mean that when a Store receives an
invoice, it can forward it to a number of MainOffices for processing,
and equally, any given MainOffice can receive invoices from a number of
Stores? Whatever the case, you wouldn't put the InvoiceNumber in the
Stores table... there must be a one-to-many relationship between Stores
and Invoices, so there needs to be a separate table for the Invoices,
and presumably a StoreID field in the Invoices table in order to record
which Store any given Invoice relates to. Anyway, I'll stop guessing...
can you please give some more details about the data you are trying to
manage?

 

[Crystal K]

Hi Steve,

Thanks for attempting to understand the scenario. Let me try again. There is
only one store and one main office. The store gets invoices and later
forwards them to the main office to process. The main office process invoices
received from the store or through the mail from vendors directly. So I want
to know the three things from my earlier post.

I wish I can draw circles. One circle would contain data on the invoices the
store received. A second circle contain data on the invoices the main office
processed. When you push the two circles slightly together (the store circle
on the left and the main office circle on the right), the middle will
intersect. The middle intersecting part are the invoices that the store
forwarded to the main office to process. The left non-intersecting part are
the invoices that the store forwarded to the main office that didn't get
processed. The right non-intersecting part are the invoices that the main
office processed received through the mail (not forwarded by the store).

I want to know the invoices the two tables have in common and those that are
not common to each other. I hope the scenario is a little clearer....

 

[Steve Schapel]

Crystal,

I enjoyed the story of the circles. This conveys your meaning well. In
fact, it conveys it much better than your table-based explanation. This
is because your table design does not adequately reflect the nature of
the information you are trying to manage... if you don't mind me saying
so. I would really suggest taking a deep breath here, and start again.
There is no way this can be interpreted within the concept of a
many-to-many relationship... it just doesn't fit that model. And the
idea of using different tables to store different information about the
invoices is really asking for trouble. You have Invoices. Whether any
given invoice was received in the office via the store or through the
mail from vendors, this is information *about* the invoice, and as such
needs a *field* in the Invoices table to describe it. Similarly,
whether any given invoice from the store has or hasn't been processed
through the office, this is information *about* the invoice, and as such
needs a *field* in the Invoices table to describe it. If you try to
descibe this information according to which table it's in, by having
different tables for different characteristics of the same data, this is
called the "tables as data" trap. I still don't really know enough
about you business procedures to get too specific. But I would say all
the information about the invoices goes in one table. And imagine you
would have one field which might be called ReceivedBy where the data
will be entered either Store or Office, and another field called
DateProcessed so if this field is empty you will know that the Office
has not processed it yet. Etc. And then any queries to retrieve the
kinds of data summaries that your initial question was about, will be
very simple.

 

[nhornsby]

As MSAccess is a relational database with an acceptable SQL as well a
VBA the relationship manager it is unneccessary and wise to not use th
relationship manager. I agree with Steve, all data should kept in th
'Invoice' table. Use a query to replicate the relationship manager o
use SQL.
Can I assume that the difference offices traversed by the invoice
handles them for different purposes. ie the first office logs the
(accounts payable) and the other office actually processes the funds
(Good seperation of duties)

 

[Crystal K]

Okay- I can start from scratch.

I have two lists. Let's call them List A and List B. List A and List B each
has two columns, named InvoiceNumber, Invoice Date. All I want to know is
what data appears on both lists, what data appears on list A but not on list
B, and what data appears on list B but not on list A. Should I ust Excel or
can Access easily give me the information I'm looking for?

 

[Steve Schapel]

Crystal,

Access can easily give you the information you are looking for... as
long as you set it up as a database. When I said about starting again,
I did not mean starting again to explain the problem. I meant starting
again to design your database. What I meant is you *should not* have
two lists. You should have one list, with an additional column(s) to
identify the type/stage information about the invoices. That was the
whole point of my previous post... I am sorry my meaning was not clear.
A database should identify information according to the data, not
according to which list it's in. In you case, I suggest the "list"
would have 4 columns (obviously over-simplified)... InvoiceNumber,
Invoice Date, ReceivedBy, DateProcessed. With this, you will be able to
use a Query to extract, count, etc according to:
- the invoices received by the office by mail
- the invoices received by the store but not processed by the office
- the invoices received by the store and processed by the office

 

[Crystal K]

Steve-

Thank you. I understand what you are saying. I guess I was hoping there was
a different way to accomplish what I need.

Thanks again.

-Crystal

 

[ArthurJ]

Crystal,

I think I can understand some of the challenges that lead to your business
questions:

Both the store and main office get asked by vendors about the status of
their payment. So both entities would like to know if the other one has
information. They also are leery of paying the same invoice twice.

As others have said, I think all you need are two tables: Vendor and
Invoice, with a one to many join. Your invoice table could include fields
such as: StoreDateRcvd, MainDateRcvd, and DatePaid. If StoreDateRcvd is blank
and MainDateRcvd is not, you know the invoice must have gone directly to the
main office. And if there is no DatePaid, it still is in process. And there
are other queries that would answer your three questions.

Your single database would contain validation and integrity rules such as
preventing duplicate invoice numbers.

Are the main office and the store able to share the same database? I suspect
they are NOT, by the way you talk about "two lists". However that is a
different sort of problem that should not affect the database design. Seems
to me the database should reside at the main office (since they control and
payments and presumably the accounting). The store should keep a list of
invoices received
(Vendor, InvNbr, DateRcvd, DateSentMain). This could be transmitted daily or
weekly to the main office to be keyed into the database. Store personnel
would then ask the Main Office for status questions on a particular invoice.

Art

 

[Steve Schapel]

Crystal,

There is a different way to accomplish what you need... but it would be
unprofessional of me to advise such a course.