Unable to generate correct report from query based on join properties

[nika.duncan]

Hey,

I am struggling with a query to generate a report with the choice selection by each person. The issue I am having is that after selection if I used the "StaffMenuDetail" table to run the query the output for the choices are listed in numbers. When I add the other tables like the "ProteinChoices", "AppetizerChoice etc., what happen is that detail of data is reduced. The join or relationship is not able to produce the name of the choices because although "ProteinID" and "AppetizerID" is linked in the "StaffMenuDetail" it is not generating the name.

Not sure how to generate the name as oppose but any help will be appreciated. Attached is a copy of the file been used. It is incomplete but I wanted to ensure that aspect is working before moving forward.

Nika

[June7]

Which query? Don't see report object.

[nika.duncan]

The query is "qryMenuChoosen", I haven't created the report as yet but was just running the query to see what is generated. When I start created the query and add the tables one by one starting with ""StaffMenuDetail" the record keeps reducing as I continue the join.

[Gicu]

not looked at your file but you might want to use outer joins with the arrows pointing away from StaffMenuDetails.
Cheers,
Vlad

[June7]

I have looked at. The issue is beyond just changing the JOIN type. The query you have won't work. Joining WeekDay_Choice to every table is unnecessary. Just join it to StaffMenuDetail.

Then change to RIGHT JOIN:

SELECT AppetizerChoices.Appetizer, StaffMenuDetail.Branch, CarbChoices.CarbChoices, ProteinChoices.ProteinChoices, SideChoices.SideChoices, VegetableChoices.VegetableChoices, Staff_Listing.Staff_Name, WeekDay_Choice.WeekDays
FROM WeekDay_Choice RIGHT JOIN (AppetizerChoices RIGHT JOIN (ProteinChoices RIGHT JOIN (VegetableChoices RIGHT JOIN (Staff_Listing RIGHT JOIN (CarbChoices RIGHT JOIN (SideChoices RIGHT JOIN StaffMenuDetail ON SideChoices.SideID = StaffMenuDetail.SideID) ON CarbChoices.CarbID = StaffMenuDetail.CarbID) ON Staff_Listing.Staff_UD = StaffMenuDetail.StaffNameID) ON VegetableChoices.VegetableID = StaffMenuDetail.VegetableID) ON ProteinChoices.ProteinID = StaffMenuDetail.ProteinID) ON AppetizerChoices.AppetizerID = StaffMenuDetail.AppetizerID) ON WeekDay_Choice.WeekDay_ID = StaffMenuDetail.WeekDayID;

The query is creating the unnecessary joins and using INNER JOIN because they have been defined in Relationship Builder. You will have to manually adjust.

[nika.duncan]

Hey,
Thanks I have removed the relationship from WeekDay_Choice as stated and update query to read "RIGHT JOIN" as oppose to "INNER JOIN". It is generating the data now but when the "StaffMenuDetail" is checked when the data is inputted it is not updating the foreign keys "WeekDayID, AppetizerID,CarbID, ProteinID, SideID, and VegetableID" base on input. When I manually enter the data in those field based on input everything is displayed. I don't know how to fix that but once I am able to correct I will be able to generate the reports needed.

Any help will be appreciated.

Regards,
Nika

[Gicu]

Nica,
You have too sets of fields for the "choices" in your StaffMenuChoices table (i.e. AppetizerID and ApetizerChoice). Your combo boxes on the form are linked to the xxxChoice ones and they do populate. But your query user the first set (the xxxID). So you'll have to decide which ones you want to keep and act accordingly. You can modify the form to use the xxxID as control sources for the choice combos, or you can modify the query to use in your outer joins the xxxChoice instead of the xxxID fields.

Cheers,
Vlad

[Gicu]

Sorry, you have two sets of fields...

[June7]

Oops, I should have noticed that as well.

Recommend you eliminate the text xxxChoice fields from StaffMenuDetails and change the comboboxes to save the xxxID. Also, should only save Staff_UD and remove StaffName and Branch fields.

Since all the Choice tables are the same structure, really could be one table. Just have another field for the food category.

If you don't show the weekday name in the combobox list, there is no need to include the WeekDay_Choice table. Instead of saving tons of query objects, can build SQL statement directly in RowSource and RecordSource properties.

Staff name parts should be in separate fields: LastName, FirstName.