buidling relationships and queries

[danthrom]

I have four tables:
Table A: client information ex. John Doe (DOEJ); Kate Bell (BELLK)
Table B: client file information ex. John Doe’s Business One (DOEJ 1001),
John Doe’s Business Two (DOEJ 1002); Kate Bell Business One (BELLK 1001),
Kate Bell’s Lease (BELLK 2004).
Table C: storage information ex. Box 10 was stored on 6-1-06, Box 11 was
stored on 7-1-06
Table D: extended information for both boxes and files ex. Box 10 has DOEJ
1001, BELLK 2004, Box 11 also has BELLK 2004. Inside Box 10, file BELLK 2004
is correspondence and notes, inside Box 11, file BELLK 2004 is documents and
final drafts.

I have set it up that the alias (DOEJ) is the primary key in Table A, and an
autonumber is primary key in table B, Table C’s primary key is the box
number. But I am having problems joining Table D to Table B. So far I have
a one-to-many enforced join from Table B’s autonumber to Table D in a number
field. This works for building a relationship. But how can I set up a
query/form which makes selecting a file simple? I was thinking that through
a query I can build a combo box displaying the autonumber as a combination of
client alias and file number ex. DOEJ-1001.

Is there a way to accomplish this? Or am I approaching this wrong?

Thanks for the help.

 

[Graham Mandeno]

Your design seems to be fairly sound. If I understand you correctly, you
want to set up a form which will filter all the Table D records based on
values in the related tables - for example, ClientName (Table A),
ProjectName (Table B), StorageDate (Table C).

The first two can be done with "cascading combo boxes". You have one combo
to select a client (I'll call it cboFltrClient). You have another to select
a project (let's call it cboFltrProject). The RowSource of the second one
is filtered by the selection in the first, so that it looks something like
this:

Select ProjectID, ProjectName from TableB
where ClientID=[Forms]![YourFormName]![cboFltrClient]

In the AfterUpdate of the first, you should requery the second and remove
any preselection:

Public Sub cboFltrClient_AfterUpdate()
cboFltrProject.Requery
cboFltrProject = Null
End Sub

For the storage dates, you just need two textboxes formatted as dates
(txtFltrStartDate and txtFltrEndDate).

Note that all these controls should be unbound.

You can then write some code behind a command button to construct a filter
string. For example:

Const cFmtDate = "\#mm\/dd\/yyyy\#
Dim sFltr as string
If not IsNull (cboFltrProject) then
sFltr = "[ProjectID]=" & cboFltrProject
ElseIf not IsNull (cboFltrClient) then
sFltr = "[ClientID]='" & cboFltrClient & "'"
End If
If not IsNull (txtFltrStartDate) then
If Len(sFltr) <> 0 then sFltr = sFltr & " and "
sFltr = sFltr & "[StorageDate]>=" & Format(txtFltrStartDate,
cFmtDate)
End If
If not IsNull (txtFltrEndDate) then
If Len(sFltr) <> 0 then sFltr = sFltr & " and "
sFltr = sFltr & "[StorageDate]<" & Format(txtFltrEndDate+1,
cFmtDate)
End If
' Now you can apply the filter string to your form, or use it to open a
report
If Len(sFltr) = 0 then
Me.FilterOn = False
Else
Me.Filter = sFltr
Me.FilterOn = True
End If

You should create a query based on all four joined tables, which includes
all the fields you are using to filter, as well as any fields you wish to
display in your form/report. Base your form/report on this query.

 

[danthrom]

thanks for the help, I've tried the cascading combo boxes before, the problem
came with subforms. I know I have to redirect the query to then go through
the main form, but for some reason it wasn't working. I input this expression

SELECT [MatterID],[Matter] FROM [tbl Matters] WHERE [Client Alias]
=[Forms]![MainForm]![Subform]![cboFltrClient]

Thanks,

danthrom

Your design seems to be fairly sound. If I understand you correctly, you
want to set up a form which will filter all the Table D records based on
values in the related tables - for example, ClientName (Table A),
ProjectName (Table B), StorageDate (Table C).

The first two can be done with "cascading combo boxes". You have one combo
to select a client (I'll call it cboFltrClient). You have another to select
a project (let's call it cboFltrProject). The RowSource of the second one
is filtered by the selection in the first, so that it looks something like
this:

Select ProjectID, ProjectName from TableB
where ClientID=[Forms]![YourFormName]![cboFltrClient]

In the AfterUpdate of the first, you should requery the second and remove
any preselection:

Public Sub cboFltrClient_AfterUpdate()
cboFltrProject.Requery
cboFltrProject = Null
End Sub

For the storage dates, you just need two textboxes formatted as dates
(txtFltrStartDate and txtFltrEndDate).

Note that all these controls should be unbound.

You can then write some code behind a command button to construct a filter
string. For example:

Const cFmtDate = "\#mm\/dd\/yyyy\#
Dim sFltr as string
If not IsNull (cboFltrProject) then
sFltr = "[ProjectID]=" & cboFltrProject
ElseIf not IsNull (cboFltrClient) then
sFltr = "[ClientID]='" & cboFltrClient & "'"
End If
If not IsNull (txtFltrStartDate) then
If Len(sFltr) <> 0 then sFltr = sFltr & " and "
sFltr = sFltr & "[StorageDate]>=" & Format(txtFltrStartDate,
cFmtDate)
End If
If not IsNull (txtFltrEndDate) then
If Len(sFltr) <> 0 then sFltr = sFltr & " and "
sFltr = sFltr & "[StorageDate]<" & Format(txtFltrEndDate+1,
cFmtDate)
End If
' Now you can apply the filter string to your form, or use it to open a
report
If Len(sFltr) = 0 then
Me.FilterOn = False
Else
Me.Filter = sFltr
Me.FilterOn = True
End If

You should create a query based on all four joined tables, which includes
all the fields you are using to filter, as well as any fields you wish to
display in your form/report. Base your form/report on this query.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have four tables:
Table A: client information ex. John Doe (DOEJ); Kate Bell (BELLK)
Table B: client file information ex. John Doe's Business One (DOEJ 1001),
John Doe's Business Two (DOEJ 1002); Kate Bell Business One (BELLK 1001),
Kate Bell's Lease (BELLK 2004).
Table C: storage information ex. Box 10 was stored on 6-1-06, Box 11 was
stored on 7-1-06
Table D: extended information for both boxes and files ex. Box 10 has DOEJ
1001, BELLK 2004, Box 11 also has BELLK 2004. Inside Box 10, file BELLK
2004
is correspondence and notes, inside Box 11, file BELLK 2004 is documents
and
final drafts.

I have set it up that the alias (DOEJ) is the primary key in Table A, and
an
autonumber is primary key in table B, Table C's primary key is the box
number. But I am having problems joining Table D to Table B. So far I
have
a one-to-many enforced join from Table B's autonumber to Table D in a
number
field. This works for building a relationship. But how can I set up a
query/form which makes selecting a file simple? I was thinking that
through
a query I can build a combo box displaying the autonumber as a combination
of
client alias and file number ex. DOEJ-1001.

Is there a way to accomplish this? Or am I approaching this wrong?

Thanks for the help.

 

[Graham Mandeno]

Subforms can be a real pain with cascading combos, partly because of timing
issues with the levels of indirection, and partly because a subform can be
used on more than one main form and you don't know which one to reference it
through.

So, for subforms (in fact, in general), I prefer a different approach.
Instead of requerying the second combo in the AfterUpdate of the first,
respecify its filtered rowsource:

cboFltrMatterID.RowSource = "SELECT [MatterID],[Matter] " _
& "FROM [tbl Matters] WHERE [Client Alias] = '" & cboFltrClient & "'"
cboFltrMatter = Null

Note that you need the single quotes around the cboFltrClient value because
[Client Alias] is a text field.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

thanks for the help, I've tried the cascading combo boxes before, the
problem
came with subforms. I know I have to redirect the query to then go
through
the main form, but for some reason it wasn't working. I input this
expression

SELECT [MatterID],[Matter] FROM [tbl Matters] WHERE [Client Alias]
=[Forms]![MainForm]![Subform]![cboFltrClient]

Thanks,

danthrom

Your design seems to be fairly sound. If I understand you correctly, you
want to set up a form which will filter all the Table D records based on
values in the related tables - for example, ClientName (Table A),
ProjectName (Table B), StorageDate (Table C).

The first two can be done with "cascading combo boxes". You have one
combo
to select a client (I'll call it cboFltrClient). You have another to
select
a project (let's call it cboFltrProject). The RowSource of the second
one
is filtered by the selection in the first, so that it looks something
like
this:

Select ProjectID, ProjectName from TableB
where ClientID=[Forms]![YourFormName]![cboFltrClient]

In the AfterUpdate of the first, you should requery the second and remove
any preselection:

Public Sub cboFltrClient_AfterUpdate()
cboFltrProject.Requery
cboFltrProject = Null
End Sub

For the storage dates, you just need two textboxes formatted as dates
(txtFltrStartDate and txtFltrEndDate).

Note that all these controls should be unbound.

You can then write some code behind a command button to construct a
filter
string. For example:

Const cFmtDate = "\#mm\/dd\/yyyy\#
Dim sFltr as string
If not IsNull (cboFltrProject) then
sFltr = "[ProjectID]=" & cboFltrProject
ElseIf not IsNull (cboFltrClient) then
sFltr = "[ClientID]='" & cboFltrClient & "'"
End If
If not IsNull (txtFltrStartDate) then
If Len(sFltr) <> 0 then sFltr = sFltr & " and "
sFltr = sFltr & "[StorageDate]>=" & Format(txtFltrStartDate,
cFmtDate)
End If
If not IsNull (txtFltrEndDate) then
If Len(sFltr) <> 0 then sFltr = sFltr & " and "
sFltr = sFltr & "[StorageDate]<" & Format(txtFltrEndDate+1,
cFmtDate)
End If
' Now you can apply the filter string to your form, or use it to open
a
report
If Len(sFltr) = 0 then
Me.FilterOn = False
Else
Me.Filter = sFltr
Me.FilterOn = True
End If

You should create a query based on all four joined tables, which includes
all the fields you are using to filter, as well as any fields you wish to
display in your form/report. Base your form/report on this query.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have four tables:
Table A: client information ex. John Doe (DOEJ); Kate Bell (BELLK)
Table B: client file information ex. John Doe's Business One (DOEJ
1001),
John Doe's Business Two (DOEJ 1002); Kate Bell Business One (BELLK
1001),
Kate Bell's Lease (BELLK 2004).
Table C: storage information ex. Box 10 was stored on 6-1-06, Box 11
was
stored on 7-1-06
Table D: extended information for both boxes and files ex. Box 10 has
DOEJ
1001, BELLK 2004, Box 11 also has BELLK 2004. Inside Box 10, file
BELLK
2004
is correspondence and notes, inside Box 11, file BELLK 2004 is
documents
and
final drafts.

I have set it up that the alias (DOEJ) is the primary key in Table A,
and
an
autonumber is primary key in table B, Table C's primary key is the box
number. But I am having problems joining Table D to Table B. So far I
have
a one-to-many enforced join from Table B's autonumber to Table D in a
number
field. This works for building a relationship. But how can I set up a
query/form which makes selecting a file simple? I was thinking that
through
a query I can build a combo box displaying the autonumber as a
combination
of
client alias and file number ex. DOEJ-1001.

Is there a way to accomplish this? Or am I approaching this wrong?

Thanks for the help.