Simple Relationships

[Guest]

Life savers...greetings once again from South Texas. I have two
tables...both w/an "Envelope #" field. Each day, a sequential number per
envelope is issued out. For example...Customer brings in...say 10 Vehicle
Titles. All 10 Titles are placed in the first (#1) envelope. Next
customer's envelope is #2, etc. The next day different customers show up,
but we start again at #1. Several days later the customers return to pick up
their validated titles.

Even though the envelope #'s are not unique identifiers because they repeat
themselves everyday...I would like to relate these records so that I could
refer to a specific date then to the specific envelope number to post the
results. Is this possible? Please Help!!!

John

 

[Steve]

Assuming that everything any customer has, it is ALWAYS all placed together
in the same envelope.

Consider using just the following table:
TblEnvelope
EnvelopeID
EnvelopeDate
EnvelopeNumber Make Integer data type
CustomerLastName
CustomerFirstName
CustomerMiddleInitial
IsValidated (Yes/No Field)

To enter data in this table, create a query based on TblEnvelope. Set the
sort on EnvelopeDate ascending and the sort on CustomerLastName ascending.
Make this query the recordsource of your form. Make EnvelopeDate the first
field on your form to indicate to the user he needs to enter the
EnvelopeDate first. Put the following code in the AfterUpdate event of
EnvelopeDate:
If DCount("*","TblEnvelope","[EnvelopeDate] = " & Me!EnvelopeDate) = 0 Then
Me!EnvelopeNumber = 1
Else
Me!EnvelopeNumber =
DMax("[EnvelopeNumber]","TblEnvelope","[EnvelopeDate] = " & Me!EnvelopeDate)
+ 1
End If

Your form will display the reords previously entered in chronological order
and for each date display customer last names in alohabetical order.

Assuming you want to post results of only the envelopes that have been
validated, create a query based on TblEnvelope with the following fields:
EnvelopeDate
EnvelopeNumber
Customer:[CustomerLastName] & ", " & [CustomerFirstName] & " " &
[CustomerMiddleInitial]
IsValidated

Set the criteria for EnvelopeDate to:
Between [Enter Start Date] And [Enter End Date] Or ([Enter Start Date] Is
Null And [Enter End Date] Is Null)

Set the criteria for IsValidated to:
True

You can create a report based on this query to display your results. When
you open your report, a dialog will first appear asking for the envelope
date you want the records on the report to start with. Then you will get
another dialog asking for the envelope date you want the records on the
report to end with. If you answer both dialogs you will get only the records
between these dates. Leave both dialogs blank and you will get all the
records in TblEnvekope.

 

[Jeff Boyce]

I'm not clear on what you are trying to do in Access.

If a CustomerTitle can have a DateDelivered and an Envelope# associated with
it, it seems like you could create a query to look for all the records with
DateDelivered = 7/20/2007.

What are you trying to end up with?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Guest]

Upon issuing numerous envelope numbers and having to wait several days later
to return these I would like to refer to a specific date then the specific
envelope number to post results. A subform would be nice, but I'm not sure
how to relate these fields when the envelope numbers and dates are not unique.

 

[Steve]

Did you look at my response?

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Guest]

Yes I did. It was a bit lengthy. I'm saving your response for further
viewing. Thanks Steve.

 

[Steve]

It should do what you want!

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[John W. Vinson]

A subform would be nice, but I'm not sure
how to relate these fields when the envelope numbers and dates are not unique.

Just relate on both fields. A Join between two tables can be based on one
field, or on two, or even on ten (though I'd probably never do so).

Make the Master and Child Link Field of the subform

[EnvelopeDate];[EnvelopeNo]

to select just one envelope on one date. You will probably want to put a
unique Index on that combination of fields, or even make those two fields a
joint Primary Key.

John W. Vinson [MVP]

 

[Guest]

yyyyyyyyyyyyyyyyyyyy

 

[Guest]

Steve,

I'm trying your method. Please excuse my stupidity, but is this all
one code? Are there any breaks? Where does the code end? Should I type the
code exactly the way you wrote it including the spaces? Thanks Steve.

John

Assuming that everything any customer has, it is ALWAYS all placed together
in the same envelope.

Consider using just the following table:
TblEnvelope
EnvelopeID
EnvelopeDate
EnvelopeNumber Make Integer data type
CustomerLastName
CustomerFirstName
CustomerMiddleInitial
IsValidated (Yes/No Field)

To enter data in this table, create a query based on TblEnvelope. Set the
sort on EnvelopeDate ascending and the sort on CustomerLastName ascending.
Make this query the recordsource of your form. Make EnvelopeDate the first
field on your form to indicate to the user he needs to enter the
EnvelopeDate first. Put the following code in the AfterUpdate event of
EnvelopeDate:
If DCount("*","TblEnvelope","[EnvelopeDate] = " & Me!EnvelopeDate) = 0 Then
Me!EnvelopeNumber = 1
Else
Me!EnvelopeNumber =
DMax("[EnvelopeNumber]","TblEnvelope","[EnvelopeDate] = " & Me!EnvelopeDate)
+ 1
End If

Your form will display the reords previously entered in chronological order
and for each date display customer last names in alohabetical order.

Assuming you want to post results of only the envelopes that have been
validated, create a query based on TblEnvelope with the following fields:
EnvelopeDate
EnvelopeNumber
Customer:[CustomerLastName] & ", " & [CustomerFirstName] & " " &
[CustomerMiddleInitial]
IsValidated

Set the criteria for EnvelopeDate to:
Between [Enter Start Date] And [Enter End Date] Or ([Enter Start Date] Is
Null And [Enter End Date] Is Null)

Set the criteria for IsValidated to:
True

You can create a report based on this query to display your results. When
you open your report, a dialog will first appear asking for the envelope
date you want the records on the report to start with. Then you will get
another dialog asking for the envelope date you want the records on the
report to end with. If you answer both dialogs you will get only the records
between these dates. Leave both dialogs blank and you will get all the
records in TblEnvekope.

Life savers...greetings once again from South Texas. I have two
tables...both w/an "Envelope #" field. Each day, a sequential number per
envelope is issued out. For example...Customer brings in...say 10 Vehicle
Titles. All 10 Titles are placed in the first (#1) envelope. Next
customer's envelope is #2, etc. The next day different customers show up,
but we start again at #1. Several days later the customers return to pick
up
their validated titles.

Even though the envelope #'s are not unique identifiers because they
repeat
themselves everyday...I would like to relate these records so that I could
refer to a specific date then to the specific envelope number to post the
results. Is this possible? Please Help!!!

John

 

[Steve]

Hi John,

This is the code:

If DCount("*","TblEnvelope","[EnvelopeDate] = " & Me!EnvelopeDate) = 0 Then
Me!EnvelopeNumber = 1
Else
Me!EnvelopeNumber =
DMax("[EnvelopeNumber]","TblEnvelope","[EnvelopeDate] = " & Me!EnvelopeDate)
+ 1
End If

Copy it as is. Go to your form in design view, select EnvelopeDate textbox,
open properties, go to the event tab, click on the three doys at the right
side of the AfterUpdate evevt box, in the dialog that appears click Code
Builder, then click OK. You will see two lines of new code that look like:
Private Sub EnvelopeDate_AfterUpdate()

End Sub
Paste the code between these two lines of code.

Steve,

I'm trying your method. Please excuse my stupidity, but is this all
one code? Are there any breaks? Where does the code end? Should I type
the
code exactly the way you wrote it including the spaces? Thanks Steve.

John

Assuming that everything any customer has, it is ALWAYS all placed
together
in the same envelope.

Consider using just the following table:
TblEnvelope
EnvelopeID
EnvelopeDate
EnvelopeNumber Make Integer data type
CustomerLastName
CustomerFirstName
CustomerMiddleInitial
IsValidated (Yes/No Field)

To enter data in this table, create a query based on TblEnvelope. Set the
sort on EnvelopeDate ascending and the sort on CustomerLastName
ascending.
Make this query the recordsource of your form. Make EnvelopeDate the
first
field on your form to indicate to the user he needs to enter the
EnvelopeDate first. Put the following code in the AfterUpdate event of
EnvelopeDate:
If DCount("*","TblEnvelope","[EnvelopeDate] = " & Me!EnvelopeDate) = 0
Then
Me!EnvelopeNumber = 1
Else
Me!EnvelopeNumber =
DMax("[EnvelopeNumber]","TblEnvelope","[EnvelopeDate] = " &
Me!EnvelopeDate)
+ 1
End If

Your form will display the reords previously entered in chronological
order
and for each date display customer last names in alohabetical order.

Assuming you want to post results of only the envelopes that have been
validated, create a query based on TblEnvelope with the following fields:
EnvelopeDate
EnvelopeNumber
Customer:[CustomerLastName] & ", " & [CustomerFirstName] & " " &
[CustomerMiddleInitial]
IsValidated

Set the criteria for EnvelopeDate to:
Between [Enter Start Date] And [Enter End Date] Or ([Enter Start Date] Is
Null And [Enter End Date] Is Null)

Set the criteria for IsValidated to:
True

You can create a report based on this query to display your results. When
you open your report, a dialog will first appear asking for the envelope
date you want the records on the report to start with. Then you will get
another dialog asking for the envelope date you want the records on the
report to end with. If you answer both dialogs you will get only the
records
between these dates. Leave both dialogs blank and you will get all the
records in TblEnvekope.

Life savers...greetings once again from South Texas. I have two
tables...both w/an "Envelope #" field. Each day, a sequential number
per
envelope is issued out. For example...Customer brings in...say 10
Vehicle
Titles. All 10 Titles are placed in the first (#1) envelope. Next
customer's envelope is #2, etc. The next day different customers show
up,
but we start again at #1. Several days later the customers return to
pick
up
their validated titles.

Even though the envelope #'s are not unique identifiers because they
repeat
themselves everyday...I would like to relate these records so that I
could
refer to a specific date then to the specific envelope number to post
the
results. Is this possible? Please Help!!!

John