Hi DBguy, Any suggetions on this concatention issue?Hi. I could be wrong, but I think the original question about Default Value is solved. I think this is about Cascading Combobox now.
Thanks.
Hi DBguy, Any suggetions on this concatention issue?Hi. I could be wrong, but I think the original question about Default Value is solved. I think this is about Cascading Combobox now.
Siegfried,
Is your latest database still the one attached to post #32?
Also, your form is called PurchaaseOrderItemsFSub was that intended to be a subform within PurchaseOrderF??
I'd like to see a scenario that starts with a selection and proceeds through to the response/answer you are expecting. Then, we can be looking at and testing the same thing.
SELECT PurchaseOrderT.POID
,PurchaseOrderItemT.POItemID
,PurchaseOrderT.PONumber
,PurchaseOrderItemT.ProductID
,PurchaseOrderItemT.ProductModel
,PurchaseOrderItemT.UnitPrice
,Count(PurchaseOrderItemT.Quantity) AS CountOfQuantity
,PurchaseOrderItemT.CurrencyID
,CurrencyT.CurrencyName
,ExchangeRateT.ExchangeRate
,ExchangeRateT.ExchangeRateDate
FROM (
(
PurchaseOrderT INNER JOIN PurchaseOrderItemT
ON PurchaseOrderT.POID = PurchaseOrderItemT.POID
) INNER JOIN ExchangeRateT
ON (PurchaseOrderItemT.ExchangeRateID = ExchangeRateT.ExchangeRateID)
AND (PurchaseOrderItemT.ExchangeRateID = ExchangeRateT.ExchangeRateID)
)
INNER JOIN CurrencyT
ON PurchaseOrderItemT.CurrencyID = CurrencyT.CurrencyID
GROUP BY PurchaseOrderT.POID
,PurchaseOrderItemT.POItemID
,PurchaseOrderT.PONumber
,PurchaseOrderItemT.ProductID
,PurchaseOrderItemT.ProductModel
,PurchaseOrderItemT.UnitPrice
,PurchaseOrderItemT.CurrencyID
,CurrencyT.CurrencyName
,ExchangeRateT.ExchangeRate
,ExchangeRateT.ExchangeRateDate
,ExchangeRateT.ExchangeRateDate;
POID | POItemID | PO number | product | model | unit price | CountOfQuantity | CurrencyID | currency | RoE: | Date: |
---|---|---|---|---|---|---|---|---|---|---|
1 | 7 | AEANT-2020.001 | 8 | X40 | 45.00 | 1 | 1 | EUR | 0.9162 | 01-Mar-20 |
1 | 9 | AEANT-2020.001 | 18 | T5 | 250.00 | 1 | 4 | USD | 1 | 01-Jan-20 |
2 | 3 | AEANT-2020.0002 | 6 | MX3 | 99.00 | 1 | 1 | EUR | 0.8054 | 01-Feb-20 |
2 | 6 | AEANT-2020.0002 | 3 | T24i | 230.00 | 1 | 1 | EUR | 0.9162 | 01-Mar-20 |
2 | 8 | AEANT-2020.0002 | 6 | MX3 | 99.00 | 1 | 1 | EUR | 0.5804 | 01-Apr-20 |
3 | 2 | AEANT-2020.0003 | 2 | M720q | 1,200.00 | 1 | 1 | EUR | 0.8423 | 01-Jan-20 |
3 | 4 | AEANT-2020.0003 | 24 | X500 | 150.00 | 1 | 1 | EUR | 0.9162 | 01-Mar-20 |
3 | 5 | AEANT-2020.0003 | 4 | Qwerty US | 20.00 | 1 | 1 | EUR | 0.5804 | 01-Apr-20 |
I was editing my last post when you responded. I think your yes it is refers to the latest database.
What about the form and subform? It seems your subform is independent in the database, but typically we see a subform control on the main form (PurchaseOrderF here).
In your Exchange rates the # 8 shows EUR but the rate is 1------doesn't seem correct to me????
When I review your PurchaseOrders and PurchaseorderItem in this query
(set up so I can relat items/orders to your form/subform info)
Code:SELECT PurchaseOrderT.POID ,PurchaseOrderItemT.POItemID ,PurchaseOrderT.PONumber ,PurchaseOrderItemT.ProductID ,PurchaseOrderItemT.ProductModel ,PurchaseOrderItemT.UnitPrice ,Count(PurchaseOrderItemT.Quantity) AS CountOfQuantity ,PurchaseOrderItemT.CurrencyID ,CurrencyT.CurrencyName ,ExchangeRateT.ExchangeRate ,ExchangeRateT.ExchangeRateDate FROM ( ( PurchaseOrderT INNER JOIN PurchaseOrderItemT ON PurchaseOrderT.POID = PurchaseOrderItemT.POID ) INNER JOIN ExchangeRateT ON (PurchaseOrderItemT.ExchangeRateID = ExchangeRateT.ExchangeRateID) AND (PurchaseOrderItemT.ExchangeRateID = ExchangeRateT.ExchangeRateID) ) INNER JOIN CurrencyT ON PurchaseOrderItemT.CurrencyID = CurrencyT.CurrencyID GROUP BY PurchaseOrderT.POID ,PurchaseOrderItemT.POItemID ,PurchaseOrderT.PONumber ,PurchaseOrderItemT.ProductID ,PurchaseOrderItemT.ProductModel ,PurchaseOrderItemT.UnitPrice ,PurchaseOrderItemT.CurrencyID ,CurrencyT.CurrencyName ,ExchangeRateT.ExchangeRate ,ExchangeRateT.ExchangeRateDate ,ExchangeRateT.ExchangeRateDate;
I see this, is this what you expect just from related tables?
On Order2 1 MX3 has Exch .8054, and the other .5084 ----correct?
QJEDOrder_Items_Exch
POID POItemID PO number product model unit price CountOfQuantity CurrencyID currency RoE: Date: 1 7AEANT-2020.001 8X40 45.00 1 1EUR 0.9162 01-Mar-20 1 9AEANT-2020.001 18T5 250.00 1 4USD 1 01-Jan-20 2 3AEANT-2020.0002 6MX3 99.00 1 1EUR 0.8054 01-Feb-20 2 6AEANT-2020.0002 3T24i 230.00 1 1EUR 0.9162 01-Mar-20 2 8AEANT-2020.0002 6MX3 99.00 1 1EUR 0.5804 01-Apr-20 3 2AEANT-2020.0003 2M720q 1,200.00 1 1EUR 0.8423 01-Jan-20 3 4AEANT-2020.0003 24X500 150.00 1 1EUR 0.9162 01-Mar-20 3 5AEANT-2020.0003 4Qwerty US 20.00 1 1EUR 0.5804 01-Apr-20
Hmmm? I'm not sure how to answer that --it could be anything if you found some code somewhere. However, I'm curious as to why you show the EchangeRateID and not the Rate and Date. It seems more hidden and if I were using the form/subform, I think I'd like to see the Rate and Date for this currency --but I'm not using it so you and users must decide.
You want the exchange rate to be associated with the received date, not the OrderDate correct? Is that a rule that you can program? I realize your current approach is flexibility that you can/code update.
Just trying to get you to say how "frozen" your approach/design/business rules are.
I notice your POnumbers have different number of 000 after the dot "." --is that a typo or significant?
SELECT CurrencyT.CurrencyName
, ExchangeRateT.ExchangeRateDate
, ExchangeRateT.ExchangeRate
FROM ExchangeRateT INNER JOIN CurrencyT ON
(ExchangeRateT.CurrencyID = CurrencyT.CurrencyID)
WHERE (((CurrencyT.CurrencyName)= "EUR"));
Hi jdraw,Siegfried,
I realize you are designing and testing components.
Do you have a detailed requirement list, or a list of business rules that the database should support.
What exactly is your role in this project/company? Is there a project development and implementation plan? Do you have some tester /user/focus groups to assist your efforts?
Just me being a little nosy --trying to get some context.
A little soap box here ---focus on WHAT is required, then determine HOW it might be implemented by reviewing some options.
Good luck.
Hi jdraw,Siegfried,
I did a little experimenting. I put copies of your form and subform together
(note the J in the form names)
see: MockupOfFormSubform.png
Then a combo box (combo14) and a text box (text16).
see MockupComboSelected.png
The combo rowsource fo testing the concept is
Code:SELECT CurrencyT.CurrencyName , ExchangeRateT.ExchangeRateDate , ExchangeRateT.ExchangeRate FROM ExchangeRateT INNER JOIN CurrencyT ON (ExchangeRateT.CurrencyID = CurrencyT.CurrencyID) WHERE (((CurrencyT.CurrencyName)= "EUR"));
and for testing I added a msgbox to show what was happening
see MockupComboWithMessageForTesting.png
This is a mockup the combo rowsource is made up.
The png files are in the attached zip.
I think you should review the requirements and firm them up a little, then have readers propose at some options for HOW to do it.
Feedback is welcome.
Good luck.
thanks for all the useful info.Another thing you can do to help with analysis and to confirm your understanding of the requirement is to build a "demonstration prototype". Something with a few forms and buttons that hints to the overview of the project and some of the major components. The buttons do nothing more than change from one form to another or pops up some message of what happens here, or ho is responsible for something, or signifies some status change or event. It doesn't do much in detail, but it shows the logic flow and some significant steps/stages in the overall project.
eg. Consider a loan/grant application ( Section A)
Applicant is entered into the system
Details gathered
Msg re insufficient info
Application reviewed
Status updated
Preliminary project info supplied
Vet the supplied info
Review is successful or Review identifies issues an return to statusX
If successful, identify loan/grant conditions make offer to applicant
Applicant accepts/rejects/wants to negotiate
Status updated
End of this section A of prototype
This sort of thing is used to get feed back, clarify business process and requirements.
Let us know how your project progresses. We're here if you have questions.
Good luck.