Creating a form from 2 tables

[JustLearning]

Hey guys, new guy here, trying to get a handle on things. I'm not even sure if a form is the right way to do this, but here it goes.

Basically, I have 2 tables: Orders and Changes. Orders is the base order, has a number, project numbers that are attached to it and a total amount of cost. Changes is the list of changes to an order, basically showing the progression of an order. For example, Order 101 is attached to projects A and B. It's total amount initially is $100. Along the way, Changes 1, 2 and 3 happen. Each of these changes is documented and stored in the Changes table. I then need a way to view the total amount of that Order including all the changes on one form. Would that be possible?

And some random questions if possible:
How do I add a timestamp to a table when clicking on Add New Record button for a form?
Is there any way to make the new change numbers go immediately after the others? (ie. Order 101 has change requests 1 and 2. Order 102 comes afterwards, and has change request 1. I want to add change 3 to the table, but have it come after change request 2 of Order 101 on the table)


And then I have some general questions about relationships and cross table population, but I'm still a little confused on how to word those right now.

Thanks!

[pbaldy]

Generally you would use a form based on orders and a subform based on changes.

You can have a field in the table with a default value of Now(), which will add the current date/time.

You don't really worry about the order of records in the table, you give them order when you view them. In your case, it sounds like the combination of order number and that timestamp field give you a perfect combination of fields to sort them.

[JustLearning]

Thanks for the quick reply!

I'm trying to create a form and subform with the 2 tables, but I can't seem to get it to work. Does it have something to do with the relationships I set up? Whenever I create a form with data from more than 1 table, the form shows up blank.

For the field in the table with a default value of Now(), what Data Type should I set the column as?

Thanks!

[pbaldy]

Well, the main form would be based on a single table, and the subform would be based on a single table. They would be linked by the related field, presumably an order ID field.

The data type would be Date/Time.