Join expression not supported

[Bschmale]

If CustomerFinancials will have only one record for each CustomerID and there will always be a record for each CustomerID, could just merge the two tables.

June, I combined the two tables and I set primary keys which took care of my join error!

I have also started learning about subforms, which leads me to my next question.

I have a table that has multiple "areas" for each customer. The table is as follows:

DID	CustomerID	Area	Volume	Revenue	RevDivVol
1	1	Area1	100	101	.99
2	2	Area2	200	201	.99
3	2	Area3	300	301	.99
4	2	Area1	400	401	.99
5	3	Area2	500	501	.99
6	3	Area3	600	601	.99
7	4	Area2	700	701	.99
8	4	Area3	800	801	.99
9	5	Area2	900	901	.99

The DID is basically an arbitrary autonumber that I created as a primary key. My goal is to have a form (subform) which will display all of the records for each company ID. When I try to make a form it displays these as individual based on row. Is there a way to group them together?

[June7]

I don't see company ID in that data sample. How do these records relate to company? Is that the CustomerID?

Did you set the Master/Child Links properties of subform container?

[Bschmale]

I don't see company ID in that data sample. How do these records relate to company? Is that the CustomerID?

Did you set the Master/Child Links properties of subform container?

The customerID is the foreign key. I connected the master/child properties but I am still getting the problem where my subform only shows one of the records for that company. I suspect it is an issue with the way that I have the subform set up. Is there a keyword for this kind of issue that I should be looking for online?

[Bschmale]

Another Question I had was whether or not it is possible to have a drop down box that selects a subform? In other words, is there away to have subforms on a main form that are invisible until selected from a drop down box?

[June7]

What is the subform RecordSource? Does it refer to table or query object or is it an SQL statement? If you want to provide db for analysis, follow instructions at bottom of my post.

Yes, code behind combobox can manage visibility of subform containers. Could also have subforms on pages of a Tab control.

[Bschmale]

June attached is my db. I will look into using tabs to select subforms.

Let me know if you need clarification of my issue. Basically my form is only showing one of the records associated with the customer id.DBHelp.zip

[June7]

Should give fields better name than 'ID'.

CompanyTicker field should not be in Historical table, only the CompanyID.

The subforms are set for Single View. Change to Continuous or Datasheet if you want to see multiple records at once.

[Bschmale]

Should give fields better name than 'ID'.

CompanyTicker field should not be in Historical table, only the CompanyID.

The subforms are set for Single View. Change to Continuous or Datasheet if you want to see multiple records at once.

I will change the ID fields to something better. CompanyTicker is functioning as the primary for that field at the moment and Company ID is the foreign key.

I will look into continuous view as well as try to set up tabs for my different subforms.

[June7]

That won't work.

The PK in CustomerInformation is autonumber CompanyID. This value should be saved as foreign key in Historical.

If you make CompanyTicker a PK in Historical then there can be only one record for each company. This means a 1-to-1 relationship with CustomerInformation. There is no need for both CompanyTicker and CompanyID in Historical. CompanyID can be the foreign key as well as primary key in a 1-to-1 relationship.

The Historical table is not a normalized structure. Will have to modify table, queries, forms, reports every year. Might as well combine the two tables into one.