Date field not showing in a form that has been created using a query

[mismag]

Is it a coincidence that total Deals equals VTR/Etch?

It means that the manager sold 51 VTR/ETCH out of his 51 total deals.


Similarly only 34 out of 51 deals were financed.
And sold 40 service contracts out of 51 deals and so on.

[June7]

So the suggestion for tblSales and tblSalesDetails might be valid.

[mismag]

Yes June7.

I have created all the tables as suggested.
Attached are the tables and their relationships.
Hopefully I did it right this time.

I am trying to create a form this way.

Main form:- DealershipName (ComboBox) , Saledate (DatePicker) , Title (Combo Box with 2 options- Sales Manager, Finance Manager), FullName (Combo Box filtered Based on the Title), TotalDeals (TextBox).

1st Sub Form:- If Title is chosen as Finance Manager then, ProductName, Deals, GrossAmount (Enter all the products and their deals information). And calculations of Penetration% and AverageGross.
2nd Sub Form:- If Title is chosen as Sales Manager then enter the following information like NewDeals, UsedDeals, F&IGrossAmount, BackGrossAmount and calculate their averages.

For the main form I have created a query and trying to create the form off of the query. I am attaching the query design too.
Since there is no data in tblSales and tblSalesdetails, when i run the query it is blank.
That is the reason I think I am not able to pick any salesdate.

Please see the attachments and let me know what needs to be fixed.

Thanks a lot.

Regards,
Mismag

[June7]

Do not use that query as main form RecordSource. That query would definitely not be editable. http://allenbrowne.com/ser-61.html

Including tables that are not linked to another table in the query results in a Cartesian association of records and will not be an updatable query.

As I said before, 1 form to 1 table. Including other tables is possible with certain constraints but suggest you start simple.

[mismag]

1 form to 1 table. Including other tables is possible with certain constraints but suggest you start simple.

I am confused. Do you mean I can't create a form using fields from different tables?
How will I be able to do this then?

Main form:- DealershipName (ComboBox) , Saledate (DatePicker) , Title (Combo Box with 2 options- Sales Manager, Finance Manager), FullName (Combo Box filtered Based on the Title), TotalDeals (TextBox).

1st Sub Form:- If Title is chosen as Finance Manager then, ProductName, Deals, GrossAmount (Enter all the products and their deals information). And calculations of Penetration% and AverageGross.
2nd Sub Form:- If Title is chosen as Sales Manager then enter the following information like NewDeals, UsedDeals, F&IGrossAmount, BackGrossAmount and calculate their averages.

Please let me know if I can do these forms and subforms, using the tables I have created.

Thanks,
Mismag

[June7]

Can have multiple tables in a form RecordSource but as you discovered, can cause issues if not done correctly.

Start with main form bound to tblSales with combobox to select dealership. Subform bound to tblSalesDetails with combobox to select product. Set the subform container Master/Child Links properties. Get that to work then we can look at other features.

[mismag]

June7,

I followed your suggestion and got till this point. See the form design.
Now I am not able to add Title and Name from the managers table.

Any help is greatly appreciated.

Thanks,
Mismag

[June7]

Why would you need to? The manager is associated with the dealership. You choose the dealership.

There is no field in either tblSales or tblSalesDetails for manager info.

SaleDate and TotalDeals should not even show in the subform. You do not need a textbox for SaleID in subform. The form/subform Master/Child Links will automatically save the SaleID into SaleIDFK. Autonumber primary keys are not intended to have meaning to users and they really don't even need to see them. Certainly don't need to repeat displaying the SaleID in the subform and if you did, do not need to use an expression referencing main form. Their purpose is to serve as links between related tables.

These are all basic concepts in relational database principles and Access design functionality. Have you studied an introductory tutorial?

[mismag]

Why would you need to? The manager is associated with the dealership. You choose the dealership.

Because there are two types of managers in my database and based on their title I need to input data.
When the user selects the title as "Sales Manager", one sub form should open and when he selects the title as "Finance Manager", another sub form should open.

Any help on how to get to add the title to the form is greatly appreciated.

Thanks,
Mismag

[June7]

Could have an UNBOUND combobox on form to select manager type. Then code loads appropriate form.

Who are 'users' entering data - not the managers I presume.

[mismag]

Who are 'users' entering data - not the managers I presume.

Users are the reporting group, which includes me.
No the managers will not enter the data.

Could have an UNBOUND combobox on form to select manager type. Then code loads appropriate form.

Can I create a form using a query and append the sales table and sales detail table, as i enter the data?
Will that work?

Thanks,
Mismag

[June7]

A form RecordSource can reference a table or query object or can be an SQL statement.

However, Sales and SalesDetail should be on separate forms, ideally in a form/subform arrangement.

[mismag]

June7,

What would be the control Source for the unbound combobox?

Thanks,
Mismag

[June7]

UNBOUND means there is no ControlSource. Control is not used to enter data to table, just to select some criteria used by a process.

[mismag]

UNBOUND means there is no ControlSource. Control is not used to enter data to table, just to select some criteria used by a process.

Thank you June7, for your insight and time. I have learned a lot and now I am able to enter the data as expected.
I really appreciate you.

Thanks,
Mismag