way over my head

[butthead]

Not being very proficient at access I am at a loss for where to start at this task.

I also am not very adept at vba (C++ is my environment) .
My SQL is not to advanced.

I have a database that tracks a group of welders and their
welds. The purpose is to be able to check quickly whether
a particular welder has exceeded his or her number of
allowable bad welds. Termination occurs rather quickly.
(not my problem)

I am able to generate reports of individual welders and by
date ranges also. The task I have been given is to
generate a report that lists all the welders and the number
of Repairs that were necessary for each welder.

Report fields for each welder

1- number of welds
2- number of repairs to individual welds
3- percentage of repairs
4- date range

any help as to were to start would be appreciated.

Other than exporting the table to paradox format and
writing the code in C++ I don’t have a clue.

thanks in advance

 

[PHV]

Any chance you could post your database schema, some data examples and the expected result ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[BNPMike]

Access is an SQL environment, so you really need to read at least some introductory material on SQL.

One way of counting different things at once is to assign 1 or zero to them. Then you can sum them. Firstly you need to group by Welder. Then for example you could sum the the number of all welds and bad welds. This gives you the basis for your percentage calculation. You can do this calculation in your report by creating a field on the report page.

Access is not like a different car - you can't just jump in and drive off. It's like an airliner - you have to spend some time studying all the controls, levers and systems before you start rolling down the runway.

 

[butthead]

I said my sql is not too advanced, not absent.

the weld list table contains the following fields.

date
jobticket#
job#
welderID
weld number
accept
repair
type
to
from
other


each weld can have several entries as this table is actually a tracking
mechanism for the xray film results. the welds may require one or
more entrys to list all the film required to xray the entire weld.

Weld# welderID accept repair to from
weld1 welderA yes no 1 2
weld1 welderA yes no 2 3
weld1 welderA yes no 3 4
weld1 welderA yes yes 4 1

weld2 welderB yes no 1 2
weld2 welderB yes no 2 3
weld2 welderB yes no 3 4
weld2 welderB yes no 4 1

the above table also has date and job information not shown. Also if a weld
has 2 or more repairs it is counted as 1 bad weld.

I percieve athat I must do the follwing

1-obtain a sublist of weldlist by date range
2-from the preceding list do a disnct query for
date,jobticket#,welderID,weld# for a count of the total welds
this list contains all welders.
3- from the sublist do a disnct query for
date,jobticket#,welderID,weld#,repair for a count of the total repairs
this list contains all welders.

All I am trying to do is to get a report of all Welders in a date range and
display the peercentage of repairs, posibly to a graph later. I think that I
may have some trouble with the repair count query.

Since I will get a zero value for most welds for repiair this table will end
Up shorter than the the sublist table and therefore be out of sync.
i.e. record1 welder of the first table may not corespond to the first record
welder of the repair list and so forth

result table desired

welderID weld count %repair
w1 2 0
w2 3 0
w34d 7 1

 

[PHV]

result table desired
welderID weld count %repair
w1 2 0
w2 3 0
w34d 7 1
How is this desired result obtained from the posted samples ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[butthead]

the folowing is the result for the sample listed ablove

welderID weld count %repair
weldA 1 100
weldB 1 0

the table result that I gave just had sample data that
was created on the fly and I can now see how it would be
confusing.

I can get the individual reports for each welder but
i suspect that I will have to use VBA to iterate through
the list of welders and add the sql results from each
query to a result table. I would do this with a for loop
in C++. if some one could give me an example of a loop in
VBA I could perhaps muddle through. I have used some VBA
in this project but it does not go much further than simple
if/then statements.