Comparing Data

[DJTepas]

Hello all,

I need to write a database for work that can compare and report
differences of settings in a PLC.
The PLC saves the data in a paradox file. I can pull information into
access but I am unable to change the way the data is stored. PLC
table is stored;

Machine Number, Setting No., Setting Name, Setting, Setting Page,
Setting No. On Page

12345, 1.1, Max Speed, 3500, 1, 1
12345, 1.2, Min Speed, 3000, 1, 2
12345, 1.3, Selected Components, 10010000111, 1, 3


The Table I need to set up looks like this;

Machine Number, Setting 1.1 value, Setting 1.2 value Setting 1.3 value
12345, 3500, 3000, 10010000111
12223, 3500, 2900, 11101111100

There is alot of information in the tables that is not needed to
compare all the settings. I do need to be able to compare either a
single setting, a whole page, or all settings for a turbine number. I
have to compare these settings against many different sources of data
and I am trying to speed up my current why of doing this. To much
copying and pasting. I did write a macro for excel to speed some of
this up, but still to much time is being used to copy and paste the
different reports that are needed. How do I move that data I need
from table A to table B?

 

[Albert D. Kallal]

The Table I need to set up looks like this;

Machine Number, Setting 1.1 value, Setting 1.2 value Setting 1.3 value
12345, 3500, 3000, 10010000111
12223, 3500, 2900, 11101111100

I not sure you really want to

Further, you can' use "setting 1.1" as a legal field name in ms-access.

if you have either a linked table to the paradox data, or imported the data,
then just build a query to product the above.

select [Machine Number],[Setting no.] as [Value11] from tblData
where [setting no.] = "1.1"

union all
select [Machine Number],[Setting no.] as [Value12] from tblData
where [setting no.] = "1.2"

union all
select [Machine Number],[Setting no.] as [Value13] from tblData
where [setting no.] = "1.3"


I not really sure you want to de-normalize the data. What happens if there
is 4 reading? You might want to work on solving the problem of comparing the
data instead of re-formatting the data to a structure that not very
workable. If you ONLY ever going to have readings 1 to 3, then you might
consider the above, but if there the possibly of more readings, then you
don't want to create a new column. (creating new columns is de-normalized
data, and tends to make data management VERY difficult (you have to change
forms, reports, queries etc. when you add additional columns).

While it common in Excel to create the new columns, in data procesing
circles adding a new columns for new readings of data is going to make the
project VERY difficult to deal with.

Anyway, the union query as above will produce your data in the format you
ask. If you going to need more then 3 readings grouped, then I would
approach this diffent.

it is not 100% clear what readings you want to display, and how you plan to
search, view, or report on readings. My spider sense tells me it best to
leave the data as is, and then build a screen, or export that displays the
readings as you need.....

 

[Hunter57]

Hi,

The quickest way I know to do what you want is by using an Append Query.
But that is a risky thing for those who are not very familiar with quries.
To be safe, make a copy of your Database and test your append query with the
copied Database. Then when you get it right you can inport the query into
your working Database.

An alternative to moving that data: have you thought about creating Reports
or Forms based on a Query that will give you the information you need? A
query can gather data from different tables and put it together on a Report
or Form.

Hunter57
http://churchmanagementsoftware.googlepages.com

 

[DJTepas]

Ok, I need to clarify some information

I am not very good with access, I can build tables and reports, or
import other databases, but other then simple querys, I end up not
getting what I want.

What I am doing is trying to compare values that are saved in paradox
files into a few different reports,

The first is one that is for a customer, and it would list just the
setting number, setting description, and what systems numbers they own
that have wrong settings.

The second one is for the warranty team, and it needs to show the
above but also add the master setting and current setting for systems
the cover.

The third one is for the engineering team and would be for all systems
of type and would need to be able to select just the settings that
they need.

And the last one is one I am having the most problems with, I need a
report that shows all settings and highlight the wrong ones, and then
show after change what all the settings are now set to.

The setting tables are manual downloaded, systems are in closed
network, and when are added I current rename them to their system
number, they are all saved with the same name.

I also see the point of not bringing the information over to a new
table, but with 2000+ systems and 800+ settings that need to be
compared and many of the settings are just counters that can be
ignored. I need to find a faster way to generator the reports. I am
suppose to compare once a quarter, and at my current rate I will be
lucky to finish the first set within 2 years. What I had started to
do was create a table that had an autogenerated number, then the
system number, what customer, then 20 columns(one for each setting per
page) and then the date time the data was upload. I would then have
one table for each page. Please help if you know of a better way? I
have made some improvement since I started this, old way was to open
each table with a database viewer and cut paste to a spreadsheet and
then compare. Now I can compare using access to link each table and
import to excel all settings. But I manual have to edit each one
depending on the report type. Thanks all for your help.

 

[Hunter57]

Hi,
It does look like you have a big job on your hands. I am afraid that this
forum is not capable or designed to meet your needs. I think you need
someone to sit down with you, listen to what you need, and develop the
database for you.

Of course, you can learn how to do it yourself. Here are a few of my
favorite Access sites with tutorials, samples, and tips.

http://www.functionx.com/access/index.htm --Outstanding Tutorials
http://allenbrowne.com/tips.html --Allen is a genius
http://www.kayodeok.btinternet.co.uk/favorites/kbofficeaccess.htm --Great
collection of resources
http://www.rogersaccesslibrary.com/TableOfContents3.asp --Numerous samples
to download
http://www.granite.ab.ca/accsmstr.htm --Really great help here.
http://www.lebans.com/toc.htm --Another Genius
http://www.utteraccess.com ---A lot of free code samples, tips,
& advice.

Best to you,
Hunter57

 

[Albert D. Kallal]

The first is one that is for a customer, and it would list just the

setting number, setting description, and what systems numbers they own
that have wrong settings.

So, do you have a table of these customers?

When you say list settings number, is there come special connection of a
settings number to a customer?

The second one is for the warranty team, and it needs to show the
above but also add the master setting and current setting for systems
the cover.

Ok...you might just include the above in the report you make.

The third one is for the engineering team and would be for all systems
of type and would need to be able to select just the settings that
they need.

And the last one is one I am having the most problems with, I need a
report that shows all settings and highlight the wrong ones, and then
show after change what all the settings are now set to.

I at a loss here, as it seems your under the impression that you have to
build a brand new report from scratch every time you do this. This is NOT
correct.

The sorting and grouping options in ms-access will allow you supply a list
of Settins numbers, and then the reprot WILL GROUP all the setttings numbers
for you. YOu can then use two sub-reposon to dispalyt he relatned data (and
do thins like "higlight" values that don't match.).

I also see the point of not bringing the information over to a new
table, but with 2000+ systems and 800+ settings that need to be
compared

Well, dieiwng a reprton syste for 20 systems, or 20,000 sysetms is not more
dicuclnt. There is not going to be human albourn invlcne, or take MORE human
albor to buitl aht reprot. You mentoned aobut having to type and do all
kinds of things in excel...why? Desing the sytem and the reprot you need.
Once you deonged this, then if you have 20 sysetms, for 20,000 systems, it
will be the same work (the work will be cliking aon a buttion to process the
data..and the luacnh the reprot). When you have this working, it whoudl not
take you mor then a minute to gerneal the reprot you need.

I am
suppose to compare once a quarter

Do you plan to have a years workth of data, or is theis parnncl fiela
one-time file that is sppoe dto you on a queryalry basies?

What I had started to
do was create a table that had an autogenerated number, then the
system number, what customer, then 20 columns

NO, taht sounds all wong again. You thinking in terms of a speand sheet, and
not database data modeling (uselaly the #1 mistkae Excel peole make). What
hapens if you need 21 coluems? You duck soup, and you whole esong comes
raching doewn.

I would then have
one table for each page.

You mean display a data in a table like formatn on a page. You not suggestin
that you have a new table for each page? Again, this sounds all wrong, and
not how reprots are built.

I
have made some improvement since I started this, old way was to open
each table with a database viewer and cut paste to a spreadsheet and
then compare. Now I can compare using access to link each table and
import to excel all settings. But I manual have to edit each one
depending on the report type. Thanks all for your help.

If you design the reporting and queries correct, then very little, if any
human intervention will be need here.

You might have to write some code to general a list of machine, settings
numbers to use for the query joins, but even then, I suspect we can just use
the grouping options of queries to makes these lists on the fly.

You need some knowledge of using sql and grouping.

And, you likely build a nice report prompt page that lets you select the
"turbine, page etc that you need to report on. you then *supply* these
parameters tot he report, and it will generate the results you need.

The first thing is can we assume that your table data is the one table as
defined:

Machine Number, Setting No., Setting Name, Setting, Setting Page,
Setting No. On Page

12345, 1.1, Max Speed, 3500, 1, 1
12345, 1.2, Min Speed, 3000, 1, 2
12345, 1.3, Selected Components, 10010000111, 1, 3

Do we just need to build groupings on the machine number? Or, do you want
the user to enter a machine number, and then general a report for that one
machine? Or shall we have ms-access print each machine number starting on a
new page?

Also, it not clear in the above data, what you want the final report to
show? What in the above data needs to be compared to what data (or is the
comparison of data form the same table?

 

[DJTepas]

Thanks all for your help so far.

This is in reponse to Mr. Kallal's reply. First yes I do almost all
the work right now in Excel. I know that Access is the way to go, but
being new to using both SQL and databases I am struggling some with
this. I do have a list of what customers own what machines. I have a
way to pull the files over to a central drive and rename them to the
machine number. I can also link to them. The problem I am now having
looks to be more with my SQL knowledge.

The first thing is can we assume that your table data is the one table as
defined:

Machine Number, Setting No., Setting Name, Setting, Setting Page,
Setting No. On Page

12345, 1.1, Max Speed, 3500, 1, 1
12345, 1.2, Min Speed, 3000, 1, 2
12345, 1.3, Selected Components, 10010000111, 1, 3

This is partial correct. I have 1 paradox table per machine but they
are all set up the same. There are other columns, but do not apply to
this task.

Do we just need to build groupings on the machine number? Or, do you want
the user to enter a machine number, and then general a report for that one
machine? Or shall we have ms-access print each machine number starting on a
new page?

Most machines are grouped together, so groups would be ok. Also most
of the reports will be for a certain group. I do need to be able to
compare a single machine to a master list. But this is rare, most of
the time it is a group to the master list.

Also, it not clear in the above data, what you want the final report to
show? What in the above data needs to be compared to what data (or is the
comparison of data form the same table?

The first 2 reports I need down as quickly as I can, I currently do
them by hand in excel:

1)A report that breaks out what settings are different then master
list, and what machines have the different settings.

example
Setting Description Machine
1.1 Max Speed 12377, 12378, 12379
1.3 Selected Components 12345

2)A report that breaks out by machine what settings are different from
master list, and the settings

12377
Setting Master Machine
1.1 3500 3499
1.2 3000 1500
1.4 700 0

12378
Setting Master Machine
1.1 3500 3799
1.4 700 7000
I hope this answers your questions. And Thanks all for your help.