queries or calculating controls

[FerryMary]

My obvious newbie-ness and total lack of understanding will probably make the
MVPs in here want to gag. Disclaimer: My apologies for any upchuck action I
may cause.

I have a problem that has to be so simple to fix but I just keep getting
blocked.

Tables are set as so, db is based on service element and credential element
of ferry maintenance service.

Tables are as followsI'm leaving out lookup tables and the like)
tblJob-pk-JobNumber(auto#) :Contains=vslid,statusid,initiateddate {All
information my query needs is in tblJob}
tblJobGate-pk-JobNumber(from tblJob):REPORTNO(number field),EndDate, and more
tblJobsBySystem-pk-JobsBySystemID(Auto#):JobNumber,equipid,temp readings etc
tblJobsByCertsDocs-pk-JobsByCertsDocsID(Auto#):JobNumber,CertID,Training
source, Required By:, etc.

frmJob has command button with macros which close/save frmJob,Opens
frmqry(which formats tblJob info via query adapted from a really nice person
on this site figured out for me as follows: vessel #-2digityear-count jobs
per vessel>ooo-oo-ooo) My goal is for this result to be my ReportNo in
tblJobGate. I want it for local tracking convenience,retention info and I'm
starting to think I want it because it's the only thing hindering me from a
great system.

I've tried DLookup, but since the JobNumber won't show up in a lower table I
can't search by that means. I'm willing to have a form contain all the
tblJob and tblJobGate info, but it's not working for me either. I have an
append query that places the Report No in my table, but can't get it to do so
without the query showing on top. You can't make form based on an action
query,,right?

Can I just place the INSERT query that works into my form code? If so where
might that be. I think my little microsoft paperclip was bleeding yesterday.

The append qry looks something like this. Sorry I'm at home and don't have
it in front of me.

INSERT INTO tblJobGate.JobNumber,Report
Select (MaxOfJobNumber AS JobNumber,ReportNo)
FROM qryVMPNumber

Or could a calculated control in a footer figure and concatenate(?) my
ReportNo?
I need to do from a form. Thanks so much.

 

[Jeff Boyce]

See comments in-line below...

My obvious newbie-ness and total lack of understanding will probably make the
MVPs in here want to gag. Disclaimer: My apologies for any upchuck action I
may cause.

Everything's relative. What you've already accomplished is far ahead of
some.

I have a problem that has to be so simple to fix but I just keep getting
blocked.

Tables are set as so, db is based on service element and credential element
of ferry maintenance service.

Tables are as followsI'm leaving out lookup tables and the like)
tblJob-pk-JobNumber(auto#) :Contains=vslid,statusid,initiateddate {All
information my query needs is in tblJob}
tblJobGate-pk-JobNumber(from tblJob):REPORTNO(number field),EndDate, and

more

?If tblJobGate has the same primary key that tblJob has, is there a business
reason you've made it a separate table? This is a one-to-one relationship,
as you've described -- is that what you are after?

tblJobsBySystem-pk-JobsBySystemID(Auto#):JobNumber,equipid,temp readings etc
tblJobsByCertsDocs-pk-JobsByCertsDocsID(Auto#):JobNumber,CertID,Training
source, Required By:, etc.

These appear to be the "many" side of one-to-many relationships with tblJob.

frmJob has command button with macros which close/save frmJob,Opens
frmqry(which formats tblJob info via query adapted from a really nice person
on this site figured out for me as follows: vessel #-2digityear-count jobs
per vessel>ooo-oo-ooo) My goal is for this result to be my ReportNo in
tblJobGate. I want it for local tracking convenience,retention info and I'm
starting to think I want it because it's the only thing hindering me from a
great system.

I'm not clear on this -- are you saying that you are storing a calculated
value in ReportNo? There's no need to do that, if so, since the formula you
use to "calculate/display" it can be part of a query that you use to fill
your form.

I've tried DLookup, but since the JobNumber won't show up in a lower table I
can't search by that means. I'm willing to have a form contain all the
tblJob and tblJobGate info, but it's not working for me either. I have an
append query that places the Report No in my table, but can't get it to do so
without the query showing on top. You can't make form based on an action
query,,right?

Can I just place the INSERT query that works into my form code? If so where
might that be. I think my little microsoft paperclip was bleeding yesterday.

The append qry looks something like this. Sorry I'm at home and don't have
it in front of me.

INSERT INTO tblJobGate.JobNumber,Report
Select (MaxOfJobNumber AS JobNumber,ReportNo)
FROM qryVMPNumber

Or could a calculated control in a footer figure and concatenate(?) my
ReportNo?
I need to do from a form. Thanks so much.

You've describe a lot of the "how" you are trying to do something. Could
you explain, in terms my 78-year old mother would understand, the "what"
(and "why") of what you are trying to accomplish?

 

[FerryMary]

I'll follow your comments in-line too. Some snipped.

See comments in-line below...




Everything's relative. What you've already accomplished is far ahead of
some.

Thanks, but the credit goes mostly to the kindness/resource of this group.

more

?If tblJobGate has the same primary key that tblJob has, is there a business
reason you've made it a separate table? This is a one-to-one relationship,
as you've described -- is that what you are after?

My thinking was, I needed only the information currently in tblJob
(Vessel#,ServiceDate,StatusID) to get the report numbers I wanted.

The report number I want makes it easy forme to find a particular record
when crew members ask about a job. (Engineer on boat # 005 asks about "that
thing y'all were supposed to fix on port main sometime last month") The
number itself lets me know when I can throw it away-retention laws etc, also
it's a quick way to see how often this vessel is breaking down or requiring
more than standard service. MostlyI've been using this numbering system and
the guys are used to it ,so I thought it would be nice to stick to.

These appear to be the "many" side of one-to-many relationships with tblJob.


I'm not clear on this -- are you saying that you are storing a calculated
value in ReportNo? There's no need to do that, if so, since the formula you
use to "calculate/display" it can be part of a query that you use to fill
your form.

I want to store it in the table, I know that's not good policy, but I'd like
to be able lookup records by that number. I haven't been ble to figure out
how to get the recordset(?) to fill into my form.

You've describe a lot of the "how" you are trying to do something. Could
you explain, in terms my 78-year old mother would understand, the "what"
(and "why") of what you are trying to accomplish?

--
Good luck

Jeff Boyce
<Access MVP>

What I want is a distinct recordset,based on jobtype and vessel, (query
returns the number for all vessels) retrieved and filled in on my form. Which
in turn would save it to my table. The JobGate is not necessary, but it
seemed like it was acting as a junction table from tblJob to my two Details
tables.

I'm sorry to overexplain,,,,hopped up on caffeine at 4am is no way to start
the day.
Thanks again.
FerryMary

 

[Jeff Boyce]

Tables are as followsI'm leaving out lookup tables and the like)

My thinking was, I needed only the information currently in tblJob
(Vessel#,ServiceDate,StatusID) to get the report numbers I wanted.

The report number I want makes it easy forme to find a particular record
when crew members ask about a job. (Engineer on boat # 005 asks about "that
thing y'all were supposed to fix on port main sometime last month") The
number itself lets me know when I can throw it away-retention laws etc, also
it's a quick way to see how often this vessel is breaking down or requiring
more than standard service. MostlyI've been using this numbering system and
the guys are used to it ,so I thought it would be nice to stick to.

With Access, the first thing is the data. If you have the specific data
elements you need, you can assemble/display them in almost any format. If
the data in your JobGate table was only for helping build a recognizable ID
that humans can relate to, there's no business (nor Access) need to keep it
in a separate table.

I want to store it in the table, I know that's not good policy, but I'd like
to be able lookup records by that number. I haven't been ble to figure out
how to get the recordset(?) to fill into my form.

Here's one way to look up a record by the "recordnumber", WITHOUT storing
the recordnumber:

Create a query that "calculates" the recordnumber from the pieces you are
assembling. Include the JobID and this calculated recordnumber.

In a form based on tblJob, add a combo box (unbound) to the Header of the
form. This combo box uses the query above as its row source. The first
field is the ID number (set the width of this one to 0), the second is the
"calculated" recordnumber that folks know and love. When you select a
recordnumber from the combo box, requery or filter the form to display the
related record from tblJob.

 

[FerryMary]

Replies to replies to replies below <g>

lol

Here's one way to look up a record by the "recordnumber", WITHOUT storing
the recordnumber:

Create a query that "calculates" the recordnumber from the pieces you are
assembling. Include the JobID and this calculated recordnumber.

In a form based on tblJob, add a combo box (unbound) to the Header of the
form. This combo box uses the query above as its row source. The first
field is the ID number (set the width of this one to 0), the second is the
"calculated" recordnumber that folks know and love. When you select a
recordnumber from the combo box, requery or filter the form to display the
related record from tblJob.

That works great, but now my query behaves oddly. My problem seems to be
that I have the query only returning values of "Max" assuming a new number
would be the most recent added. I much prefer the method you described to
what I was trying to do. Maybe I should post my 'new' query problem in query
group?

Just in case you or another Good Samaritan has any ideas here's my SQL (
y'all will probably see some areas that could be improved)

SELECT Max(tblJob.JobNumber) As MaxOfJobNumber,
Format(tblJob.VesselID,"000") &
Format(tblJob.InitiatedDate,"yy") &
Format(tblStatus.JobType) &
Format(Count(tblStatus.Jobtpe),"0000") As ReportNo,tblJob.VesselID
FROM tblStatus INNER JOIN tblJob ON tblStatus.StatusED
GROUP BY tblJob.Vessel,(tblStatus.JobType),Format(tblJob.VesselID,"000") &
Format(tblJob.InitiateDate,"yy")
ORDER BY Max(tblJob.JobNumber) DESC;


VesselID-number,yy,JobType-aaa,Count-0000
Sample looks like 00605SCI0001
Jeez,,,even my SQL is long winded.


I've tried different angles on this query, but when I get each JobNumber to
show up, all entries in count field return as 1. I need it to count each
record where VesselID and JobType are common and result show on JobNumber row.

You've been great Jeff
Thanks Again

Mary

 

[Jeff Boyce]

A post to the query 'group will get you more "eyes" on it.

I'll mention that I sometimes "chain" together queries. In your example,
would there be any benefit from building one query that derives the standard
record identifier, then joining that with another query to work on the
Max()? You could still include the underlying fields from the standard
record identifier, for sorting or selecting...

Good luck

Jeff Boyce
<Access MVP>