Query to base report, multiple forms, subreports, confused

[blappy347]

I'm trying to create a query that will run a report of the current form. I've got it to run the current form, but that form is based off of multiple tables. The form has pk of WorkOrderID, with fk of CustomerID, FieldID (this is a location, think farming, fields with crops), and ProductID. My form is setup nicely so that you can choose a customer, field, or product from a combo box. When you click the drop down of the combo box it displays the customers first and last name, but the combo box is based off of fk CustomerID only. So when I put the query together to make the report, the report only shows the CustomerID (autonumber) instead of the first and last name of the customer. Same with field. Instead of showing the four parts that make up a fields legal unique locations (1/4, section, township, range) it only shows the first part. In my report I need it to give all the info. This also goes for the product.

I tried to add FirstName and LastName from my CustomerT table into the query and put in criteria based off of CustomerID from the form but I'm definitely not doing it right. I tried "WHERE [WODetsQ1].[CustomerID]"=[CustomerT].[CustomerID], but then what?

I also need to get all of the EPA info for each product on a work order to be in the report. All this info is in the ProductT table. It's a lot of info for each one, should I put that in a sub report? How do I link it to the work order?



Any suggestions? I can post more details or include my db to look at. This is the last piece of the puzzle for my first database and I'm dying to get it done, but this is a little over my head. Any help is appreciated. Thanks.

[rpeare]

if you're putting it as criteria in a query the syntax for referencing a field on a form is:

forms!formname!fieldonform

So let's say your form is called MAINFORM and your field is CUSTOMERID the criteria of your query would be:

[forms]![MAINFORM]![CUSTOMERID]

if you're doing it through building your own SQL statement it would be:

".... WHERE [WODETSQ1].[CustomerID] = " & forms!MAINFORM!CUSTOMERID

if customerid is a numeric field

".... WHERE [WODETSQ1].[CustomerID] = '" & forms!MAINFORM!CUSTOMERID & "'"

if customerid is a text field

[June7]

You based the report on a query that includes the CustomerT and Fields tables by joining on the PK/FK fields?

Need to concatenate variables into SQL string. Reference to control is a variable.

"WHERE [WODetsQ1].[CustomerID]=" & Forms![form name here].[CustomerID],

I don't think the EPA info needs to be a subreport but need to know more about data structure.

[blappy347]

I based the report on a query from the WorkOrderF form. This form has it's PK of WorkOrderID, with two FK - FieldID and CustomerID. In the subform there is another FK, ProductID. So CustomerID is used on the WorkerOderF form to also show FirstName and LastName fields from the CustomerT table, but it's still only the CustomerID field. When I run the report I only get CustomerID, and not the FirstName and LastName fields. I need names in too. Same exact scenario with Field and Product.

The EPA info is enough to take up about a page for each product. It is organized into about 20 different fields. About half are text box and the other half are yes/no check boxes.

It's hard for me to explain, I've attached some screenshots of the db, hopefully it will help.

[June7]

If CustomerT is included in the report RecordSource then the customer name info should be available. Don't know why is not, would have to analyse database.

You have a circular relationship between FieldT, CustomerT, WorkOrderT. This might not be a good thing. http://www.codeproject.com/Articles/...atabase-Design

[blappy347]

So it'd be smart to remove the Field table and form and have the user just enter that into each WorkOrder to avoid the circle of death?

[June7]

Appears to me could:

Break link between FieldT and CustomerT then remove CustomerID from FieldT

Or

Break link between FieldT and WorkOrderT then remove FieldID from WorkOrderT

Just don't know enough about your data and business flow.

Each Customer can have only one location?
If the customer will always have the same location then put the FieldID with customer info. Then the workorder location can be determined through link with customer.

Each workorder can have only one location?
Or if customer can do business in multiple locations and the workorder can be for only one location then put the FieldID in workorder table.