Expression data selection trouble

[moocho992345]

I have this expression:


[Tickets Sold] * [Ticket Price]
in the TotalCash column

from this table.


The expression is supposed to take the ticket price times the tickets sold to get a total, But as you can see the math is wrong. Ticket price is linked to the following table table. The connection is ID>TicketPrice. It's multiplying by the ID of the ticket Price. I need to find a way to get the math to work correctly.

The ticket price field is a combo box.

[JoeM]

Can you post the SQL code for your query (switch your query to "SQL View" and copy and paste the code here)?

[moocho992345]

The expression is in the table design, not a query. I don't think there is an SQL view option for tables.

[JoeM]

I know that newer versions of Access allow calculated fields in Tables, but I would recommend against using them (as most programmers do). For one thing, it is not compatible with any other database program (like SQL), so if you ever needed to upgrade to SQL, you would have issues.

I would recommend doing all calculations like this in a calculated field in a query. There is no need to store anything at the table level which can easily be calculated in a query.

[CJ_London]

calculated fields in tables can only reference fields in that record, so sounds like you have committed the second sin of table design and used a lookup for your prices thinking this is a way round it. You see a price so you think it is a price - it isn't - it is a looked up price - your calculation is multiplying quantity x the priceID.

Take JoeM's advice. Don't use calculated (or lookup) fields so you can see what you actually have as data, not what is presented.

[moocho992345]

I made a query with the calculations. Here is the SQL:

SELECT TicketEventWithMyData.TEID, TicketEventWithMyData.SellerName, TicketEventWithMyData.EventName, TicketEventWithMyData.EventDate, TicketEventWithMyData.PatronType, TicketEventWithMyData.RollColor, TicketEventWithMyData.TicketPrice, TicketEventWithMyData.RollStartNum, TicketEventWithMyData.RollEndNum, TicketEventWithMyData.StartCash, [RollEndNum]-[RollStartNum] AS TicketsSold, [TicketsSold]*[TicketPrice] AS TotalCashFROM TicketEventWithMyData;

[JoeM]

So, does it work now?

If not, it may not like that you are using aliases for fields you just calculated in other calculations in the same query. If that is the case, replace this:

[TicketsSold]*[TicketPrice] AS TotalCash

with this:

([RollEndNum]-[RollStartNum])*[TicketPrice] AS TotalCash

[moocho992345]

I replaced it. The wierdest thing happened though. It didn't change. It should pull the calculated value from ([RollEndNum]-[RollStartNum]). I get this:

The ticketsSold is corect, but the TotalCash still used the ID numbers. What???

[JoeM]

Wait, I think I know what is going on. You said that the Ticket Price is a combo box field from another table, right?
Take a look at the Properties of this field in your table. On the Lookup tab, is the Bound Column set to 1 or 2?
If your price is the 2nd field in your Row Source (ID field is 1, TicketPrice is 2), then the Bound Column needs to be set to 2.

Note that in a LOOKUP table where all your values have to be unique, there usually isn't a need for an ID field. If the LOOKUP table only has one field, then this won't ever be an issue. If you do need/want an ID field for some reason, sometimes they are easier to handle if you make the ID field the last field instead of the first. That will also avoid this issue.

[CJ_London]

as identified in post #5

[moocho992345]

The bound column just seems to change the appearance of the data. When the bound column is 1, it shows what its actually multiplying by, the ID. When the bound column is 2, it shows the ticket price, but it's still multiplying by the ID.

[JoeM]

How about getting rid of the ID field in that table altogether (or at least make it the last field in your table instead of the first)?
As I mentioned in my previous post, it may not even be necessary.

[moocho992345]

Switching the the ID to the last field just switched it around so that if the bound column is 1, it show the ticket price and If it's 2, it shows the ID. The value it's multiplying by doesn't seem to change. I can't delete it because it's a primary key.

[JoeM]

Make the Price key the Primary Key, and then you should be able to delete it.

[moocho992345]

Alright I tried making the price key the primary key and It screws up the combo box, so I changed the ID, the primary key into a currency. That allowed me to enter numbers like 2.5 and 3.5 in so I could match the ID to the price. The only issue with this however is that when you go to use the combo box to select a non whole number like 2.5, it changes it into 2. I wish I could same the same happened for 3.5, but it rounded that up to 4.