union query

[New_Access]

Hi all,
I'm trying to create a database that contain electrical
performance of my companiy's product.As the objective,
at last I have a report look like below:

Model: OSC30TDS1 Prod.code: 71102
-------------------------------------------
Specification
------------------------------
Ampere Power Airflow
SampleNo 3 10 100
-------------------------------------------
1 2.4 8.7 89
2 2.6 8.8 90
:
5 2.1 8.5 87

I have:
1.Model_tbl with 1400 records.
2.Spec_tbl contain spec for each model

Untill that I'm still getting dizzy what should I do next.
I think I should build a Inspection result_tbl but not
sure how unit it by 'Model' and 'Prod.code' with a union
query and then build a form for inputting the data.

For every Prod.code I need to take 5 sample or less
Hope somebody like to help about union query or
show me another better way before I put my foot on
a wrong next steps.
Thank's before

 

[Klatuu]

You don't need a union query for this. What you need is a field in the
specifications table that contains the modle number the specifications belong
to. To do this correctly, you need a form/subform. The form's record source
should be the model table and the subform's record source should be the
specifications table.

 

[New_Access]

Thank's Mr.Dave,
But where do I need to input the inspection result?.
Do I need to create another table?
Hope you would like to help.

 

[Klatuu]

You don't need another table.
The model table would be in the main form and the specifications table would
be in the subform. You would input your data there.

 

[New_Access]

Dear Mr.Dave,
Then you think I should have new below fields in my Spec_tbl :
- Actual Ampere 1
- Actual Ampere 2
- Actual Ampere 3
- Actual Ampere 4
- Actual Ampere 5
- Actual Power 1
- Actual Power 2
- Actual Power 3
- Actual Power 4
- Actual Power 5
- Actual Airflow 1
- Actual Airflow 2
- Actual Airflow 3
- Actual Airflow 4
- Actual Airflow 5

Doesn't it look like too complicated,and in a certain condition
there will be a second Spec_tbl I need.Cause there some model
work in two condition.
Thank's before

 

[Klatuu]

No, it is not too complicated. This is a normal situation. One model can
easily have more than one specification. You need to have a field in the
specification table that has a field that contains the model number to which
the specification belongs. This is called a one to many relationship. That
is one model with multiple specifications.

 

[Michel Walsh]

Definitively not the way to go. No. All measures should (could) be in ONE
table, a little bit like:

DateTimeStamp ModelRef Ampere Power Airflow ' fields
name
2000.01.01 08:08:00 1010 1.3 4.5 132
2000.02.01 08:12:00 1010 1.4 4.8 145
....
2000.01.01 10:11:00 1011 1.8 5.4 178
....
' data sample


Each model (machine, sampling) is only referred to through the field
ModelRef, as example.
Instead of numeral reading, 1, 2, 3, ... it is generally more robust to use
a date_time_stamping: it gives an implicit ordering (through time) for each
reading, and it also say 'when' the reading has been made. There is one and
only one reading (ampere, power, airflow) per record.

You can fill the data with a form / subform user interface. The form allows
you to point to the model (machine) for which you have to enter data, and
the sub-form allow you to fill this table with as many records as you need,
supplying Ampere, Power and Airflow. The ModelRef value can be read from the
main form (so no need, really, to re-enter it in the sub form) and if you
have no date_time_stamp value to be entered, have the actual date and time
value, now, filling the stamp field 'by default'.


For your report, make a query that will join this table and the table for
all models (machines), make that query the source or the report, and, in the
report, GROUP on models, and, in the detail section of each group, supply
the three values Ampere, Power and AirFlow. If you ORDER by date_time_stamp,
on the report, you will get those records printed in that order, in the
details.


Vanderghast, Access MVP

 

[New_Access]

Thank's you both,
but bit more explanation.
Beetween my main form and my subform,do I need
to set Master link and Child link by using field Model
and Prod.Code?

And about 'The datetimestamp',can we just hide it but
it exist,so the user no need to fill it.Cause in my report
I need to dispaly no 1.2....5.
Please advice

Thank's before

 

[New_Access]

Forget to mention,
I have field Prod.code just only in the
'Measures_tbl' and how to link it with
the main table.And as you say maybe
it's not to good to repeatly type model
name and prod code to the subform.

Thank's before

 

[Michel Walsh]

Sure, it is preferable to make the link between the main form and its
subform.

To regenerate the ranking, in a report, a text edit control has a property
called "Running Sum", set it to yes, and its control source to the constant
one (1). Properly set, you should then get the running sum of 1, 1+1, 1+1+1,
.... So there is no need to enter it, if you already have a field defining
the order in a unique way (so it is always printed in the same way,
hopefully).


Vanderghast, Access MVP

 

[Michel Walsh]

Indeed, if this field is a repetition, move it to the right table to avoid
the need for the repetition, and use a join to retrieve it back, if
required.


Vanderghast, Access MVP