Queries Across Tables w/ no Relationship (Access 2000)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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!

Reply | Quote

I am certainly not omniscient, as my continual confusion in this thread proves.

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.

Reply | Quote

You are pure genius, sir.

B. Drake

Reply | Quote

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

Reply | Quote

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)

Reply | Quote

You’re a true hero.

Reply | Quote

What happened??

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.

Reply | Quote

A sentence like this isn’t very helpful:[indent]

---

Our … frmSeachNOIs … is suddenly being difficult.

---

[/indent]It doesn’t provide any relevant information:
– Do you get an error message?
– If so, what does the error message say, and on wich line does it occur?
– Do you get an unexpected or incorrect result?
– If so, what is the result and how is it unexpected/incorrect?

Please help us to help you by providing specific, exact and relevant information.

Reply | Quote

Apologies….

Upon clicking the ‘Search’ button, we get a run-time error ‘2001’ – ‘You cancelled the previous operation’
The debug function highlights the following section:

Me.frmSubNOI.Form.Filter = _
“[ExcavJob #]=” & Me.txtJobNum & ” Or ” & _
“[PavJobNum]=” & Me.txtJobNum & ” Or ” & _
“[UtiJobNum]= ” & Me.txtJobNum
Me.frmSubNOI.Form.FilterOn = True

The other search button on the same form works fine…

Sorry for the vaguness before, you’ve been nothing but a huge help thus far.

Thanks again.

Reply | Quote

The three fields are text fields, not number fields, so their values must be enclosed in quotes:

Me.frmSubNOI.Form.Filter = _
"[ExcavJob #]=" & Chr(34) & Me.txtJobNum & Chr(34) & " Or " & _
"[PavJobNum]=" & Chr(34) & Me.txtJobNum & Chr(34) & " Or " & _
"[UtiJobNum]= " & Chr(34) & Me.txtJobNum & Chr(34)

Text values must be enclosed in quotes, date values in # characters.

Reply | Quote

This is why you are an Administrator and I am a lowly NewLounger.

Thank you sir.

Reply | Quote

I’m just a guy spending too much time at his PC

Reply | Quote