T
Teri
Below is the background for an issue I had not too long ago that was never
resolved. I am hoping someone has the time to look at this and possibly help
me out. I am at my wits end with this! I really need to have this portion
completed by this Friday and am not getting anywhere on my own.
Thank you!!
Teri.
MVP,
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.
MVP wrote:
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
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.
MVP wrote:
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
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:
MVP wrote:
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
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.
MVP wrote:
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
Microsoft Office/Access MVP
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.
MVP wrote:
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
Microsoft Office/Access MVP
I was thinking it was a M:M relationship. But, because I really can'tseem
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 MVP!
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.
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
resolved. I am hoping someone has the time to look at this and possibly help
me out. I am at my wits end with this! I really need to have this portion
completed by this Friday and am not getting anywhere on my own.
Thank you!!
Teri.
MVP,
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.
MVP wrote:
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
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.
MVP wrote:
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
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:
MVP wrote:
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
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.
MVP wrote:
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
Microsoft Office/Access MVP
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.
MVP wrote:
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
Microsoft Office/Access MVP
I was thinking it was a M:M relationship. But, because I really can'tseem
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 MVP!
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.
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