using sub forms (1 Viewer)

kobiashi

Registered User.
Local time
Today, 00:56
Joined
May 11, 2018
Messages
258
hi all


i just wanna make sure im doing something correct


so i have a form, and in that form i have 9 sub forms that are set as continuous forms

although the data comes from the same table, i have used 9 different queries, for the sub forms, to filter the data correctly is this the correct way of managing sub forms?

what i want to achieve with this form is store the data historically, so i can return the data from the sub forms, based on the main form date created.


i have thought of using an append query to store the data in another table, but how do i do this so the sub forms return the original values?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 08:56
Joined
May 7, 2009
Messages
19,169
it's okey to have 9 queries showing different view of same data.

the last question is not clear.
 

kobiashi

Registered User.
Local time
Today, 00:56
Joined
May 11, 2018
Messages
258
ok, so the data thats being displayed in the sub forms, will change over time, but what i want to do is capture the data is it is when in the form.

i am assuming as the data being displayed in the sub forms, are linked to data that will change, so will the data in the sub forms, i need to be able to show the data in the sub forms historically.
 

jdraw

Super Moderator
Staff member
Local time
Yesterday, 20:56
Joined
Jan 23, 2006
Messages
15,364
Data is stored in tables.
You can SELECT data from your table(s) via query(s).
You can use criteria to restrict/constrain what data is presented via a query.
The form and subform(s) are strictly a means to present/display the data stored in a table(s).

Can you describe or show us some sample data and what exactly you want to show for historical purposes.

Good luck with your project.
 

mike60smart

Registered User.
Local time
Today, 00:56
Joined
Aug 6, 2017
Messages
1,899
Hi

I would suggest that there is something wrong with your table structure that needs 9 subforms?

You would normally have a Main form based on 1 table and a Subform(s) to display related data.
 

kobiashi

Registered User.
Local time
Today, 00:56
Joined
May 11, 2018
Messages
258
Hi

I would suggest that there is something wrong with your table structure that needs 9 subforms?

You would normally have a Main form based on 1 table and a Subform(s) to display related data.

the way the form is set up, there is no relation, the data is displayed on different criteria
 

kobiashi

Registered User.
Local time
Today, 00:56
Joined
May 11, 2018
Messages
258
Data is stored in tables.
You can SELECT data from your table(s) via query(s).
You can use criteria to restrict/constrain what data is presented via a query.
The form and subform(s) are strictly a means to present/display the data stored in a table(s).

Can you describe or show us some sample data and what exactly you want to show for historical purposes.

Good luck with your project.

thank you

i will put some sample data and upload it
 

kobiashi

Registered User.
Local time
Today, 00:56
Joined
May 11, 2018
Messages
258
ok, so ive uploaded some sample data


if you go to frm_Eng_MCU_InServiceIssues

you'll see the form, which i have the sub forms, there is a corresponding query for each sub form, which are labelled the same

all of the data for these forms come from frm_Eng_MCU_Tracker, and are filtered in the queries.


so what i want to be able to do, is save the main frm_Eng_MCU_InServiceIssues, as a records, but then capture the data fro the sub forms as well, as i said, all of the data comes from frm_Eng_MCU_Tracker, and it changes, so i want to capture the sub forms data as historical
 

Attachments

  • example.zip
    530.1 KB · Views: 54

mike60smart

Registered User.
Local time
Today, 00:56
Joined
Aug 6, 2017
Messages
1,899
Hi

Storing details of 4 Vehicles is not normalised.

The Vehicles should be in a separate table.

Can you explain in simple English what your Business process is??
 

kobiashi

Registered User.
Local time
Today, 00:56
Joined
May 11, 2018
Messages
258
Hi

Storing details of 4 Vehicles is not normalised.

The Vehicles should be in a separate table.

Can you explain in simple English what your Business process is??

hi


sorry in dont quite understand the question,

do you mean, you wish to know what industry i work in?
 

mike60smart

Registered User.
Local time
Today, 00:56
Joined
Aug 6, 2017
Messages
1,899
Hi

What I am trying to determine is your work flow for Data Input.

Something along the lines of :-

A Customer on a given date will Order one or more Items.

Indicating that I need a minimum of 3 tables
 

kobiashi

Registered User.
Local time
Today, 00:56
Joined
May 11, 2018
Messages
258
hi right i get ya

ok so everything is stored against the vehicles

so in this instance the vehicle will be the customer

so all exams are carried out against the vehicles, but all types of exams can be carried out against the vehicles (many to many)

when events occur against the vehicle, all events can happen to all vehicles but the events are unique to each vehicle
 

mike60smart

Registered User.
Local time
Today, 00:56
Joined
Aug 6, 2017
Messages
1,899
Hi

Now I am completely baffled.

Please explain simply your business process using your tables.

What does MCU stand for??
 

June7

AWF VIP
Local time
Yesterday, 16:56
Joined
Mar 9, 2014
Messages
5,423
Why do you have 4 vehicle fields? Remove 1, 2, 3.

When you save a IDFK don't also save descriptive text, e.g.: VehicleType_IDFK, VehicleType.

Be aware that storing large file objects within database can quickly use up Access 2GB file size limit. What do you plan to save as attachments?
 

jdraw

Super Moderator
Staff member
Local time
Yesterday, 20:56
Joined
Jan 23, 2006
Messages
15,364
kobiashi,

If you can't clearly describe the business in simple terms, then neither you nor anyone else can design it.
Too many jump into physical Access before they have a plan for development built on analysis and design. Build a model (on paper is fine); create some test data and make adjustments until it meets your requirements. Now you have a blueprint for your tables and relationships.

so in this instance the vehicle will be the customer
is a symptom of insufficient analysis or clarity.
 

kobiashi

Registered User.
Local time
Today, 00:56
Joined
May 11, 2018
Messages
258
Why do you have 4 vehicle fields? Remove 1, 2, 3.

When you save a IDFK don't also save descriptive text, e.g.: VehicleType_IDFK, VehicleType.

Be aware that storing large file objects within database can quickly use up Access 2GB file size limit. What do you plan to save as attachments?

hi thanks for the reply

when you say 4 vehicle fields are you talking about the table "Table_Vhicles"?

the IDFK is the ID foreign key

i am developing a function to manage images with out storing them in the tables
 

kobiashi

Registered User.
Local time
Today, 00:56
Joined
May 11, 2018
Messages
258
kobiashi,

If you can't clearly describe the business in simple terms, then neither you nor anyone else can design it.
Too many jump into physical Access before they have a plan for development built on analysis and design. Build a model (on paper is fine); create some test data and make adjustments until it meets your requirements. Now you have a blueprint for your tables and relationships.

is a symptom of insufficient analysis or clarity.


hi

thanks for the advice


this is what i did to begin with, i do understand my database, maybe i should have been a bit more descriptive, i thought referencing the vehicles as customers, would have made more sense as i assume most people use a database to manage customers, order, sales etc
 

June7

AWF VIP
Local time
Yesterday, 16:56
Joined
Mar 9, 2014
Messages
5,423
I meant the 4 vehicle fields in Table_MCU_Tracker. Fields Vehicle1, Vehicle2, Vehicle3 just don't make sense.

We still don't know what MCU stands for.

VehicleType_IDFK and VehicleType should not be in Table_MCU_Tracker. There are 12 _IDFK fields and looks like each has a corresponding descriptive text field that should not be there.

People get bills, therefore people are usually customers. Viewing vehicles as customers is unorthodox but if you don't care about tracking info (name, address, phone) about people who own the vehicles then go with it.
 
Last edited:

Users who are viewing this thread

Top Bottom