Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Queries Across Tables w/ no Relationship (Access 2000)

    Ok....This database is quickly becoming a doozy. A master job list is generated by our accounting software. The list is then converted and used as a comparison to the job list we are maintaining. We are trying to generate 2 different queries: one shows which jobs have an active NOI, but no NOT; the other shows jobs with no NOI on file (and obviously, no NOT either). The problem lies in that jobs with no NOI do not yet appear on one table (as it grows via user entry). The master job list is un-editable. I'm having trouble with this, but I know my main man HansV can fix it. Of course, anybody who'd like to take a look and offer any suggestions would have my sincere gratitude. The file is attached, and I'm sure there are quite a few unnecessary queries now.

    Much obliged,

    B. Drake

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Queries Across Tables w/ no Relationship (Access 2000)

    Can you please explain which tables and which fields are involved?

  3. #3
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Queries Across Tables w/ no Relationship (Access 2000)

    Ok...Job Number Query shows all the data users enter via the Add/Update form. tblMasterJobList is generated by our accounting software, and is only made into a table to convert the 'job' field from text to a number (possibly problem #1). qryExcav, qryPav, and qryUti are used to compare the master job list to user-entered jobs with currently active NOIs, and SHOULD display the results under tblJobsHaveNOI (problem #2). qryJobsWithoutNOI SHOULD display the remaining jobs in the master job list that don't appear in Job Number Query (aka - jobs without an NOI filed) (problem #3). Seems simple, as I'm sure you can show me, but I've probably managed to make it more complicated than it needs to be (and hence, have made it unfunctional).

    You are the greatest my good man!

    B. Drake

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Queries Across Tables w/ no Relationship (Access 2000)

    You'll have to provide MUCH more explanation. For example, why are qryPavJobsHaveNOI etc. update queries? What is their purpose?

  5. #5
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Queries Across Tables w/ no Relationship (Access 2000)

    The update queries are used to continually update tblJobsHaveNOI. The problem lies in that some reference #s will cover up to 3 job numbers (1 for excav, 1 for pav, 1 for uti), while there are a few circumstances in which the same job # will have multiple reference #s. So - the grand scheme - another administrator will add a new job number to the master database (which is what feeds tblMasterJobList). The project manager is supposed to file an NOI for that job. However, that doesn't always happen. So, the first goal is to be able to compare our master job list to the jobs in Job Number Query to determine which ones have NOI's, and which ones have nothing.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Queries Across Tables w/ no Relationship (Access 2000)

    I don't even begin to understand these update queries.

    qryExcavJobsHaveNOI won't work, because it attempts to set a number field to a text value.
    qryMasterJobList, qryPavJobsHaveNOI and qryUtiJobsHaveNOI won't work because they don't set anything.
    Even if they worked, none of them would update tblJobsHaveNOI.

    <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  7. #7
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Queries Across Tables w/ no Relationship (Access 2000)

    In working with reverendleo, one of the basic things we need to do is be able to take three queries(tblExcavJobs, tblPavJobs, tblUtilityJobs) and make one query out of this. All three queries have the same three fields(Job#, RefNum, JobName). How might we go about this?

    Jackal

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Queries Across Tables w/ no Relationship (Access 2000)

    The names tblExcavJobs, tblPavJobs, tblUtilityJobs seem to refer to tables, not queries. There are no tables with those names in the database attached to the first post in this thread, nor queries.

    I remain <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  9. #9
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Queries Across Tables w/ no Relationship (Access 2000)

    Good morning sir. Upon a night's rest I think we've decided to scrap the non-functional parts of the operation and try again. So...all that we have left are the forms and Job Number Query. Our goal is still the same, but maybe a new approach will be easier and more successful. The first task would be to combine 3 different columns in Job Number Query (Exc. Job #, Pav. Job #, and Uti. Job #) into one, longer column with the possibility of encountering duplicates, which should all appear in the list. We would then need those entries to compare against a master job list (not currently in the database), using 2 additional columns from J.N.Q. (NOIDate and NOTDate [the latter of which might be null]).
    Maybe this at least clarifies our goal? You are the Omnicient Guru of Access as far as we're concerned over here.

    Thanks for all the help thus far!

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Queries Across Tables w/ no Relationship (Access 2000)

    I am certainly not omniscient, as my continual confusion in this thread proves. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    It is possible to create a query to merge the three job number fields into one (with some repeating RefNums):

    SELECT RefNum, [ExcavJob #] As JobNumber, JobName FROM [Ref#vsJob#] WHERE [ExcavJob #] Is Not Null
    UNION
    SELECT RefNum, [PavJobNum], JobName FROM [Ref#vsJob#] WHERE [PavJobNum] Is Not Null
    UNION
    SELECT RefNum, [UtiJobNum], JobName FROM [Ref#vsJob#] WHERE [UtiJobNum] Is Not Null;

    This is a union query, it can only be created in SQL view, not in design view. Save this query as qryRefJob. Next, you can create a query that joins this query with Ref#vsPermit#andNOT on RefNum:

    SELECT qryRefJob.RefNum, qryRefJob.JobNumber, qryRefJob.JobName, [Ref#vsPermit#andNOT].PermitNum, [Ref#vsPermit#andNOT].FiledOnDate, [Ref#vsPermit#andNOT].PayTraceNum, [Ref#vsPermit#andNOT].[NOTYes/No], [Ref#vsPermit#andNOT].NOTDate, [Ref#vsPermit#andNOT].[SWPPPBookYes/No]
    FROM qryRefJob INNER JOIN [Ref#vsPermit#andNOT] ON qryRefJob.RefNum = [Ref#vsPermit#andNOT].RefNum;

    This is a standard query, it can be created in design view (or in SQL view). Save it as (for example) qryJobNumbers.

  11. #11
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Queries Across Tables w/ no Relationship (Access 2000)

    You are pure genius, sir.

    B. Drake

  12. #12
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Queries Across Tables w/ no Relationship (Access 2000)

    OK...things are coming along quite nicely now. A simple step now that seems to be giving me more trouble than it should. I want rptJobsNoNOI to prompt the user to pick a Project Manager, and then generate the report filtered by the user's input. A combo box would be ideal, since the names have to be precise. The choices in the hypothetical combo box are in qryProjMan, but right now I've got a prompt upon opening the report. Doesn't seem to work though.
    By the way, we would have never figured that SQL out, but it's exactly what we needed.

    You da man!

    B. Drake

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Queries Across Tables w/ no Relationship (Access 2000)

    You do this by creating a form with a combo box that has qryProjMan as row source, and a command button that opens the report with a where-condition. You don't put the code to filter the report in the report itself.

    See the attached version (note: I deleted the other forms since they are not relevant to this problem)

    This is the On Click code for the command button:

    Private Sub cmdOpenReport_Click()
    On Error GoTo ErrHandler
    DoCmd.OpenReport "rptJobsNoNOI", acPreview, , _
    "Project_Manager=" & Chr(34) & Me.cboProjMan & Chr(34)

    ErrHandler:
    If Not Err = 2501 Then
    MsgBox Err.Description, vbExclamation
    End If
    End Sub

    (If the report is canceled, error 2501 occurs; we don't want an error message in that situation)

  14. #14
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Queries Across Tables w/ no Relationship (Access 2000)

    You're a true hero.

  15. #15
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries Across Tables w/ no Relationship (Access 2000)

    What happened?? <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45>

    Our beautiful frmSeachNOIs which you helped us code in the first place is suddenly being difficult. Here is the code used for the OnClick command for the button.

    If (IsNull(Me.txtJobNum)) Then
    MsgBox "Please enter a job # or reference #", 0, "Oops"
    Exit Sub
    Else
    Me.frmSubNOI.Form.Filter = _
    "[ExcavJob #]=" & Me.txtJobNum & " Or " & _
    "[PavJobNum]=" & Me.txtJobNum & " Or " & _
    "[UtiJobNum]=" & Me.txtJobNum
    Me.frmSubNOI.Form.FilterOn = True
    Me.txtJobNum = Null
    Me.txtRefNum = Null
    End If

    I don't see how anything we've done as of late could have affected this. We've generated some new queries, but nothing that should have affected our operation with this one form.

    I thought I'd ask the expert before I go postal on this comp-u-box.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •