Invoicing from multiple tables - thoughts?

[Evan M]

Hello, I am trying to generate invoices from different forms (based on two
completely different sales tables). Does anyone else have any insight on how
to do this?

My problem is that while there are two distinctly different tables i am
trying to create Invoices from, each table's PKs are incremental
autonumbers. So, if i were to enter "1" in the invoices table to link it
back to another record in another table, it could possibly be a record in
both tables.

any assistance would be great! Thanks!
Evan M
evan AT radiologyonesource DOT com

 

[Lynn Trapp]

Linking 2 tables on their respective AutoNumber fields assumes that there is
a 1 to 1 relationship between the tables. That means that for every record
in table 1 there is 1 and only 1 record in table 2. This kind of
relationship is typically only used for subclassing records, thus removing
excessive null values from a table.

Is this what your two tables are like? If not, could you post the structures
of the tables and a bit more information about what you are trying to do.

 

[Evan M]

Hi Lynn, I'm sorry - i wasn't very clear at first. I have two tables, one
for parts inventory, and another for system inventory. Parts looks similar
to this:

PartID; (PK)
PartNumber
Manufacturer
Price

and Systems this:

SystemID; (PK)
Manufacturer
Model
Price

My thoughts to create an invoice that could include both parts and systems
was to create a thrid table, (to be related to my main invoicing table which
contains the company we are invoicing, due dates, etc via a unique
invoiceID) which contains the invoiceID, partID and SystemID, yet i am
unsure how i could get this to display when i would query it for use in a
subform. I know there has to be an easier way to do this, but my mind is
waay too small to deal figure it out! Thanks for the help!

Evan Mc
evan AT radiologyonesource DOT com

 

[Lynn Trapp]

Evan,
At first look, I think I would probably approach this differently, but you
may be stuck with your current structure. If I were doing this from scratch
I would most likely only have the one table for items with a type field to
distinguish between a part and a system. Then I could simply relate the item
table to the invoice table. The structure would be something like this:

tblItems
ItemID (PK)
Part/ModelNumber
Manufacturer
Price

tblInvoices
InvoiceId (PK)
InvoiceDate

tblInvoiceDetails
InvoiceDetailID (PK)
InvoiceId (FK)
ItemId

 

[Evan M]

Lynn,

Yes, i wish that were the case - that's the way i would have set it up to!
Unfortunately i only inherated this database and now there are over 3500
records in one table and over 1000 in the other. Thanks for your assistance
though! I'm going to try to muddle through this.

Thanks again!
Evan M
evan AT radiologyonesource DOT com

 

[John Vinson]

Hello, I am trying to generate invoices from different forms (based on two
completely different sales tables). Does anyone else have any insight on how
to do this?

My problem is that while there are two distinctly different tables i am
trying to create Invoices from, each table's PKs are incremental
autonumbers. So, if i were to enter "1" in the invoices table to link it
back to another record in another table, it could possibly be a record in
both tables.

As Lynn says, it's almost certain that joining two tables autonumber
to autonumer is just plain WRONG. You have no control over autonumber
values; there will always be gaps, so you have no assurance whatever
that Invoice 3187 in TableA has any connection whatsoever with Invoice
3187 in TableB.

How are the sales tables related? What information needs to be
combined?

John W. Vinson[MVP]

 

[Evan M]

John, I am trying to generate invoices from two sales tables which have
really nothing in common. My goal is to create an invoice that can
accept/show both information from the parts table and info from the systems
table. I'm going to copy and paste one of my previous postings for further
explaination. Thanks for your assistance!

Hi Lynn, I'm sorry - i wasn't very clear at first. I have two tables, one
for parts inventory, and another for system inventory. Parts looks similar
to this:

PartID; (PK)
PartNumber
Manufacturer
Price

and Systems this:

SystemID; (PK)
Manufacturer
Model
Price

My thoughts to create an invoice that could include both parts and systems
was to create a thrid table, (to be related to my main invoicing table which
contains the company we are invoicing, due dates, etc via a unique
invoiceID) which contains the invoiceID, partID and SystemID, yet i am
unsure how i could get this to display when i would query it for use in a
subform. I know there has to be an easier way to do this, but my mind is
waay too small to deal figure it out!

Thanks again John, Evan Mc; evan AT radiologyonesource DOT com

 

[John Vinson]

My thoughts to create an invoice that could include both parts and systems
was to create a thrid table, (to be related to my main invoicing table which
contains the company we are invoicing, due dates, etc via a unique
invoiceID) which contains the invoiceID, partID and SystemID, yet i am
unsure how i could get this to display when i would query it for use in a
subform. I know there has to be an easier way to do this, but my mind is
waay too small to deal figure it out!

I suspect that you'll need a Form (or Report, for printing) with two
Subforms (Subreports), one for each table - but it's not altogether
clear to me how you would lik them! By manufacturer perhaps?

John W. Vinson[MVP]

 

[Lynn Trapp]

Evan,
Uh oh....don't ya just hate inheriting stuff like that. You might look into
John's suggestion for relating the tables by Manufacturer, but I'm not sure
that will work either. If you use your third table approach, you will
probably have to enter all the PartIDs and SystemIDs manually, since there
is no link between them. As long as you don't make any data entry errors you
might be ok and, going forward, you could use that junction table.

Good luck.

 

[Evan M]

Hmmm, ok. I'll see what i can do with that! This shall be interesting, to
say the least.

Thanks John and Lynn!
Evan Mc
evan AT radiologyonesource DOT com