Advice and guidance please

[Mike Green]

Hi All
I am looking for some guidance if someone would be so kind as to indulge
with me for a moment.
I am trying to create a Db to record Job cards, Work carried out, Time taken
and Parts used, along with the total cost for a completed job card. I have
the following tables
Tbl_JobCard, Fd_JobID, FdDateOpened, FdJobState, Fd_DateClosed, FdPitchNo...
Tbl_Work, Fd_WorkID, Fd_JobID, Fd_Time, Fd_Work, Fd_Cost, Fd_WVAT...
Tbl_Parts, Fd_PartID, Fd_JobID, Fd_PartName, Fd_PartQty, Fd_PartCost,
FdPVAT...

As you can see I have a Job Card that can have many Jobs(work) and many
Parts.
From these tables I need to produce a report that shows the total cost of a
complete job card.
Here is where I have come unstuck.
Because there can be job cards that only have many work items but no parts I
cannot get a reports or a query to work when no parts exist for that job
card.
I have to use a query against the Tbl_Work and against the Tbl_Parts to work
out when a Job takes more that 1 hour and the same for the Tbl_Parts when
more than one of each part is used. I have the added problem that depending
on the actual pitch the job card is for depends on if VAT is chargeable or
not (add in some instances there is a mixture of chargeable and non
chargeable items on the same job card).
I have everything working fine apart from the fact that if there are no
parts used and work carried out (or viseversa) I cannot get the calculations
to work as there is no data to return even a null value (or is there a way?)
I have tried using a query to create amount of VAT, total cost, VATTotal and
NonVAT for Fd_JobID from Tbl_Work and one from Tbl_Parts. My idea being
that I was going to create a Tbl_Invoice to store the info for the completed
job card, unfortunately things go horribly wrong and Access errors saying
the source query must be updatable (I tried to use the query mentioned above
along with an update query to update the Tbl_Invoice.
I am just stuck now and I have been for the last 5 hours. I have some
reasonable Access skill although I will admit to being very rusty at the
moment.
Any help and guidance will be greatly appreciated.

Mike Green

 

[Gary Walter]

I don't know if this will help
(or if you already know this),
but some general guidance:

o - for a total query (like for a calculation report),

Left Join your job table to work and parts tables.

you probably have your 3 tables in query designer
and join lines connecting
job.jobid to work.jobid
job.jobid to parts.jobid

Right-mouse click on each line,
choose "Properties"
select option to show *all jobs*
and
- only matching work (for one line)
- only matching parts (for other line)

if a job had no parts, its parts "values" will
be NULL, which you can change to 0 via NZ().

if a job had no work, its work "values" will
be NULL, again changeable to 0 via NZ().

You can filter for specific job(s) in this query
(on the outer table), but you cannot filter
on the inner tables (work or parts) without
destroying the outer join.

To filter on the inner tables, commonly one
will filter an inner table in a prequery, then
outer join job table to this prequery(s).

A typical scenario might be a saved report query
(say "qryrptCalcs" for report "rptCalcs")
where job table is LEFT JOINed to saved
qryPreFilterWork and qryPreFilterParts.

The SQL of the prefilter queries are rewritten
for the circumstance (maybe based on text boxes
on your form in click event of some command
button to show the report)

strSQL="SELECT * FROM Work WHERE [f1]=" & Me!txtbox1
CurrentDb.QueryDefs("qryPreFilterWork").SQL=strSQL

strSQL="SELECT * FROM Parts WHERE [f2]=" & Me!txtbox2
CurrentDb.QueryDefs("qryPreFilterParts").SQL=strSQL

strWhere = "Job.f3=" & Me!txtbox3

DoCmd.OpenReport "rptCalcs",,strWhere


o - an editable form for your structure
usually involves

- a main form bound to jobs table
- a subform bound to parts table
- a subform bound to work table

the Master/Child relationship you set
up between main form and subform will
automatically keep things "square," i.e.,
move to a specific job, and the parts and
work entered for that job magically appear
in the subforms (and you can add/edit/delete
the parts/work data for that job.

Of course, you must create a job record before
you can assign work and part "values" to it....

Maybe that helps (or maybe I completely misunderstood)....

 

[Dun]

i'm not sure i understand the complete problem (it's a lot of
text), but just a question... in query design (Job Card - Jobs -
Parts), have you tried to set the relationship between the tables to
"Include all records from 'Jobs' and only those records from 'Parts'
where the joined fields are equal"? This is the simple way.

It produces the sql like the following...

SELECT aktualno.aktualno_id, aktualno.podrocje_struktura_id,
aktualno_jezik.jezik_id
FROM aktualno LEFT JOIN aktualno_jezik ON aktualno.aktualno_id =
aktualno_jezik.aktualno_id;

(Fields are from another tables, not yours, transform as:
Jobs=aktualno; Parts=aktualno_jezik)

 

[Dun]

i'm not sure i understand the complete problem (it's a lot of
text), but just a question... in query design (Job Card - Jobs -
Parts), have you tried to set the relationship between the tables to
"Include all records from 'Jobs' and only those records from 'Parts'
where the joined fields are equal"? This is the simple way.

It produces the sql like the following...

SELECT aktualno.aktualno_id, aktualno.podrocje_struktura_id,
aktualno_jezik.jezik_id
FROM aktualno LEFT JOIN aktualno_jezik ON aktualno.aktualno_id =
aktualno_jezik.aktualno_id;

(Fields are from another tables, not yours, transform as:
Jobs=aktualno; Parts=aktualno_jezik)

 

[Mike Green]

Thanks Gary
That was the direction I needed. Problem now solved, with your assistance.

Regards

Mike

I don't know if this will help
(or if you already know this),
but some general guidance:

o - for a total query (like for a calculation report),

Left Join your job table to work and parts tables.

you probably have your 3 tables in query designer
and join lines connecting
job.jobid to work.jobid
job.jobid to parts.jobid

Right-mouse click on each line,
choose "Properties"
select option to show *all jobs*
and
- only matching work (for one line)
- only matching parts (for other line)

if a job had no parts, its parts "values" will
be NULL, which you can change to 0 via NZ().

if a job had no work, its work "values" will
be NULL, again changeable to 0 via NZ().

You can filter for specific job(s) in this query
(on the outer table), but you cannot filter
on the inner tables (work or parts) without
destroying the outer join.

To filter on the inner tables, commonly one
will filter an inner table in a prequery, then
outer join job table to this prequery(s).

A typical scenario might be a saved report query
(say "qryrptCalcs" for report "rptCalcs")
where job table is LEFT JOINed to saved
qryPreFilterWork and qryPreFilterParts.

The SQL of the prefilter queries are rewritten
for the circumstance (maybe based on text boxes
on your form in click event of some command
button to show the report)

strSQL="SELECT * FROM Work WHERE [f1]=" & Me!txtbox1
CurrentDb.QueryDefs("qryPreFilterWork").SQL=strSQL

strSQL="SELECT * FROM Parts WHERE [f2]=" & Me!txtbox2
CurrentDb.QueryDefs("qryPreFilterParts").SQL=strSQL

strWhere = "Job.f3=" & Me!txtbox3

DoCmd.OpenReport "rptCalcs",,strWhere


o - an editable form for your structure
usually involves

- a main form bound to jobs table
- a subform bound to parts table
- a subform bound to work table

the Master/Child relationship you set
up between main form and subform will
automatically keep things "square," i.e.,
move to a specific job, and the parts and
work entered for that job magically appear
in the subforms (and you can add/edit/delete
the parts/work data for that job.

Of course, you must create a job record before
you can assign work and part "values" to it....

Maybe that helps (or maybe I completely misunderstood)....

I am looking for some guidance if someone would be so kind as to indulge
with me for a moment.
I am trying to create a Db to record Job cards, Work carried out, Time
taken and Parts used, along with the total cost for a completed job card.
I have the following tables
Tbl_JobCard, Fd_JobID, FdDateOpened, FdJobState, Fd_DateClosed,
FdPitchNo...
Tbl_Work, Fd_WorkID, Fd_JobID, Fd_Time, Fd_Work, Fd_Cost, Fd_WVAT...
Tbl_Parts, Fd_PartID, Fd_JobID, Fd_PartName, Fd_PartQty, Fd_PartCost,
FdPVAT...

As you can see I have a Job Card that can have many Jobs(work) and many
Parts.
From these tables I need to produce a report that shows the total cost of
a complete job card.
Here is where I have come unstuck.
Because there can be job cards that only have many work items but no
parts I cannot get a reports or a query to work when no parts exist for
that job card.
I have to use a query against the Tbl_Work and against the Tbl_Parts to
work out when a Job takes more that 1 hour and the same for the Tbl_Parts
when more than one of each part is used. I have the added problem that
depending on the actual pitch the job card is for depends on if VAT is
chargeable or not (add in some instances there is a mixture of chargeable
and non chargeable items on the same job card).
I have everything working fine apart from the fact that if there are no
parts used and work carried out (or viseversa) I cannot get the
calculations to work as there is no data to return even a null value (or
is there a way?)
I have tried using a query to create amount of VAT, total cost, VATTotal
and NonVAT for Fd_JobID from Tbl_Work and one from Tbl_Parts. My idea
being that I was going to create a Tbl_Invoice to store the info for the
completed job card, unfortunately things go horribly wrong and Access
errors saying the source query must be updatable (I tried to use the
query mentioned above along with an update query to update the
Tbl_Invoice.
I am just stuck now and I have been for the last 5 hours. I have some
reasonable Access skill although I will admit to being very rusty at the
moment.
Any help and guidance will be greatly appreciated.

Mike Green