Problem with relationship??

[Teri]

I'm not sure if the problem I am currently having has to do with the
relationship or not. I have a subform which includes a field for a seriel
number. The subform draws on a query which in turn draws from three
different tables (Orders, Order Details and SerielNumber). The relationship
from Orders to Order Details is one-to-many with many on the Order Details
table; the relationship from Order Details to SerielNumber is one to many
with the many on the Order Details table. (The SQL version of the query is
as follows:

SELECT Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate,
Orders.PurchaseOrderNumber, Orders.ShipDate, Orders.ShippingMethodID,
Orders.SalesTaxRate, [Order Details].OrderID, [Order Details].ProductID,
[Order Details].Quantity, [Order Details].UnitPrice, [Order Details].Price,
[Order Details].Discount, SerielNumber.*
FROM SerielNumber INNER JOIN (Orders INNER JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID) ON SerielNumber.SerielNumberID =
[Order Details].SerielNumberID;

When I put a seriel number into the seriel number field of the subform I get
an error message saying the following: "You cannot add or change a record
because a related record is requred in table 'SerielNumber'." I need to be
able to input a seriel number which is not a unique number in any way (it may
be repeated several times in conjunction with several different customers for
a variety of reasons). If I turn off the referential integrity I am able to
input the seriel number and it will appear in the Order Details table in the
SerielNumberID field, but it does not appear in my Seriel Number table.

Is this the way it is supposed to be? Or did I screw up somewhere?

Thanks,

Teri.

 

[Jeff Boyce]

Teri

I'm not sure, but it sounds like you could use the same serial number in
more than one order detail. If so, you have a many-to-many relationship
between those. In Access, you need a third table to resolve the m:m.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Teri]

I was thinking it was a M:M relationship. But, because I really can't seem
to get a grasp on this relational thing, I don't know how to set one of those
up. When I create yet another table, how do I relate it so that it does what
I want and would I have to change my query at all?

Thanks Jeff!

Teri.

Teri

I'm not sure, but it sounds like you could use the same serial number in
more than one order detail. If so, you have a many-to-many relationship
between those. In Access, you need a third table to resolve the m:m.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm not sure if the problem I am currently having has to do with the
relationship or not. I have a subform which includes a field for a seriel
number. The subform draws on a query which in turn draws from three
different tables (Orders, Order Details and SerielNumber). The
relationship
from Orders to Order Details is one-to-many with many on the Order Details
table; the relationship from Order Details to SerielNumber is one to many
with the many on the Order Details table. (The SQL version of the query
is
as follows:

SELECT Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate,
Orders.PurchaseOrderNumber, Orders.ShipDate, Orders.ShippingMethodID,
Orders.SalesTaxRate, [Order Details].OrderID, [Order Details].ProductID,
[Order Details].Quantity, [Order Details].UnitPrice, [Order
Details].Price,
[Order Details].Discount, SerielNumber.*
FROM SerielNumber INNER JOIN (Orders INNER JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID) ON SerielNumber.SerielNumberID =
[Order Details].SerielNumberID;

When I put a seriel number into the seriel number field of the subform I
get
an error message saying the following: "You cannot add or change a record
because a related record is requred in table 'SerielNumber'." I need to
be
able to input a seriel number which is not a unique number in any way (it
may
be repeated several times in conjunction with several different customers
for
a variety of reasons). If I turn off the referential integrity I am able
to
input the seriel number and it will appear in the Order Details table in
the
SerielNumberID field, but it does not appear in my Seriel Number table.

Is this the way it is supposed to be? Or did I screw up somewhere?

Thanks,

Teri.

 

[Jeff Boyce]

First thing first, Teri.

To "resolve" a m:m relationship, you have one table that holds one side, a
second table that holds the other side, and a third table that show valid
pairs. In your situation, you have an table for OrderDetails, a table for
SerialNumbers, and a "resolver" table that holds valid combinations of
OrderDetailID and SerialNumberID (the respective IDs from those two tables).

Get the data structured first, then work on the queries, etc.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I was thinking it was a M:M relationship. But, because I really can't seem
to get a grasp on this relational thing, I don't know how to set one of
those
up. When I create yet another table, how do I relate it so that it does
what
I want and would I have to change my query at all?

Thanks Jeff!

Teri.

Teri

I'm not sure, but it sounds like you could use the same serial number in
more than one order detail. If so, you have a many-to-many relationship
between those. In Access, you need a third table to resolve the m:m.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm not sure if the problem I am currently having has to do with the
relationship or not. I have a subform which includes a field for a
seriel
number. The subform draws on a query which in turn draws from three
different tables (Orders, Order Details and SerielNumber). The
relationship
from Orders to Order Details is one-to-many with many on the Order
Details
table; the relationship from Order Details to SerielNumber is one to
many
with the many on the Order Details table. (The SQL version of the
query
is
as follows:

SELECT Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate,
Orders.PurchaseOrderNumber, Orders.ShipDate, Orders.ShippingMethodID,
Orders.SalesTaxRate, [Order Details].OrderID, [Order
Details].ProductID,
[Order Details].Quantity, [Order Details].UnitPrice, [Order
Details].Price,
[Order Details].Discount, SerielNumber.*
FROM SerielNumber INNER JOIN (Orders INNER JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID) ON
SerielNumber.SerielNumberID =
[Order Details].SerielNumberID;

When I put a seriel number into the seriel number field of the subform
I
get
an error message saying the following: "You cannot add or change a
record
because a related record is requred in table 'SerielNumber'." I need
to
be
able to input a seriel number which is not a unique number in any way
(it
may
be repeated several times in conjunction with several different
customers
for
a variety of reasons). If I turn off the referential integrity I am
able
to
input the seriel number and it will appear in the Order Details table
in
the
SerielNumberID field, but it does not appear in my Seriel Number table.

Is this the way it is supposed to be? Or did I screw up somewhere?

Thanks,

Teri.

 

[Teri]

Jeff,

I'm not entirely sure what you mean by "Get the data structured first. . .",
but I created another table and called it ValidSerielNumbers. I put the
primary key as ValidSeriel, then added OrderDetailID and SerielNumbersID to
the table and a field to hold the seriel number. Is this what you meant?

I went to relationships and related the ValidSerielNumbers table to the
OrderDetails table (came up as 1:M) and then to the SerielNumbers table (also
came up as 1:M). How am I doing so far?

Thanks again!!!!!

Teri.

First thing first, Teri.

To "resolve" a m:m relationship, you have one table that holds one side, a
second table that holds the other side, and a third table that show valid
pairs. In your situation, you have an table for OrderDetails, a table for
SerialNumbers, and a "resolver" table that holds valid combinations of
OrderDetailID and SerialNumberID (the respective IDs from those two tables).

Get the data structured first, then work on the queries, etc.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I was thinking it was a M:M relationship. But, because I really can't seem
to get a grasp on this relational thing, I don't know how to set one of
those
up. When I create yet another table, how do I relate it so that it does
what
I want and would I have to change my query at all?

Thanks Jeff!

Teri.

Teri

I'm not sure, but it sounds like you could use the same serial number in
more than one order detail. If so, you have a many-to-many relationship
between those. In Access, you need a third table to resolve the m:m.

Regards

Jeff Boyce
Microsoft Office/Access MVP


I'm not sure if the problem I am currently having has to do with the
relationship or not. I have a subform which includes a field for a
seriel
number. The subform draws on a query which in turn draws from three
different tables (Orders, Order Details and SerielNumber). The
relationship
from Orders to Order Details is one-to-many with many on the Order
Details
table; the relationship from Order Details to SerielNumber is one to
many
with the many on the Order Details table. (The SQL version of the
query
is
as follows:

SELECT Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate,
Orders.PurchaseOrderNumber, Orders.ShipDate, Orders.ShippingMethodID,
Orders.SalesTaxRate, [Order Details].OrderID, [Order
Details].ProductID,
[Order Details].Quantity, [Order Details].UnitPrice, [Order
Details].Price,
[Order Details].Discount, SerielNumber.*
FROM SerielNumber INNER JOIN (Orders INNER JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID) ON
SerielNumber.SerielNumberID =
[Order Details].SerielNumberID;

When I put a seriel number into the seriel number field of the subform
I
get
an error message saying the following: "You cannot add or change a
record
because a related record is requred in table 'SerielNumber'." I need
to
be
able to input a seriel number which is not a unique number in any way
(it
may
be repeated several times in conjunction with several different
customers
for
a variety of reasons). If I turn off the referential integrity I am
able
to
input the seriel number and it will appear in the Order Details table
in
the
SerielNumberID field, but it does not appear in my Seriel Number table.

Is this the way it is supposed to be? Or did I screw up somewhere?

Thanks,

Teri.

 

[Jeff Boyce]

Teri

Sounds like you've set it up correctly (but I don't understand about "a
field to hold the Serial Number". You do have two fields, right? One for
the OrderDetailID and one for the SerialNumberID? You don't need to put the
actual SerialNumber in this "resolver" table, since your SerialNumber table
should already have it. You are just using the resolver table to hold the
respective row IDs from the other two tables.

First things first -- I was suggesting that you get the data structure
correct first, as everything else you'll be doing will depend on that.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

I'm not entirely sure what you mean by "Get the data structured first. .
.",
but I created another table and called it ValidSerielNumbers. I put the
primary key as ValidSeriel, then added OrderDetailID and SerielNumbersID
to
the table and a field to hold the seriel number. Is this what you meant?

I went to relationships and related the ValidSerielNumbers table to the
OrderDetails table (came up as 1:M) and then to the SerielNumbers table
(also
came up as 1:M). How am I doing so far?

Thanks again!!!!!

Teri.

First thing first, Teri.

To "resolve" a m:m relationship, you have one table that holds one side,
a
second table that holds the other side, and a third table that show valid
pairs. In your situation, you have an table for OrderDetails, a table
for
SerialNumbers, and a "resolver" table that holds valid combinations of
OrderDetailID and SerialNumberID (the respective IDs from those two
tables).

Get the data structured first, then work on the queries, etc.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I was thinking it was a M:M relationship. But, because I really can't
seem
to get a grasp on this relational thing, I don't know how to set one of
those
up. When I create yet another table, how do I relate it so that it
does
what
I want and would I have to change my query at all?

Thanks Jeff!

Teri.

:

Teri

I'm not sure, but it sounds like you could use the same serial number
in
more than one order detail. If so, you have a many-to-many
relationship
between those. In Access, you need a third table to resolve the m:m.

Regards

Jeff Boyce
Microsoft Office/Access MVP


I'm not sure if the problem I am currently having has to do with the
relationship or not. I have a subform which includes a field for a
seriel
number. The subform draws on a query which in turn draws from three
different tables (Orders, Order Details and SerielNumber). The
relationship
from Orders to Order Details is one-to-many with many on the Order
Details
table; the relationship from Order Details to SerielNumber is one to
many
with the many on the Order Details table. (The SQL version of the
query
is
as follows:

SELECT Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate,
Orders.PurchaseOrderNumber, Orders.ShipDate,
Orders.ShippingMethodID,
Orders.SalesTaxRate, [Order Details].OrderID, [Order
Details].ProductID,
[Order Details].Quantity, [Order Details].UnitPrice, [Order
Details].Price,
[Order Details].Discount, SerielNumber.*
FROM SerielNumber INNER JOIN (Orders INNER JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID) ON
SerielNumber.SerielNumberID =
[Order Details].SerielNumberID;

When I put a seriel number into the seriel number field of the
subform
I
get
an error message saying the following: "You cannot add or change a
record
because a related record is requred in table 'SerielNumber'." I
need
to
be
able to input a seriel number which is not a unique number in any
way
(it
may
be repeated several times in conjunction with several different
customers
for
a variety of reasons). If I turn off the referential integrity I am
able
to
input the seriel number and it will appear in the Order Details
table
in
the
SerielNumberID field, but it does not appear in my Seriel Number
table.

Is this the way it is supposed to be? Or did I screw up somewhere?

Thanks,

Teri.

 

[Teri]

I now have just the two fields in my "resolver" table. But I still do not
understand what you meant by structuring my data. Is that what I am doing by
creating this new table?

Thank you,

Teri.

Teri

Sounds like you've set it up correctly (but I don't understand about "a
field to hold the Serial Number". You do have two fields, right? One for
the OrderDetailID and one for the SerialNumberID? You don't need to put the
actual SerialNumber in this "resolver" table, since your SerialNumber table
should already have it. You are just using the resolver table to hold the
respective row IDs from the other two tables.

First things first -- I was suggesting that you get the data structure
correct first, as everything else you'll be doing will depend on that.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

I'm not entirely sure what you mean by "Get the data structured first. .
.",
but I created another table and called it ValidSerielNumbers. I put the
primary key as ValidSeriel, then added OrderDetailID and SerielNumbersID
to
the table and a field to hold the seriel number. Is this what you meant?

I went to relationships and related the ValidSerielNumbers table to the
OrderDetails table (came up as 1:M) and then to the SerielNumbers table
(also
came up as 1:M). How am I doing so far?

Thanks again!!!!!

Teri.

First thing first, Teri.

To "resolve" a m:m relationship, you have one table that holds one side,
a
second table that holds the other side, and a third table that show valid
pairs. In your situation, you have an table for OrderDetails, a table
for
SerialNumbers, and a "resolver" table that holds valid combinations of
OrderDetailID and SerialNumberID (the respective IDs from those two
tables).

Get the data structured first, then work on the queries, etc.

Regards

Jeff Boyce
Microsoft Office/Access MVP


I was thinking it was a M:M relationship. But, because I really can't
seem
to get a grasp on this relational thing, I don't know how to set one of
those
up. When I create yet another table, how do I relate it so that it
does
what
I want and would I have to change my query at all?

Thanks Jeff!

Teri.

:

Teri

I'm not sure, but it sounds like you could use the same serial number
in
more than one order detail. If so, you have a many-to-many
relationship
between those. In Access, you need a third table to resolve the m:m.

Regards

Jeff Boyce
Microsoft Office/Access MVP


I'm not sure if the problem I am currently having has to do with the
relationship or not. I have a subform which includes a field for a
seriel
number. The subform draws on a query which in turn draws from three
different tables (Orders, Order Details and SerielNumber). The
relationship
from Orders to Order Details is one-to-many with many on the Order
Details
table; the relationship from Order Details to SerielNumber is one to
many
with the many on the Order Details table. (The SQL version of the
query
is
as follows:

SELECT Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate,
Orders.PurchaseOrderNumber, Orders.ShipDate,
Orders.ShippingMethodID,
Orders.SalesTaxRate, [Order Details].OrderID, [Order
Details].ProductID,
[Order Details].Quantity, [Order Details].UnitPrice, [Order
Details].Price,
[Order Details].Discount, SerielNumber.*
FROM SerielNumber INNER JOIN (Orders INNER JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID) ON
SerielNumber.SerielNumberID =
[Order Details].SerielNumberID;

When I put a seriel number into the seriel number field of the
subform
I
get
an error message saying the following: "You cannot add or change a
record
because a related record is requred in table 'SerielNumber'." I
need
to
be
able to input a seriel number which is not a unique number in any
way
(it
may
be repeated several times in conjunction with several different
customers
for
a variety of reasons). If I turn off the referential integrity I am
able
to
input the seriel number and it will appear in the Order Details
table
in
the
SerielNumberID field, but it does not appear in my Seriel Number
table.

Is this the way it is supposed to be? Or did I screw up somewhere?

Thanks,

Teri.

 

[Teri]

Sorry, correction, I have three fields in my table: ValidSerielID,
OrderDetailID and SerielNumbersID.

I now have just the two fields in my "resolver" table. But I still do not
understand what you meant by structuring my data. Is that what I am doing by
creating this new table?

Thank you,

Teri.

Teri

Sounds like you've set it up correctly (but I don't understand about "a
field to hold the Serial Number". You do have two fields, right? One for
the OrderDetailID and one for the SerialNumberID? You don't need to put the
actual SerialNumber in this "resolver" table, since your SerialNumber table
should already have it. You are just using the resolver table to hold the
respective row IDs from the other two tables.

First things first -- I was suggesting that you get the data structure
correct first, as everything else you'll be doing will depend on that.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

I'm not entirely sure what you mean by "Get the data structured first. .
.",
but I created another table and called it ValidSerielNumbers. I put the
primary key as ValidSeriel, then added OrderDetailID and SerielNumbersID
to
the table and a field to hold the seriel number. Is this what you meant?

I went to relationships and related the ValidSerielNumbers table to the
OrderDetails table (came up as 1:M) and then to the SerielNumbers table
(also
came up as 1:M). How am I doing so far?

Thanks again!!!!!

Teri.

:

First thing first, Teri.

To "resolve" a m:m relationship, you have one table that holds one side,
a
second table that holds the other side, and a third table that show valid
pairs. In your situation, you have an table for OrderDetails, a table
for
SerialNumbers, and a "resolver" table that holds valid combinations of
OrderDetailID and SerialNumberID (the respective IDs from those two
tables).

Get the data structured first, then work on the queries, etc.

Regards

Jeff Boyce
Microsoft Office/Access MVP


I was thinking it was a M:M relationship. But, because I really can't
seem
to get a grasp on this relational thing, I don't know how to set one of
those
up. When I create yet another table, how do I relate it so that it
does
what
I want and would I have to change my query at all?

Thanks Jeff!

Teri.

:

Teri

I'm not sure, but it sounds like you could use the same serial number
in
more than one order detail. If so, you have a many-to-many
relationship
between those. In Access, you need a third table to resolve the m:m.

Regards

Jeff Boyce
Microsoft Office/Access MVP


I'm not sure if the problem I am currently having has to do with the
relationship or not. I have a subform which includes a field for a
seriel
number. The subform draws on a query which in turn draws from three
different tables (Orders, Order Details and SerielNumber). The
relationship
from Orders to Order Details is one-to-many with many on the Order
Details
table; the relationship from Order Details to SerielNumber is one to
many
with the many on the Order Details table. (The SQL version of the
query
is
as follows:

SELECT Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate,
Orders.PurchaseOrderNumber, Orders.ShipDate,
Orders.ShippingMethodID,
Orders.SalesTaxRate, [Order Details].OrderID, [Order
Details].ProductID,
[Order Details].Quantity, [Order Details].UnitPrice, [Order
Details].Price,
[Order Details].Discount, SerielNumber.*
FROM SerielNumber INNER JOIN (Orders INNER JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID) ON
SerielNumber.SerielNumberID =
[Order Details].SerielNumberID;

When I put a seriel number into the seriel number field of the
subform
I
get
an error message saying the following: "You cannot add or change a
record
because a related record is requred in table 'SerielNumber'." I
need
to
be
able to input a seriel number which is not a unique number in any
way
(it
may
be repeated several times in conjunction with several different
customers
for
a variety of reasons). If I turn off the referential integrity I am
able
to
input the seriel number and it will appear in the Order Details
table
in
the
SerielNumberID field, but it does not appear in my Seriel Number
table.

Is this the way it is supposed to be? Or did I screw up somewhere?

Thanks,

Teri.

 

[Jeff Boyce]

Yes, that's what I meant. Yes, three fields. You could also get by with
just the two RowIDs, and use them as a multi-column primary key. If you ask
for the 'groups' recommendations on this, brace yourself for a "religious
war". Some folks swear by only including the key fields from the related
tables, and using the combination as a unique primary key. Others add (as
you have) a unique row identifier (?Autonumber, right?).

If you will only ever have one "pair" valid in that resolver table, you'll
need to add a unique index on the pair of fields (which is one of the
arguments for making the combination the unique primary key). Adding the
index assures you'll never have more than one set of the same pair of IDs.

If, on the other hand, you might be tracking changes over time, you'd need
to add begin and end date fields to the resolver table. Think of it a
little like a student, a class, and an enrollment (student signed up for
class). Theoretically, the same student could sign up for the same class
more than once, so you'd need one/two date fields to track WHEN the sign up
was.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Sorry, correction, I have three fields in my table: ValidSerielID,
OrderDetailID and SerielNumbersID.

I now have just the two fields in my "resolver" table. But I still do
not
understand what you meant by structuring my data. Is that what I am
doing by
creating this new table?

Thank you,

Teri.

Teri

Sounds like you've set it up correctly (but I don't understand about "a
field to hold the Serial Number". You do have two fields, right? One
for
the OrderDetailID and one for the SerialNumberID? You don't need to
put the
actual SerialNumber in this "resolver" table, since your SerialNumber
table
should already have it. You are just using the resolver table to hold
the
respective row IDs from the other two tables.

First things first -- I was suggesting that you get the data structure
correct first, as everything else you'll be doing will depend on that.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

I'm not entirely sure what you mean by "Get the data structured
first. .
.",
but I created another table and called it ValidSerielNumbers. I put
the
primary key as ValidSeriel, then added OrderDetailID and
SerielNumbersID
to
the table and a field to hold the seriel number. Is this what you
meant?

I went to relationships and related the ValidSerielNumbers table to
the
OrderDetails table (came up as 1:M) and then to the SerielNumbers
table
(also
came up as 1:M). How am I doing so far?

Thanks again!!!!!

Teri.

:

First thing first, Teri.

To "resolve" a m:m relationship, you have one table that holds one
side,
a
second table that holds the other side, and a third table that show
valid
pairs. In your situation, you have an table for OrderDetails, a
table
for
SerialNumbers, and a "resolver" table that holds valid combinations
of
OrderDetailID and SerialNumberID (the respective IDs from those two
tables).

Get the data structured first, then work on the queries, etc.

Regards

Jeff Boyce
Microsoft Office/Access MVP


I was thinking it was a M:M relationship. But, because I really
can't
seem
to get a grasp on this relational thing, I don't know how to set
one of
those
up. When I create yet another table, how do I relate it so that
it
does
what
I want and would I have to change my query at all?

Thanks Jeff!

Teri.

:

Teri

I'm not sure, but it sounds like you could use the same serial
number
in
more than one order detail. If so, you have a many-to-many
relationship
between those. In Access, you need a third table to resolve the
m:m.

Regards

Jeff Boyce
Microsoft Office/Access MVP


I'm not sure if the problem I am currently having has to do
with the
relationship or not. I have a subform which includes a field
for a
seriel
number. The subform draws on a query which in turn draws from
three
different tables (Orders, Order Details and SerielNumber). The
relationship
from Orders to Order Details is one-to-many with many on the
Order
Details
table; the relationship from Order Details to SerielNumber is
one to
many
with the many on the Order Details table. (The SQL version of
the
query
is
as follows:

SELECT Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate,
Orders.PurchaseOrderNumber, Orders.ShipDate,
Orders.ShippingMethodID,
Orders.SalesTaxRate, [Order Details].OrderID, [Order
Details].ProductID,
[Order Details].Quantity, [Order Details].UnitPrice, [Order
Details].Price,
[Order Details].Discount, SerielNumber.*
FROM SerielNumber INNER JOIN (Orders INNER JOIN [Order Details]
ON
Orders.OrderID = [Order Details].OrderID) ON
SerielNumber.SerielNumberID =
[Order Details].SerielNumberID;

When I put a seriel number into the seriel number field of the
subform
I
get
an error message saying the following: "You cannot add or
change a
record
because a related record is requred in table 'SerielNumber'."
I
need
to
be
able to input a seriel number which is not a unique number in
any
way
(it
may
be repeated several times in conjunction with several different
customers
for
a variety of reasons). If I turn off the referential integrity
I am
able
to
input the seriel number and it will appear in the Order Details
table
in
the
SerielNumberID field, but it does not appear in my Seriel
Number
table.

Is this the way it is supposed to be? Or did I screw up
somewhere?

Thanks,

Teri.

 

[Teri]

I will need to create a report in the future which will simply list the
seriel number and all the record numbers associated with that seriel number
"since the beginning of time" for that seriel number. So I believe I would
need to go with your second option. But I do need to ask, do I really need
to add date fields if dates are not going to be an issue in this report? The
only date that will be involved is when it is created. It will be set up
similar to:
Seriel Number Record Number
1133 1
2
3
268
1134 7
6
22
Date Printed: 5/30/2006

Yes, that's what I meant. Yes, three fields. You could also get by with
just the two RowIDs, and use them as a multi-column primary key. If you ask
for the 'groups' recommendations on this, brace yourself for a "religious
war". Some folks swear by only including the key fields from the related
tables, and using the combination as a unique primary key. Others add (as
you have) a unique row identifier (?Autonumber, right?).

If you will only ever have one "pair" valid in that resolver table, you'll
need to add a unique index on the pair of fields (which is one of the
arguments for making the combination the unique primary key). Adding the
index assures you'll never have more than one set of the same pair of IDs.

If, on the other hand, you might be tracking changes over time, you'd need
to add begin and end date fields to the resolver table. Think of it a
little like a student, a class, and an enrollment (student signed up for
class). Theoretically, the same student could sign up for the same class
more than once, so you'd need one/two date fields to track WHEN the sign up
was.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Sorry, correction, I have three fields in my table: ValidSerielID,
OrderDetailID and SerielNumbersID.

I now have just the two fields in my "resolver" table. But I still do
not
understand what you meant by structuring my data. Is that what I am
doing by
creating this new table?

Thank you,

Teri.

:

Teri

Sounds like you've set it up correctly (but I don't understand about "a
field to hold the Serial Number". You do have two fields, right? One
for
the OrderDetailID and one for the SerialNumberID? You don't need to
put the
actual SerialNumber in this "resolver" table, since your SerialNumber
table
should already have it. You are just using the resolver table to hold
the
respective row IDs from the other two tables.

First things first -- I was suggesting that you get the data structure
correct first, as everything else you'll be doing will depend on that.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

I'm not entirely sure what you mean by "Get the data structured
first. .
.",
but I created another table and called it ValidSerielNumbers. I put
the
primary key as ValidSeriel, then added OrderDetailID and
SerielNumbersID
to
the table and a field to hold the seriel number. Is this what you
meant?

I went to relationships and related the ValidSerielNumbers table to
the
OrderDetails table (came up as 1:M) and then to the SerielNumbers
table
(also
came up as 1:M). How am I doing so far?

Thanks again!!!!!

Teri.

:

First thing first, Teri.

To "resolve" a m:m relationship, you have one table that holds one
side,
a
second table that holds the other side, and a third table that show
valid
pairs. In your situation, you have an table for OrderDetails, a
table
for
SerialNumbers, and a "resolver" table that holds valid combinations
of
OrderDetailID and SerialNumberID (the respective IDs from those two
tables).

Get the data structured first, then work on the queries, etc.

Regards

Jeff Boyce
Microsoft Office/Access MVP


I was thinking it was a M:M relationship. But, because I really
can't
seem
to get a grasp on this relational thing, I don't know how to set
one of
those
up. When I create yet another table, how do I relate it so that
it
does
what
I want and would I have to change my query at all?

Thanks Jeff!

Teri.

:

Teri

I'm not sure, but it sounds like you could use the same serial
number
in
more than one order detail. If so, you have a many-to-many
relationship
between those. In Access, you need a third table to resolve the
m:m.

Regards

Jeff Boyce
Microsoft Office/Access MVP


I'm not sure if the problem I am currently having has to do
with the
relationship or not. I have a subform which includes a field
for a
seriel
number. The subform draws on a query which in turn draws from
three
different tables (Orders, Order Details and SerielNumber). The
relationship
from Orders to Order Details is one-to-many with many on the
Order
Details
table; the relationship from Order Details to SerielNumber is
one to
many
with the many on the Order Details table. (The SQL version of
the
query
is
as follows:

SELECT Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate,
Orders.PurchaseOrderNumber, Orders.ShipDate,
Orders.ShippingMethodID,
Orders.SalesTaxRate, [Order Details].OrderID, [Order
Details].ProductID,
[Order Details].Quantity, [Order Details].UnitPrice, [Order
Details].Price,
[Order Details].Discount, SerielNumber.*
FROM SerielNumber INNER JOIN (Orders INNER JOIN [Order Details]
ON
Orders.OrderID = [Order Details].OrderID) ON
SerielNumber.SerielNumberID =
[Order Details].SerielNumberID;

When I put a seriel number into the seriel number field of the
subform
I
get
an error message saying the following: "You cannot add or
change a
record
because a related record is requred in table 'SerielNumber'."
I
need
to
be
able to input a seriel number which is not a unique number in
any
way
(it
may
be repeated several times in conjunction with several different
customers
for
a variety of reasons). If I turn off the referential integrity
I am
able
to
input the seriel number and it will appear in the Order Details
table
in
the
SerielNumberID field, but it does not appear in my Seriel
Number
table.

Is this the way it is supposed to be? Or did I screw up
somewhere?

Thanks,

Teri.

 

[Jeff Boyce]

Your reports don't have to show 100% of what you store (as data).

If you wish to prevent duplication (Same record number/serial number
combination), you'll need to index that combination. If you can
legitimately have more than one combination of the same serial number and
record number, you need a "tie-breaker" like date/time or something. If you
can only have one legitimate combination, don't bother.

And yes, your report can be laid out as you described. When you get your
data set up, create a query to return Serial Number and Record Number.

Check on the HideDuplicates property in the report definition you create.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I will need to create a report in the future which will simply list the
seriel number and all the record numbers associated with that seriel
number
"since the beginning of time" for that seriel number. So I believe I
would
need to go with your second option. But I do need to ask, do I really
need
to add date fields if dates are not going to be an issue in this report?
The
only date that will be involved is when it is created. It will be set up
similar to:
Seriel Number Record Number
1133 1
2
3
268
1134 7
6
22
Date Printed: 5/30/2006

Yes, that's what I meant. Yes, three fields. You could also get by with
just the two RowIDs, and use them as a multi-column primary key. If you
ask
for the 'groups' recommendations on this, brace yourself for a "religious
war". Some folks swear by only including the key fields from the related
tables, and using the combination as a unique primary key. Others add
(as
you have) a unique row identifier (?Autonumber, right?).

If you will only ever have one "pair" valid in that resolver table,
you'll
need to add a unique index on the pair of fields (which is one of the
arguments for making the combination the unique primary key). Adding the
index assures you'll never have more than one set of the same pair of
IDs.

If, on the other hand, you might be tracking changes over time, you'd
need
to add begin and end date fields to the resolver table. Think of it a
little like a student, a class, and an enrollment (student signed up for
class). Theoretically, the same student could sign up for the same class
more than once, so you'd need one/two date fields to track WHEN the sign
up
was.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Sorry, correction, I have three fields in my table: ValidSerielID,
OrderDetailID and SerielNumbersID.

:

I now have just the two fields in my "resolver" table. But I still do
not
understand what you meant by structuring my data. Is that what I am
doing by
creating this new table?

Thank you,

Teri.

:

Teri

Sounds like you've set it up correctly (but I don't understand about
"a
field to hold the Serial Number". You do have two fields, right?
One
for
the OrderDetailID and one for the SerialNumberID? You don't need to
put the
actual SerialNumber in this "resolver" table, since your
SerialNumber
table
should already have it. You are just using the resolver table to
hold
the
respective row IDs from the other two tables.

First things first -- I was suggesting that you get the data
structure
correct first, as everything else you'll be doing will depend on
that.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

I'm not entirely sure what you mean by "Get the data structured
first. .
.",
but I created another table and called it ValidSerielNumbers. I
put
the
primary key as ValidSeriel, then added OrderDetailID and
SerielNumbersID
to
the table and a field to hold the seriel number. Is this what you
meant?

I went to relationships and related the ValidSerielNumbers table
to
the
OrderDetails table (came up as 1:M) and then to the SerielNumbers
table
(also
came up as 1:M). How am I doing so far?

Thanks again!!!!!

Teri.

:

First thing first, Teri.

To "resolve" a m:m relationship, you have one table that holds
one
side,
a
second table that holds the other side, and a third table that
show
valid
pairs. In your situation, you have an table for OrderDetails, a
table
for
SerialNumbers, and a "resolver" table that holds valid
combinations
of
OrderDetailID and SerialNumberID (the respective IDs from those
two
tables).

Get the data structured first, then work on the queries, etc.

Regards

Jeff Boyce
Microsoft Office/Access MVP


I was thinking it was a M:M relationship. But, because I really
can't
seem
to get a grasp on this relational thing, I don't know how to
set
one of
those
up. When I create yet another table, how do I relate it so
that
it
does
what
I want and would I have to change my query at all?

Thanks Jeff!

Teri.

:

Teri

I'm not sure, but it sounds like you could use the same serial
number
in
more than one order detail. If so, you have a many-to-many
relationship
between those. In Access, you need a third table to resolve
the
m:m.

Regards

Jeff Boyce
Microsoft Office/Access MVP


I'm not sure if the problem I am currently having has to do
with the
relationship or not. I have a subform which includes a
field
for a
seriel
number. The subform draws on a query which in turn draws
from
three
different tables (Orders, Order Details and SerielNumber).
The
relationship
from Orders to Order Details is one-to-many with many on the
Order
Details
table; the relationship from Order Details to SerielNumber
is
one to
many
with the many on the Order Details table. (The SQL version
of
the
query
is
as follows:

SELECT Orders.CustomerID, Orders.EmployeeID,
Orders.OrderDate,
Orders.PurchaseOrderNumber, Orders.ShipDate,
Orders.ShippingMethodID,
Orders.SalesTaxRate, [Order Details].OrderID, [Order
Details].ProductID,
[Order Details].Quantity, [Order Details].UnitPrice, [Order
Details].Price,
[Order Details].Discount, SerielNumber.*
FROM SerielNumber INNER JOIN (Orders INNER JOIN [Order
Details]
ON
Orders.OrderID = [Order Details].OrderID) ON
SerielNumber.SerielNumberID =
[Order Details].SerielNumberID;

When I put a seriel number into the seriel number field of
the
subform
I
get
an error message saying the following: "You cannot add or
change a
record
because a related record is requred in table
'SerielNumber'."
I
need
to
be
able to input a seriel number which is not a unique number
in
any
way
(it
may
be repeated several times in conjunction with several
different
customers
for
a variety of reasons). If I turn off the referential
integrity
I am
able
to
input the seriel number and it will appear in the Order
Details
table
in
the
SerielNumberID field, but it does not appear in my Seriel
Number
table.

Is this the way it is supposed to be? Or did I screw up
somewhere?

Thanks,

Teri.

 

[Teri]

Once I have my data input, how am I going to pull my query together? I have
three different tables which are all related. The reports I am going to have
to pull together are based on a certain product (3 different ones) and the
seriel number for that product. Am I going to pull the Product itself from
the Order Details table as the ProductID and the record number from the same
table and then the seriel number from the seriel number table? I really I am
making sense.

Thank you so much for all your help!!
Teri.

Your reports don't have to show 100% of what you store (as data).

If you wish to prevent duplication (Same record number/serial number
combination), you'll need to index that combination. If you can
legitimately have more than one combination of the same serial number and
record number, you need a "tie-breaker" like date/time or something. If you
can only have one legitimate combination, don't bother.

And yes, your report can be laid out as you described. When you get your
data set up, create a query to return Serial Number and Record Number.

Check on the HideDuplicates property in the report definition you create.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I will need to create a report in the future which will simply list the
seriel number and all the record numbers associated with that seriel
number
"since the beginning of time" for that seriel number. So I believe I
would
need to go with your second option. But I do need to ask, do I really
need
to add date fields if dates are not going to be an issue in this report?
The
only date that will be involved is when it is created. It will be set up
similar to:
Seriel Number Record Number
1133 1
2
3
268
1134 7
6
22
Date Printed: 5/30/2006

Yes, that's what I meant. Yes, three fields. You could also get by with
just the two RowIDs, and use them as a multi-column primary key. If you
ask
for the 'groups' recommendations on this, brace yourself for a "religious
war". Some folks swear by only including the key fields from the related
tables, and using the combination as a unique primary key. Others add
(as
you have) a unique row identifier (?Autonumber, right?).

If you will only ever have one "pair" valid in that resolver table,
you'll
need to add a unique index on the pair of fields (which is one of the
arguments for making the combination the unique primary key). Adding the
index assures you'll never have more than one set of the same pair of
IDs.

If, on the other hand, you might be tracking changes over time, you'd
need
to add begin and end date fields to the resolver table. Think of it a
little like a student, a class, and an enrollment (student signed up for
class). Theoretically, the same student could sign up for the same class
more than once, so you'd need one/two date fields to track WHEN the sign
up
was.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Sorry, correction, I have three fields in my table: ValidSerielID,
OrderDetailID and SerielNumbersID.

:

I now have just the two fields in my "resolver" table. But I still do
not
understand what you meant by structuring my data. Is that what I am
doing by
creating this new table?

Thank you,

Teri.

:

Teri

Sounds like you've set it up correctly (but I don't understand about
"a
field to hold the Serial Number". You do have two fields, right?
One
for
the OrderDetailID and one for the SerialNumberID? You don't need to
put the
actual SerialNumber in this "resolver" table, since your
SerialNumber
table
should already have it. You are just using the resolver table to
hold
the
respective row IDs from the other two tables.

First things first -- I was suggesting that you get the data
structure
correct first, as everything else you'll be doing will depend on
that.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

I'm not entirely sure what you mean by "Get the data structured
first. .
.",
but I created another table and called it ValidSerielNumbers. I
put
the
primary key as ValidSeriel, then added OrderDetailID and
SerielNumbersID
to
the table and a field to hold the seriel number. Is this what you
meant?

I went to relationships and related the ValidSerielNumbers table
to
the
OrderDetails table (came up as 1:M) and then to the SerielNumbers
table
(also
came up as 1:M). How am I doing so far?

Thanks again!!!!!

Teri.

:

First thing first, Teri.

To "resolve" a m:m relationship, you have one table that holds
one
side,
a
second table that holds the other side, and a third table that
show
valid
pairs. In your situation, you have an table for OrderDetails, a
table
for
SerialNumbers, and a "resolver" table that holds valid
combinations
of
OrderDetailID and SerialNumberID (the respective IDs from those
two
tables).

Get the data structured first, then work on the queries, etc.

Regards

Jeff Boyce
Microsoft Office/Access MVP


I was thinking it was a M:M relationship. But, because I really
can't
seem
to get a grasp on this relational thing, I don't know how to
set
one of
those
up. When I create yet another table, how do I relate it so
that
it
does
what
I want and would I have to change my query at all?

Thanks Jeff!

Teri.

:

Teri

I'm not sure, but it sounds like you could use the same serial
number
in
more than one order detail. If so, you have a many-to-many
relationship
between those. In Access, you need a third table to resolve
the
m:m.

Regards

Jeff Boyce
Microsoft Office/Access MVP


I'm not sure if the problem I am currently having has to do
with the
relationship or not. I have a subform which includes a
field
for a
seriel
number. The subform draws on a query which in turn draws
from
three
different tables (Orders, Order Details and SerielNumber).
The
relationship
from Orders to Order Details is one-to-many with many on the
Order
Details
table; the relationship from Order Details to SerielNumber
is
one to
many
with the many on the Order Details table. (The SQL version
of
the
query
is
as follows:

SELECT Orders.CustomerID, Orders.EmployeeID,
Orders.OrderDate,
Orders.PurchaseOrderNumber, Orders.ShipDate,
Orders.ShippingMethodID,
Orders.SalesTaxRate, [Order Details].OrderID, [Order
Details].ProductID,
[Order Details].Quantity, [Order Details].UnitPrice, [Order
Details].Price,
[Order Details].Discount, SerielNumber.*
FROM SerielNumber INNER JOIN (Orders INNER JOIN [Order
Details]
ON
Orders.OrderID = [Order Details].OrderID) ON
SerielNumber.SerielNumberID =
[Order Details].SerielNumberID;

When I put a seriel number into the seriel number field of
the
subform
I
get
an error message saying the following: "You cannot add or
change a
record
because a related record is requred in table
'SerielNumber'."
I
need
to
be
able to input a seriel number which is not a unique number
in
any
way
(it
may
be repeated several times in conjunction with several
different
customers
for
a variety of reasons). If I turn off the referential
integrity
I am
able
to
input the seriel number and it will appear in the Order
Details
table
in
the
SerielNumberID field, but it does not appear in my Seriel
Number
table.

 

[Jeff Boyce]

Teri

Take a look at how queries work. You can add as many (related) tables as
you need to, join them together, and select (only) the fields you wish to
display (within practical limits).

Then you design a report, based on the data the query returns.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Once I have my data input, how am I going to pull my query together? I
have
three different tables which are all related. The reports I am going to
have
to pull together are based on a certain product (3 different ones) and the
seriel number for that product. Am I going to pull the Product itself
from
the Order Details table as the ProductID and the record number from the
same
table and then the seriel number from the seriel number table? I really I
am
making sense.

Thank you so much for all your help!!
Teri.

Your reports don't have to show 100% of what you store (as data).

If you wish to prevent duplication (Same record number/serial number
combination), you'll need to index that combination. If you can
legitimately have more than one combination of the same serial number and
record number, you need a "tie-breaker" like date/time or something. If
you
can only have one legitimate combination, don't bother.

And yes, your report can be laid out as you described. When you get your
data set up, create a query to return Serial Number and Record Number.

Check on the HideDuplicates property in the report definition you create.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I will need to create a report in the future which will simply list the
seriel number and all the record numbers associated with that seriel
number
"since the beginning of time" for that seriel number. So I believe I
would
need to go with your second option. But I do need to ask, do I really
need
to add date fields if dates are not going to be an issue in this
report?
The
only date that will be involved is when it is created. It will be set
up
similar to:
Seriel Number Record Number
1133 1
2
3
268
1134 7
6
22
Date Printed: 5/30/2006



:

Yes, that's what I meant. Yes, three fields. You could also get by
with
just the two RowIDs, and use them as a multi-column primary key. If
you
ask
for the 'groups' recommendations on this, brace yourself for a
"religious
war". Some folks swear by only including the key fields from the
related
tables, and using the combination as a unique primary key. Others add
(as
you have) a unique row identifier (?Autonumber, right?).

If you will only ever have one "pair" valid in that resolver table,
you'll
need to add a unique index on the pair of fields (which is one of the
arguments for making the combination the unique primary key). Adding
the
index assures you'll never have more than one set of the same pair of
IDs.

If, on the other hand, you might be tracking changes over time, you'd
need
to add begin and end date fields to the resolver table. Think of it a
little like a student, a class, and an enrollment (student signed up
for
class). Theoretically, the same student could sign up for the same
class
more than once, so you'd need one/two date fields to track WHEN the
sign
up
was.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Sorry, correction, I have three fields in my table: ValidSerielID,
OrderDetailID and SerielNumbersID.

:

I now have just the two fields in my "resolver" table. But I still
do
not
understand what you meant by structuring my data. Is that what I
am
doing by
creating this new table?

Thank you,

Teri.

:

Teri

Sounds like you've set it up correctly (but I don't understand
about
"a
field to hold the Serial Number". You do have two fields, right?
One
for
the OrderDetailID and one for the SerialNumberID? You don't need
to
put the
actual SerialNumber in this "resolver" table, since your
SerialNumber
table
should already have it. You are just using the resolver table to
hold
the
respective row IDs from the other two tables.

First things first -- I was suggesting that you get the data
structure
correct first, as everything else you'll be doing will depend on
that.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

I'm not entirely sure what you mean by "Get the data structured
first. .
.",
but I created another table and called it ValidSerielNumbers.
I
put
the
primary key as ValidSeriel, then added OrderDetailID and
SerielNumbersID
to
the table and a field to hold the seriel number. Is this what
you
meant?

I went to relationships and related the ValidSerielNumbers
table
to
the
OrderDetails table (came up as 1:M) and then to the
SerielNumbers
table
(also
came up as 1:M). How am I doing so far?

Thanks again!!!!!

Teri.

:

First thing first, Teri.

To "resolve" a m:m relationship, you have one table that holds
one
side,
a
second table that holds the other side, and a third table that
show
valid
pairs. In your situation, you have an table for OrderDetails,
a
table
for
SerialNumbers, and a "resolver" table that holds valid
combinations
of
OrderDetailID and SerialNumberID (the respective IDs from
those
two
tables).

Get the data structured first, then work on the queries, etc.

Regards

Jeff Boyce
Microsoft Office/Access MVP


I was thinking it was a M:M relationship. But, because I
really
can't
seem
to get a grasp on this relational thing, I don't know how to
set
one of
those
up. When I create yet another table, how do I relate it so
that
it
does
what
I want and would I have to change my query at all?

Thanks Jeff!

Teri.

:

Teri

I'm not sure, but it sounds like you could use the same
serial
number
in
more than one order detail. If so, you have a many-to-many
relationship
between those. In Access, you need a third table to
resolve
the
m:m.

Regards

Jeff Boyce
Microsoft Office/Access MVP


I'm not sure if the problem I am currently having has to
do
with the
relationship or not. I have a subform which includes a
field
for a
seriel
number. The subform draws on a query which in turn draws
from
three
different tables (Orders, Order Details and
SerielNumber).
The
relationship
from Orders to Order Details is one-to-many with many on
the
Order
Details
table; the relationship from Order Details to
SerielNumber
is
one to
many
with the many on the Order Details table. (The SQL
version
of
the
query
is
as follows:

SELECT Orders.CustomerID, Orders.EmployeeID,
Orders.OrderDate,
Orders.PurchaseOrderNumber, Orders.ShipDate,
Orders.ShippingMethodID,
Orders.SalesTaxRate, [Order Details].OrderID, [Order
Details].ProductID,
[Order Details].Quantity, [Order Details].UnitPrice,
[Order
Details].Price,
[Order Details].Discount, SerielNumber.*
FROM SerielNumber INNER JOIN (Orders INNER JOIN [Order
Details]
ON
Orders.OrderID = [Order Details].OrderID) ON
SerielNumber.SerielNumberID =
[Order Details].SerielNumberID;

When I put a seriel number into the seriel number field
of
the
subform
I
get
an error message saying the following: "You cannot add
or
change a
record
because a related record is requred in table
'SerielNumber'."
I
need
to
be
able to input a seriel number which is not a unique
number
in
any
way
(it
may
be repeated several times in conjunction with several
different
customers
for
a variety of reasons). If I turn off the referential
integrity
I am
able
to
input the seriel number and it will appear in the Order
Details
table
in
the
SerielNumberID field, but it does not appear in my Seriel
Number
table.

 

[Teri]

Jeff,

I'm sorry to be such a pest about this, and I really do appreciate all your
help with it.

My assumption is that I am going to use the OrdersDetail table and the
SerielNumber Table to do my query. My problem with this particular query is
the criteria I need to put in. This really is not a strong point of mine. I
have 4 different items that I will need to do reports on for these seriel
numbers. Do I need to create separate queries for each of them or is there a
way I can do just one query which will ask me which product I am looking for
the seriel and record numbers for? As an example, the different produts that
have seriel numbers are the CCUSB, PCC16L, Box12Z and the VX3IA. If I want
to list all the seriel numbers along with the record numbers for just the
CCUSBs that have been shipped?

Once I have this figured out, I should be good, I have a good handle on
creating reports and forms. I just can't seem to get a grip on the
relationship thing and queries.

Again, my apologies for being such a pest. But I have had no training on
using Access and am trying to teach myself.

Thank you so much,

Teri.

 

[Jeff Boyce]

Teri

Sorry for the absence...

I'll recommend that you "play" with queries a bit. Take a look at
"criteria" (selection criteria) in Access HELP for ideas. If you only want
to know serial numbers for CCUSB product, that would be your criterion.

If you want a single query to be able to look up your information AND allow
you to pick a product, take a look at "parameter query" in Access HELP.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Teri]

Jeff,

I don't think creating the query is really going to be a big deal for me, I
have a very basic handle on how to do it and have created a parameter query
previously. My problem is knowing which table(s) to use for it. Any
suggestions? I need to return the product itself (there are 8 different
ones, so I will be using a parameter query which will ask for the product's
name), the order id and the seriel number. The following is the SQL version
of my query and it is not returning any records:

SELECT [Order Details].ProductID, [Order Details].OrderID, [Valid Seriel
Numbers].SerielNumberID
FROM [Order Details] INNER JOIN [Valid Seriel Numbers] ON [Order
Details].OrderDetailID = [Valid Seriel Numbers].OrderDetailID
WHERE ((([Order Details].ProductID)=[Enter Product]))
ORDER BY [Order Details].ProductID, [Order Details].OrderID;

I have tried changing combinations of tables and have tried using just the
OrderDetails table by itself (it is the only one that has all the "ID" fields
that I need). The only thing I can get it to do is if I remove the [Enter
Product] it will return ALL the products I have that require seriel numbers.

Thanks so much!!

Teri.

 

[Jeff Boyce]

Teri

Your SQL statement uses the WHERE clause:

" WHERE ((([Order Details].ProductID)=[Enter Product]))"

So if you're putting a Product NAME in, is that the same as your ProductID?

Is there a chance one/more of your underlying tables use a "lookup" datatype
field?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

I don't think creating the query is really going to be a big deal for me,
I
have a very basic handle on how to do it and have created a parameter
query
previously. My problem is knowing which table(s) to use for it. Any
suggestions? I need to return the product itself (there are 8 different
ones, so I will be using a parameter query which will ask for the
product's
name), the order id and the seriel number. The following is the SQL
version
of my query and it is not returning any records:

SELECT [Order Details].ProductID, [Order Details].OrderID, [Valid Seriel
Numbers].SerielNumberID
FROM [Order Details] INNER JOIN [Valid Seriel Numbers] ON [Order
Details].OrderDetailID = [Valid Seriel Numbers].OrderDetailID
WHERE ((([Order Details].ProductID)=[Enter Product]))
ORDER BY [Order Details].ProductID, [Order Details].OrderID;

I have tried changing combinations of tables and have tried using just the
OrderDetails table by itself (it is the only one that has all the "ID"
fields
that I need). The only thing I can get it to do is if I remove the [Enter
Product] it will return ALL the products I have that require seriel
numbers.

Thanks so much!!

Teri.

Teri

Sorry for the absence...

I'll recommend that you "play" with queries a bit. Take a look at
"criteria" (selection criteria) in Access HELP for ideas. If you only
want
to know serial numbers for CCUSB product, that would be your criterion.

If you want a single query to be able to look up your information AND
allow
you to pick a product, take a look at "parameter query" in Access HELP.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Teri]

Jeff,

Let me start by thanking you again. You and all the other volunteers are
wonderful just for taking the time to help those of us who have no clue about
what we are doing.

I am no longer working at the place where I need the help for, I took
another position far, far away from there. But I am curious and to continue
to work on this database and others just so I can learn how to use Access
correctly and efficiently.

There is a Product Name field and a ProductID field. I would like to be
able to put in the product name to return all the correct items if I can.
And the closest thing I have to a lookup datatype field is the combo box on
the orders subform where you pick the item that is being ordered.

Thank you!!!!

Teri.

Teri

Your SQL statement uses the WHERE clause:

" WHERE ((([Order Details].ProductID)=[Enter Product]))"

So if you're putting a Product NAME in, is that the same as your ProductID?

Is there a chance one/more of your underlying tables use a "lookup" datatype
field?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

I don't think creating the query is really going to be a big deal for me,
I
have a very basic handle on how to do it and have created a parameter
query
previously. My problem is knowing which table(s) to use for it. Any
suggestions? I need to return the product itself (there are 8 different
ones, so I will be using a parameter query which will ask for the
product's
name), the order id and the seriel number. The following is the SQL
version
of my query and it is not returning any records:

SELECT [Order Details].ProductID, [Order Details].OrderID, [Valid Seriel
Numbers].SerielNumberID
FROM [Order Details] INNER JOIN [Valid Seriel Numbers] ON [Order
Details].OrderDetailID = [Valid Seriel Numbers].OrderDetailID
WHERE ((([Order Details].ProductID)=[Enter Product]))
ORDER BY [Order Details].ProductID, [Order Details].OrderID;

I have tried changing combinations of tables and have tried using just the
OrderDetails table by itself (it is the only one that has all the "ID"
fields
that I need). The only thing I can get it to do is if I remove the [Enter
Product] it will return ALL the products I have that require seriel
numbers.

Thanks so much!!

Teri.

Teri

Sorry for the absence...

I'll recommend that you "play" with queries a bit. Take a look at
"criteria" (selection criteria) in Access HELP for ideas. If you only
want
to know serial numbers for CCUSB product, that would be your criterion.

If you want a single query to be able to look up your information AND
allow
you to pick a product, take a look at "parameter query" in Access HELP.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

I'm sorry to be such a pest about this, and I really do appreciate all
your
help with it.

My assumption is that I am going to use the OrdersDetail table and the
SerielNumber Table to do my query. My problem with this particular
query
is
the criteria I need to put in. This really is not a strong point of
mine.
I
have 4 different items that I will need to do reports on for these
seriel
numbers. Do I need to create separate queries for each of them or is
there a
way I can do just one query which will ask me which product I am
looking
for
the seriel and record numbers for? As an example, the different
produts
that
have seriel numbers are the CCUSB, PCC16L, Box12Z and the VX3IA. If I
want
to list all the seriel numbers along with the record numbers for just
the
CCUSBs that have been shipped?

Once I have this figured out, I should be good, I have a good handle on
creating reports and forms. I just can't seem to get a grip on the
relationship thing and queries.

Again, my apologies for being such a pest. But I have had no training
on
using Access and am trying to teach myself.

Thank you so much,

Teri.


:

Teri

Take a look at how queries work. You can add as many (related) tables
as
you need to, join them together, and select (only) the fields you wish
to
display (within practical limits).

Then you design a report, based on the data the query returns.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Once I have my data input, how am I going to pull my query together?
I
have
three different tables which are all related. The reports I am
going
to
have
to pull together are based on a certain product (3 different ones)
and
the
seriel number for that product. Am I going to pull the Product
itself
from
the Order Details table as the ProductID and the record number from
the
same
table and then the seriel number from the seriel number table? I
really I
am
making sense.

Thank you so much for all your help!!
Teri.

:

Your reports don't have to show 100% of what you store (as data).

If you wish to prevent duplication (Same record number/serial
number
combination), you'll need to index that combination. If you can
legitimately have more than one combination of the same serial
number
and
record number, you need a "tie-breaker" like date/time or
something.
If
you
can only have one legitimate combination, don't bother.

And yes, your report can be laid out as you described. When you
get
your
data set up, create a query to return Serial Number and Record
Number.

Check on the HideDuplicates property in the report definition you
create.

Regards

Jeff Boyce
Microsoft Office/Access MVP


I will need to create a report in the future which will simply
list
the
seriel number and all the record numbers associated with that
seriel
number
"since the beginning of time" for that seriel number. So I
believe
I
would
need to go with your second option. But I do need to ask, do I
really
need
to add date fields if dates are not going to be an issue in this
report?
The
only date that will be involved is when it is created. It will
be
set
up
similar to:
Seriel Number Record Number
1133 1
2
3
268
1134 7
6
22
Date Printed: 5/30/2006



:

Yes, that's what I meant. Yes, three fields. You could also
get
by
with
just the two RowIDs, and use them as a multi-column primary key.
If
you
ask
for the 'groups' recommendations on this, brace yourself for a
"religious
war". Some folks swear by only including the key fields from
the
related
tables, and using the combination as a unique primary key.
Others
add
(as
you have) a unique row identifier (?Autonumber, right?).

If you will only ever have one "pair" valid in that resolver
table,
you'll
need to add a unique index on the pair of fields (which is one
of
the
arguments for making the combination the unique primary key).
Adding
the
index assures you'll never have more than one set of the same
pair
of
IDs.

If, on the other hand, you might be tracking changes over time,
you'd
need
to add begin and end date fields to the resolver table. Think
of
it a
little like a student, a class, and an enrollment (student
signed
up
for
class). Theoretically, the same student could sign up for the
same
class
more than once, so you'd need one/two date fields to track WHEN
the
sign
up
was.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Sorry, correction, I have three fields in my table:
ValidSerielID,
OrderDetailID and SerielNumbersID.

:

I now have just the two fields in my "resolver" table. But I
still
do
not
understand what you meant by structuring my data. Is that
what
I
am
doing by

 

[Jeff Boyce]

Teri

You've been bitten by the bug! You no longer need to do this, but you're
curious about how you would have?!

One approach to letting the user select a "name" (Product, Person, ...), but
having the SQL use the rowID:

Have the SQL (or the query) refer to the form on which the user selects the
name from a combo box (and have the combo box based on a query that returns
the rowID (hidden) and the "name"). You'd use a reference something like:

Forms!YourFormName!YourComboBoxName

to get the rowID.

Regards

Jeff Boyce
Microsoft Office/Access MVP

(and thanks for the kind words. Perhaps you'll have the opportunity to
continue visiting the 'groups and find that you can help others here.)

Jeff,

Let me start by thanking you again. You and all the other volunteers are
wonderful just for taking the time to help those of us who have no clue
about
what we are doing.

I am no longer working at the place where I need the help for, I took
another position far, far away from there. But I am curious and to
continue
to work on this database and others just so I can learn how to use Access
correctly and efficiently.

There is a Product Name field and a ProductID field. I would like to be
able to put in the product name to return all the correct items if I can.
And the closest thing I have to a lookup datatype field is the combo box
on
the orders subform where you pick the item that is being ordered.

Thank you!!!!

Teri.

Teri

Your SQL statement uses the WHERE clause:

" WHERE ((([Order Details].ProductID)=[Enter Product]))"

So if you're putting a Product NAME in, is that the same as your
ProductID?

Is there a chance one/more of your underlying tables use a "lookup"
datatype
field?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

I don't think creating the query is really going to be a big deal for
me,
I
have a very basic handle on how to do it and have created a parameter
query
previously. My problem is knowing which table(s) to use for it. Any
suggestions? I need to return the product itself (there are 8
different
ones, so I will be using a parameter query which will ask for the
product's
name), the order id and the seriel number. The following is the SQL
version
of my query and it is not returning any records:

SELECT [Order Details].ProductID, [Order Details].OrderID, [Valid
Seriel
Numbers].SerielNumberID
FROM [Order Details] INNER JOIN [Valid Seriel Numbers] ON [Order
Details].OrderDetailID = [Valid Seriel Numbers].OrderDetailID
WHERE ((([Order Details].ProductID)=[Enter Product]))
ORDER BY [Order Details].ProductID, [Order Details].OrderID;

I have tried changing combinations of tables and have tried using just
the
OrderDetails table by itself (it is the only one that has all the "ID"
fields
that I need). The only thing I can get it to do is if I remove the
[Enter
Product] it will return ALL the products I have that require seriel
numbers.

Thanks so much!!

Teri.


:

Teri

Sorry for the absence...

I'll recommend that you "play" with queries a bit. Take a look at
"criteria" (selection criteria) in Access HELP for ideas. If you only
want
to know serial numbers for CCUSB product, that would be your
criterion.

If you want a single query to be able to look up your information AND
allow
you to pick a product, take a look at "parameter query" in Access
HELP.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

I'm sorry to be such a pest about this, and I really do appreciate
all
your
help with it.

My assumption is that I am going to use the OrdersDetail table and
the
SerielNumber Table to do my query. My problem with this particular
query
is
the criteria I need to put in. This really is not a strong point of
mine.
I
have 4 different items that I will need to do reports on for these
seriel
numbers. Do I need to create separate queries for each of them or
is
there a
way I can do just one query which will ask me which product I am
looking
for
the seriel and record numbers for? As an example, the different
produts
that
have seriel numbers are the CCUSB, PCC16L, Box12Z and the VX3IA. If
I
want
to list all the seriel numbers along with the record numbers for
just
the
CCUSBs that have been shipped?

Once I have this figured out, I should be good, I have a good handle
on
creating reports and forms. I just can't seem to get a grip on the
relationship thing and queries.

Again, my apologies for being such a pest. But I have had no
training
on
using Access and am trying to teach myself.

Thank you so much,

Teri.


:

Teri

Take a look at how queries work. You can add as many (related)
tables
as
you need to, join them together, and select (only) the fields you
wish
to
display (within practical limits).

Then you design a report, based on the data the query returns.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Once I have my data input, how am I going to pull my query
together?
I
have
three different tables which are all related. The reports I am
going
to
have
to pull together are based on a certain product (3 different
ones)
and
the
seriel number for that product. Am I going to pull the Product
itself
from
the Order Details table as the ProductID and the record number
from
the
same
table and then the seriel number from the seriel number table? I
really I
am
making sense.

Thank you so much for all your help!!
Teri.

:

Your reports don't have to show 100% of what you store (as
data).

If you wish to prevent duplication (Same record number/serial
number
combination), you'll need to index that combination. If you can
legitimately have more than one combination of the same serial
number
and
record number, you need a "tie-breaker" like date/time or
something.
If
you
can only have one legitimate combination, don't bother.

And yes, your report can be laid out as you described. When you
get
your
data set up, create a query to return Serial Number and Record
Number.

Check on the HideDuplicates property in the report definition
you
create.

Regards

Jeff Boyce
Microsoft Office/Access MVP


I will need to create a report in the future which will simply
list
the
seriel number and all the record numbers associated with that
seriel
number
"since the beginning of time" for that seriel number. So I
believe
I
would
need to go with your second option. But I do need to ask, do
I
really
need
to add date fields if dates are not going to be an issue in
this
report?
The
only date that will be involved is when it is created. It
will
be
set
up
similar to:
Seriel Number Record Number
1133 1
2
3
268
1134 7
6
22
Date Printed: 5/30/2006



:

Yes, that's what I meant. Yes, three fields. You could also
get
by
with
just the two RowIDs, and use them as a multi-column primary
key.
If
you
ask
for the 'groups' recommendations on this, brace yourself for
a
"religious
war". Some folks swear by only including the key fields from
the
related
tables, and using the combination as a unique primary key.
Others
add
(as
you have) a unique row identifier (?Autonumber, right?).

If you will only ever have one "pair" valid in that resolver
table,
you'll
need to add a unique index on the pair of fields (which is
one
of
the
arguments for making the combination the unique primary key).
Adding
the
index assures you'll never have more than one set of the same
pair
of
IDs.

If, on the other hand, you might be tracking changes over
time,
you'd
need
to add begin and end date fields to the resolver table.
Think
of
it a
little like a student, a class, and an enrollment (student
signed
up
for
class). Theoretically, the same student could sign up for
the
same
class
more than once, so you'd need one/two date fields to track
WHEN
the
sign
up
was.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Sorry, correction, I have three fields in my table:
ValidSerielID,
OrderDetailID and SerielNumbersID.

:

I now have just the two fields in my "resolver" table.
But I
still
do
not
understand what you meant by structuring my data. Is that
what
I
am
doing by

 

[Teri]

Yes, I guess I have been bitten by the bug. But I am also kind of thinking I
might use the same database in the future for my own use if I start my own
business.

I will try out your suggestion when I get home tonight and see if it works
(if it doesn't I 'm sure it will be something I did wrong and not something
you told me).

Thank you again for your help!

Teri.

Teri

You've been bitten by the bug! You no longer need to do this, but you're
curious about how you would have?!

One approach to letting the user select a "name" (Product, Person, ...), but
having the SQL use the rowID:

Have the SQL (or the query) refer to the form on which the user selects the
name from a combo box (and have the combo box based on a query that returns
the rowID (hidden) and the "name"). You'd use a reference something like:

Forms!YourFormName!YourComboBoxName

to get the rowID.

Regards

Jeff Boyce
Microsoft Office/Access MVP

(and thanks for the kind words. Perhaps you'll have the opportunity to
continue visiting the 'groups and find that you can help others here.)

Jeff,

Let me start by thanking you again. You and all the other volunteers are
wonderful just for taking the time to help those of us who have no clue
about
what we are doing.

I am no longer working at the place where I need the help for, I took
another position far, far away from there. But I am curious and to
continue
to work on this database and others just so I can learn how to use Access
correctly and efficiently.

There is a Product Name field and a ProductID field. I would like to be
able to put in the product name to return all the correct items if I can.
And the closest thing I have to a lookup datatype field is the combo box
on
the orders subform where you pick the item that is being ordered.

Thank you!!!!

Teri.

Teri

Your SQL statement uses the WHERE clause:

" WHERE ((([Order Details].ProductID)=[Enter Product]))"

So if you're putting a Product NAME in, is that the same as your
ProductID?

Is there a chance one/more of your underlying tables use a "lookup"
datatype
field?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

I don't think creating the query is really going to be a big deal for
me,
I
have a very basic handle on how to do it and have created a parameter
query
previously. My problem is knowing which table(s) to use for it. Any
suggestions? I need to return the product itself (there are 8
different
ones, so I will be using a parameter query which will ask for the
product's
name), the order id and the seriel number. The following is the SQL
version
of my query and it is not returning any records:

SELECT [Order Details].ProductID, [Order Details].OrderID, [Valid
Seriel
Numbers].SerielNumberID
FROM [Order Details] INNER JOIN [Valid Seriel Numbers] ON [Order
Details].OrderDetailID = [Valid Seriel Numbers].OrderDetailID
WHERE ((([Order Details].ProductID)=[Enter Product]))
ORDER BY [Order Details].ProductID, [Order Details].OrderID;

I have tried changing combinations of tables and have tried using just
the
OrderDetails table by itself (it is the only one that has all the "ID"
fields
that I need). The only thing I can get it to do is if I remove the
[Enter
Product] it will return ALL the products I have that require seriel
numbers.

Thanks so much!!

Teri.


:

Teri

Sorry for the absence...

I'll recommend that you "play" with queries a bit. Take a look at
"criteria" (selection criteria) in Access HELP for ideas. If you only
want
to know serial numbers for CCUSB product, that would be your
criterion.

If you want a single query to be able to look up your information AND
allow
you to pick a product, take a look at "parameter query" in Access
HELP.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

I'm sorry to be such a pest about this, and I really do appreciate
all
your
help with it.

My assumption is that I am going to use the OrdersDetail table and
the
SerielNumber Table to do my query. My problem with this particular
query
is
the criteria I need to put in. This really is not a strong point of
mine.
I
have 4 different items that I will need to do reports on for these
seriel
numbers. Do I need to create separate queries for each of them or
is
there a
way I can do just one query which will ask me which product I am
looking
for
the seriel and record numbers for? As an example, the different
produts
that
have seriel numbers are the CCUSB, PCC16L, Box12Z and the VX3IA. If
I
want
to list all the seriel numbers along with the record numbers for
just
the
CCUSBs that have been shipped?

Once I have this figured out, I should be good, I have a good handle
on
creating reports and forms. I just can't seem to get a grip on the
relationship thing and queries.

Again, my apologies for being such a pest. But I have had no
training
on
using Access and am trying to teach myself.

Thank you so much,

Teri.


:

Teri

Take a look at how queries work. You can add as many (related)
tables
as
you need to, join them together, and select (only) the fields you
wish
to
display (within practical limits).

Then you design a report, based on the data the query returns.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Once I have my data input, how am I going to pull my query
together?
I
have
three different tables which are all related. The reports I am
going
to
have
to pull together are based on a certain product (3 different
ones)
and
the
seriel number for that product. Am I going to pull the Product
itself
from
the Order Details table as the ProductID and the record number
from
the
same
table and then the seriel number from the seriel number table? I
really I
am
making sense.

Thank you so much for all your help!!
Teri.

:

Your reports don't have to show 100% of what you store (as
data).

If you wish to prevent duplication (Same record number/serial
number
combination), you'll need to index that combination. If you can
legitimately have more than one combination of the same serial
number
and
record number, you need a "tie-breaker" like date/time or
something.
If
you
can only have one legitimate combination, don't bother.

And yes, your report can be laid out as you described. When you
get
your
data set up, create a query to return Serial Number and Record
Number.

Check on the HideDuplicates property in the report definition
you
create.

Regards

Jeff Boyce
Microsoft Office/Access MVP


I will need to create a report in the future which will simply
list
the
seriel number and all the record numbers associated with that
seriel
number
"since the beginning of time" for that seriel number. So I
believe
I
would
need to go with your second option. But I do need to ask, do
I
really
need
to add date fields if dates are not going to be an issue in
this
report?
The
only date that will be involved is when it is created. It
will
be
set
up
similar to:
Seriel Number Record Number
1133 1
2
3
268
1134 7
6
22
Date Printed: 5/30/2006