Two tables with identical data

[GLT]

Hi,

I have a database that collects stats on jobs that ran overnight (table A).

I have another table that lists all jobs that are supposed to run overnight
(table B).

What I would like to do is present a list of all jobs that did run (from
table B), but compare that list to (table A) and also include jobs that
should have run.

Any help is always greatly appreciated...

Cheers,
GLT

 

[BruceM]

You would do far better to have a "Ran" field, maybe a Yes/No field to
indicate if the job ran as intended. There are a number of ways you could
work with the resulting data. You could compare the number of jobs that ran
with the total number of jobs on a given date or during a date range; show a
listing of jobs not completed; etc.

 

[GLT]

Hi Bruce,

Thanks for your reply.

Can anyone advise the most effecient way to ac heive this?

Would a query be the best option (if so, how do I construct this), or should
I open the two recordsets in vba and compare each recordset that way?

Can anyone point me to any examples of this?

Cheers,
GLT

 

[BruceM]

I only have time for a quick reply today. A query is almost certainly a
good way to approach this, but the big question is what is "this"? What
exactly do you want to accomplish?
For starters, does it make sense to add a Yes/No field to the jobs table, or
do you need more than two options? Assuming a Yes/No field works, you could
have an Incomplete Job report based on a query in which the criteria for the
[Ran] field is False. The query would contain whatever fields from the jobs
table you want to include in the report, with the criteria as stated applied
to [Ran].
There are lots of other approaches, depending on the details of what you
want to do.

 

[GLT]

Hi Bruce,

Thanks for your reply.

At the end of the day, I just want one table that has a list of jobs that
ran, and stuff that did not run (these will show up on the list as 'Job Not
Found').

My colleagues then fix failed jobs, then go and check the jobs that show up
as blank.

A report then gets created from this data, and after the report is created,
all data in this table is archived to a history table.

Table A is then empty for the process to happen again tomorrow.

All I would like to achieve is one table, with everything that run +
everything that should have run in the list...

Cheers,
GLT

 

[BruceM]

Unless you expect many thousands of records there is no need to archive.
Even if there is an eventual need to move some data to an archive table, it
makes little sense as a day-to-day routine. You may have a design something
like this:

tblJobs
JobID -- primary key, or PK; could be autonumber)
JobDate -- set default to Now()
JobDescription
Ran -- Yes/No field

There may be other fields, but you have not provided much detail, so this is
a generic outline.

Create a query based on the table. In design view, add something like this
to the Criteria row for JobDate:
[Enter Date]
The user will be prompted for a date.

Since you may want to use multiple criteria I would probably use a separate
form for the user to select criteria such as the Date, or Yes/No/All for the
Ran field (to have the option of showing all records, as well as either Yes
or No). Then I would use the selections from the form to build the report's
record source when you run the report. You could similarly use user-selected
criteria to filter a form. For instance, you may want to open the report to
show yesterday's incomplete jobs only.

If you are more specific about what you are trying to do I could probably
offer some specific suggestions.

It would help if you post the table fields you have so far.