Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

buidling relationships and queries

Discussion in 'Information Technology' started by danthrom, Jul 28, 2006.

  1. danthrom

    danthrom
    Expand Collapse
    Guest

    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.
     
  2. Loading...

    Similar Threads Forum Date
    I'm open-minded about romantic relationships... how do I explain to family? Love & Marriage Aug 22, 2015
    Need advice...Sikhi in relationships Love & Marriage Nov 17, 2011
    Peer Pressure, Western Traits and Relationships Announcements Aug 23, 2010
    Peer pressure, western traits and relationships Sikh Youth Aug 23, 2010
    What is the Commitment in Relationships? Relationships Jan 24, 2010

  3. Graham Mandeno

    Graham Mandeno
    Expand Collapse
    Guest

    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


    "danthrom" <danthrom@discussions.microsoft.com> wrote in message
    news:3EA5C869-8B31-4A83-82F9-509848F70E34@microsoft.com...
    >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.
    >
     
  4. danthrom

    danthrom
    Expand Collapse
    Guest

    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



    "Graham Mandeno" wrote:

    > 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
    >
    >
    > "danthrom" <danthrom@discussions.microsoft.com> wrote in message
    > news:3EA5C869-8B31-4A83-82F9-509848F70E34@microsoft.com...
    > >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.
    > >

    >
    >
    >
     
  5. Graham Mandeno

    Graham Mandeno
    Expand Collapse
    Guest

    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

    "danthrom" <danthrom@discussions.microsoft.com> wrote in message
    news:A9A54E73-6D4F-4CB4-92F5-554BE010E055@microsoft.com...
    > 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
    >
    >
    >
    > "Graham Mandeno" wrote:
    >
    >> 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
    >>
    >>
    >> "danthrom" <danthrom@discussions.microsoft.com> wrote in message
    >> news:3EA5C869-8B31-4A83-82F9-509848F70E34@microsoft.com...
    >> >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.
    >> >

    >>
    >>
    >>
     

Share This Page