MS Access Query with Multiple Tables Sequential Numbering

[lildragon88]

Hi, I'm trying to set a sequential number into a query that I use for a report. The sequential number needs to count the number of options provided, based on a commodity, which is based on a quote and revision number. What I have so far doesn't count the options properly. I have included the query I have created that uses the self join method I found online. Does anyone know how I could accomplish this? Please and Thank you.

"SELECT Count(1) AS LineNum, tbl_OptionType.optionType, join_tbl_QuoteCommodity_OptionType.quoteOptionPrice, join_tbl_QuoteCommodity_OptionType.quoteOptionalComment, join_tbl_QuoteCommodity_OptionType.quoteOptionIsProvided, join_tbl_QuoteCommodity_OptionType.commodityId, join_tbl_QuoteCommodity_OptionType.quoteId, join_tbl_QuoteCommodity_OptionType.quoteRevisionId, tbl_Commodity.commodityName
FROM join_tbl_Quote_Commodity LEFT JOIN (tbl_OptionType RIGHT JOIN ((join_tbl_QuoteCommodity_OptionType LEFT JOIN join_tbl_QuoteCommodity_OptionType AS JTQC2 ON (join_tbl_QuoteCommodity_OptionType.optionType>=JTQC2.optionType) AND (join_tbl_QuoteCommodity_OptionType.commodityId=JTQC2.commodityId) AND (join_tbl_QuoteCommodity_OptionType.quoteRevisionId=JTQC2.quoteRevisionId) AND (join_tbl_QuoteCommodity_OptionType.quoteId=JTQC2.quoteId)) LEFT JOIN tbl_Commodity ON join_tbl_QuoteCommodity_OptionType.commodityId=tbl_Commodity.commodityId) ON tbl_OptionType.optionType=join_tbl_QuoteCommodity_OptionType.optionType) ON (join_tbl_Quote_Commodity.quoteId=join_tbl_QuoteCommodity_OptionType.quoteId) AND (join_tbl_Quote_Commodity.quoteRevisionId=join_tbl_QuoteCommodity_OptionType.quoteRevisionId) AND (join_tbl_Quote_Commodity.quoteCommodityId=join_tbl_QuoteCommodity_OptionType.commodityId)
GROUP BY tbl_OptionType.optionType, join_tbl_QuoteCommodity_OptionType.quoteOptionPrice, join_tbl_QuoteCommodity_OptionType.quoteOptionalComment, join_tbl_QuoteCommodity_OptionType.quoteOptionIsProvided, join_tbl_QuoteCommodity_OptionType.commodityId, join_tbl_QuoteCommodity_OptionType.quoteId, join_tbl_QuoteCommodity_OptionType.quoteRevisionId, tbl_Commodity.commodityName
HAVING (((join_tbl_QuoteCommodity_OptionType.quoteOptionIsProvided)=True))
ORDER BY tbl_OptionType.optionType, join_tbl_QuoteCommodity_OptionType.quoteId, join_tbl_QuoteCommodity_OptionType.quoteRevisionId, join_tbl_QuoteCommodity_OptionType.commodityId;"

I'm looking for:
LineNumber CommodityId OptionType QuoteId RevisionId
1 1 1 1 1
2 1 2 1 1
3 1 3 1 1
4 1 1 1 2
5 2 1 1 1
...etc
Thanks again

 

[lildragon88]

OH I forgot to add that I would prefer no temporary tables and cursors if possible, unless they are the faster search and retrieve method. Thank you

 

[lildragon88]

Okay, I've been able to come a little closer to the final result. Here is the code I have now:

SELECT Count(*) AS LINENUMBER, A.commodityId, A.quoteId, A.quoteRevisionId, A.optionType, A.quoteOptionPrice, A.quoteOptionalQuantity, A.quoteOptionalComment
FROM join_tbl_QuoteCommodity_OptionType AS A, join_tbl_QuoteCommodity_OptionType AS B
WHERE (((A.commodityId)>.[commodityId]) AND ((A.quoteId)=.[quoteId]) AND ((A.quoteRevisionId)=.[quoteRevisionId])) OR (((A.commodityId)=.[commodityId]) AND ((A.quoteId)=.[quoteId]) AND ((A.quoteRevisionId)=.[quoteRevisionId]) AND ((A.optionType)>=.[optionType]))
GROUP BY A.commodityId, A.quoteId, A.quoteRevisionId, A.optionType, A.quoteOptionPrice, A.quoteOptionalQuantity, A.quoteOptionalComment
ORDER BY A.quoteId, A.quoteRevisionId, A.commodityId;

The only problem I have now is that I need to filter the rows based on join_tbl_QuoteCommodity_OptionType.quoteOptionIsProvided = True. Does anyone have any idea where I can place that code in my query? Thank you.

 

[traingamer]

...
WHERE (((A.commodityId)>[B].[commodityId]) AND ((A.quoteId)=[B].[quoteId]) AND ((A.quoteRevisionId)=[B].[quoteRevisionId])) OR (((A.commodityId)=[B].[commodityId]) AND ((A.quoteId)=[B].[quoteId]) AND ((A.quoteRevisionId)=[B].[quoteRevisionId]) AND ((A.optionType)>=[B].[optionType]))[COLOR=red]
AND (B.quoteOptionIsProvided = True)
[/color]

traingamer