I need help on this please

[Mike]

Hi, I postthis question alomost a month ago and got few
suggestions, but couldn't get it to work. I'm back and
here it is again.

Good old Inventory question.

one table as Inventory "tblInventory" another table for
Transaction "tblTransaction".
I have a frmInventory that is used to enter data into the
tblInventory. For transactions, i.e. items moving out of
the inventory and out of the shop, I need to have a from
on which I can enter more than one items at a time and
charge them to a job number. I have created another
table, "tblInvoice" with InvoiceNumber (code generate
it),CustomerName, and InvoiceDate on it and created a
form based on this tblInvoice, continous form and had the
Transaction form, frmTransation on this form as a
subform. The job number comes from another table, tblJobs
and is a Combo box onthe FrmTransaction. Now I enter one
record, its ok, as soon as I enter a second record, same
InvoiceNumber, same Invoicedate, and same JobNumber, I
get an error message that cannot create duplicate value.

I have played wiht the primKey of these tables as below:

Index, InvoiceNumber, Index primKey for the tblInvoice
Index, PartNumber - PrimKey for the tblTransaction

I have made the index prim key but still getting the same
message. I tried to change the primkey in my tblJobs to
index form the jobNumber but same problem.

can someone please help me get this done. I'm running out
of time and under a lot of pressue from my user community.

Thank you in advance.

Mike

 

[Steve Schapel]

Mike,

I am guessing that your tblTransaction table contains fields such
as...
TransactionID (primary key)
TransactionDate
TransactionType (i.e. in, out)
PartNumber
InvoiceNumber (for those transactions that are invoice-based)

Am I close?

Either way, I recently saw a database where the person had also got
into a tangle. Here is how it was resolved...
1. Delete all forms and queries from the database, and don't even
think about forms or queries or comboboxes etc again until step 2 is
completed.
2. Get a big sheet of paper and a pencil and an eraser, work out all
the data subjects you require, and the relationships between them, and
then draw out a map of the table structure required to reflect the
data reality.

- Steve Schapel, Microsoft Access MVP

 

[Mike]

Thanks Steve,

I certainly do that but one thing that I don't understand
is that, should PartNumber be a primkey at all for an
Inventory and Transaction tables?

You were close, yes thay are invoiced based and I really
don't know what should be the primkey for these tabkes
out of the following candiates:

AutoNumber
InvoiceNumber
PartNumber
InvoiceDate
JobNumber

Thanks,

Mike

 

[Steve Schapel]

Mike,

The purpose of a Primary Key field is to uniquely identify each record
in a table. PartNumber is a good candidate for the primary key field
in a Parts table, where each Part is listed as a separate record, and
the PartNumber will not be duplicated, i.e. will not appear in more
than one record, since you will not have two parts with the same
PartNumber. If this is what the Inventory table is, i.e. a listing of
Parts with each Part being listed once, then PartNumber can therefore
serve as the primary key for this table. PartNumber could not be the
primary key field in the Transaction table, because there can be more
than one Transaction recorded for each Part. In the Transaction
table, PartNumber will be a foreign key, and the basis of a
one-to-many relationship with the Inventory table.

InvoiceNumber will probably be the primary key in your Invoices table.
InvoiceDate is unlikely to be a primary key in any table, because
presumably there can be more than one invoice on any given date. In
any case, it is wise to avoid using a date/time field as a primary
key... theoretically possible, but unnecessarily complicated. I don't
undersatand how Jobs fit into the database, or what a JobNumber refers
to, or what the relationship is between Jobs and any other entity, so
I can't comment on this.

- Steve Schapel, Microsoft Access MVP