Query Results not as epected

[e51lrrp]

Why would my query return the PK results instead of the field data?
Any suggestions?

Really new at this but all my other queries work properly...



The query involved is the qryTrophiesDetails. I have attached my database if analysis.

Appreciate any suggestions.
Sharon

[June7]

Because the field has the CustomerID. If you want related customer info then include tblCustomers in the query.

[e51lrrp]

I put the tblCustomer in the query but it did not make a difference. Any other suggestions?
S

[June7]

I just noticed the query is a Totals - why? No fields have GROUP BY in the Total row and none have an aggregate calc.

Consider:

SELECT tblCustomer.*, tblOrders.*, tblEngravingTrophiesDetails.*
FROM tblCustomer RIGHT JOIN (tblOrders LEFT JOIN tblEngravingTrophiesDetails ON tblOrders.ID = tblEngravingTrophiesDetails.OrderID) ON tblCustomer.ID = tblOrders.CustomerID WHERE TypeOfOrder="Trophies";

[Dal Jeanis]

Copy your query to a new query, change to sql view, and then paste this in its place. Should work.

SELECT 
  T1.CustomerID, 
  TC.FirstName & " " & TC.LastName AS Custname,
  TE.OrderID, 
  T1.TypeOfOrder, 
  TE.ItemID, 
  TE.TrophyBaseStyle, 
  TE.TrophyQuantity, 
  TE.ColumnStyle, 
  TE.ColumnColor, 
  TE.ColumnLength, 
  IIf([ColumnStyle]="Wide",0.15,0.11)*[ColumnLength] AS ColumnPrice, 
  TE.Topper, 
  2 AS TopperPrice, 
  TE.CupStyle, 
  TE.CupStylePrice, 
  TE.Trim, 
  TE.TrimQuantity, 
  [TrimQuantity]*0.75 AS TrimPrice, 
  TE.BasePrice, 
  TE.AssemblyPartsPrice, 
  TE.[Personalization/SpecialInstructions], 
  [TopperPrice]+[CupStylePrice]+[TrimPrice]+[BasePrice]+[AssemblyPartsPrice]+[ColumnPrice] AS TrophyPrice, 
  [TrophyPrice]*[TrophyQuantity] AS ExtendedTrophyPrice, 
  [ExtendedTrophyPrice]*0.06 AS TrophyTaxes, 
  [ExtendedTrophyPrice]+[TrophyTaxes] AS TrophyTotal
FROM 
 ((tblOrders AS T1 
   INNER JOIN tblEngravingTrophiesDetails AS TE 
   ON T1.ID = TE.OrderID)
   INNER JOIN tblCustomer AS TC 
   ON T1.CustomerID = TC.ID)
WHERE (((T1.TypeOfOrder)="Trophies"));

[e51lrrp]

Thanks but this is way above my grade level. The query that I am having trouble with is the qryTrophiesDetails. It only produces the autonumber not the data. Not sure why. Appreciate all your help but still struggling with this....
Thanks...S

[June7]

I already told you the why.

You have two queries suggested as solution.

Did you try either?

Copy/paste it into SQL View of query builder.

[e51lrrp]

I think I have a clue but not sure how to fix my problem. If you look at the form I am using, frmTrophyDetails you will see that the field giving me a problem are autofills. I had help with this and it is working but I still would like the query to product the actual data if possible. Does anyone have any suggestions after you study my form....really just beginning to use these feature and not completely sure what I am doing. I guess that is pretty obvious.
Thanks for any assistance in this matter.
Sharon

[June7]

The query will not be useful as the RecordSource for data entry form.

A form should be used to enter/edit data for one table.

tblOrders and tblEngravingTrophiesDetails have a 1-to-many relationship.

Record must exist in tblOrders before order details records can be created.

Use form/subform arrangement.

Main form bound to tblOrders with a combobox to select customer, subform bound to tblEngravingTrophiesDetails with combobox(es) to select product data.

[e51lrrp]

June7....I am sorry you are frustrated with me but I do not feel that I can use someone else's work without understanding what is being accomplished. I'm sure it would work but I could never repeat the process if something went wrong. I did fix the other query regarding the totals and it works. Thanks for the suggestion.

[June7]

What someone else's work? You did not build the db? If that form is not intended to be used for data entry/edit, just to list records, then the query should be okay.

[e51lrrp]

June7....what I was trying to say was that I did not feel comfortable copying and pasting your work in to my db when I didn't understand what the coding was doing. Yes I have developed the entire system. I guess I am used to looking at the query results and seeing what is being applied to the tables. Remember I don't fully understand what Access is doing. I do understand that you must trust the system. Like for instance that the data is stored in many places and not in a large database. Most of the system I have worked with are like flat files and big databases that I would use...I worked for GM.

Hope I have sort of cleared up my confusion. I just want to understand WHY the query gives my the results it does...
s

[June7]

Copy/paste suggested SQL into SQL View of a new query. Switch to Design View. Study it until it makes sense, use it if you like it.

That won't hurt your existing design.

It is a very simple, basic query. You can build it yourself with the query builder.

The results you currently get are because the customer info is not included in the query. This is what primary and foreign key fields are for - to join tables so associated info can be viewed.

[Dal Jeanis]

e51 - It's very good and very ethical that you want to understand what has been done. I never recommend that people implement solutions without understanding how to support hem. That's a BAD idea.

However, that's not an excuse for not trying a solution that has been handed to you. If my code works, then you should be able to switch it back into design view and see what a correct solution looks like. Then ask use questions.

If my code does NOT work, then we will have BOTH learned something.

If you'd like to compare my SQL to your original one, then copy your original query to a second copy, and right-click each of the tables to bring up the properties box. Change the table alias for each table for that query to match the alias I gave that table in my SQL code. Then switch to SQL view, and you can compare to see what terms I actually changed, as opposed to what I did just to make it "pretty".