Problem with main form and three tabbed subforms

[LWoods]

Hi All!

I have created a project tracking db for a business unit at work. They use the db to track each project's details, as well as info from three categories (equipment, stock and labour). In the background of the db, I have calculations for the month-end reports that take the equipment, stock and labour usage as input by the users, and spits out the total cost for the project.

Unfortunately, I just ran the month-end reports, and noticed that not every project is being accounted for in the report. I know that this is happening because if no records for that project exist in the subform, then it is not being picked up in the query. Then when I combine the three categories in a query, it only grabs the projects where they all have the project number in common.

So, I'm trying to figure out a way to have at least one record for each category that is equal to 0 to ensure that it will be captured in the query/report.

Db Structure includes:

frmProject: includes project details on main form and on Tab 1. Subforms for Equipment, Stock and Labour on Tabs 2, 3, and 4 in a datasheet view as there may be multiple pieces of equipment, etc. being used for each project.

Master Field
- Project Job Number (YY-MM-xxx) where xxx is a sequence number (manually typed in)

subEquipment: drop down list of available equipment, user enters quantity.
subStock: drop down list of available stock, user enters quantity.
subLabour: drop down list of available labour types, user enters time spent.

Child Fields
- Equipment Job Number (YY-MM-xxx) when a new record is added, this is automatically numbered the same as the current Project Job Number
-Stock Job Number (YY-MM-xxx) formatted same as Equipment Job Number
- Labour Job Number (YY-MM-xxx) formatted same as others

Any thoughts?

Many thanks in advance!

-L

 

[jzwp22]

Welcome to the forum!

It sounds like you have to adjust the join types in the query you use for the report.

You will have to use a LEFT JOIN rather than an INNER JOIN from the job/project table to each of the other related tables.

In the query design grid, click on the join line from you project/job table and select the option that says select all records from your project/job table and only those records from the other table that match. It should be option 2.

 

[LWoods]

Hi jzwp22! Thanks for your response.

I tried changing the join types in the query as you suggested. When I select the left join (#2), I get an error that says:

The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in your SQL statement.
​

All of the tables in my query are from Tables, not Queries.

Also, if I select #3, the query runs, but only 12 blank lines appear suggesting that there are results, but the fields are empty.

If I can somehow fix the error, will that create the record(s) that I need in the subform(s)?

Thanks!
-L

 

[jzwp22]

Can you provide more detail (table & field names and relationships) on the tables involved and the SQL text of the query that gave you the error?

If it is easier, you can run the compact and repair utility in Access and zip and post the database (with any sensitive data removed or altered).

 

[LWoods]

No problem. I can provide an outline of the tables and fields. If absolutely necessary, I can compact and zip, but I'd prefer not to have to remove all the sensitive data, plus there are multiple BUs in the db that track their own projects differently, so there are a ton of tables and queries to weed through...

As discussed previously, I have a main project form with 3 subforms on different tabs -- I require the same resolution for all three, so for time’s sake and to reduce the outline below, I’ll just explain the Labour category. It has the least amount of fields and is less complicated than the Equipment and Stock categories.

tblProject
Description: includes all the basic details of the project.
Fields:
Project Number (Text, assigned manually by user in sequence, YY-MM-xxx)
Project Type (Text)
Employee Name (Text, linked to tblEmployees)
Project Title (Text)
Business Unit (Text, linked to tblBusinessUnits)
Date Requested (Date/Time)
Due Date (Date/Time)
Completed Date (Date/Time)
Project Details (Memo)
Status (Text, linked to tblStatus)

tblLabour
Description: shows the different types of labour and whether or not they are active
Fields:
Labour ID (AutoNumber)
Labour Type (Text, linked to tblLabourTypes)
Labour Description (Text)
Labour Active (Yes/No)
(Subdatasheet Name: tblLabourInfo)

tblLabourInfo
Description: (a subdatasheet of tblLabour) shows the costs associated with the labour type and which one is active (the client wants to be able to view the previous costs associated the each labour type)
Fields:
Labour Info ID (AutoNumber)
Cost (Currency)
Start Date (Date/Time)
End Date (Date/Time)
Charges Active (Yes/No)

tblLabourUsage
Description: records all instances of the labour according to project.
Fields:
Labour Project Number (Text, linked in query to Project Number)
Labour Sequence (AutoNumber)
Labour ID (Text)
Labour Type (Text, linked to tblLabourTypes)
Employee (Text, linked to tblEmployees)
Time Spent (Number)

The form, frmProject, contains the details from tblProject and a subform connected to tblLabourUsage. The subform is shown in datasheet view to the user as there may be multiple instances of Labour required for each project.

The queries work beautifully when the user adds details into subLabourUsage, however if they don’t, then it obviously won’t be picked up in the query.

Overall, I need to automatically create a record in subLabourUsage that is equal to 0, so that the query will show that there is a project, but that no charges were incurred in the Labour category.

Many thanks!!
-L

 

[jzwp22]

Overall, I need to automatically create a record in subLabourUsage that is equal to 0, so that the query will show that there is a project, but that no charges were incurred in the Labour category.

No you should not need to do this.

I would create a query with the tblLabourUsage and all of its related tables EXCEPT the project table. Make sure to include the project number field of tblLabourUsage in the query. Save this query.


Now for your query that includes the project table, remove the tblLabourUsage and replace it with the query just created. Make sure to do the left join as previously discussed.

 

[LWoods]

jzwp22, thank you so much for your help! I re-created the query and changed the relationships to LEFT join and everything is working beautifully. Not sure why I was experiencing the error on the other query...

Sometimes the problem is so big, you don't realize that the answer could be simple, so thanks for screwing my head on straight!

-L

 

[jzwp22]

You're welcome. Glad you got it sorted out.