Simple Query Wizard - Getting Error

[mtrueblood33]

Hello,

I am trying to do a calculation between an item in 1 table and another item in another table. I want to do [BackCost = (TicketQty/1000) * 15]; where "BackCost" is in [tbl_services] and "TicketQty" is in [tbl_ticket].

Every time I launch the query wizard (Simple Query Wizard) and select "BackCost" from tbl_services and "TicketQty" from tbl_ticket, I get the following error:

"The wizard is unable to open your query in Datasheet view, possibly because another user has a source table open in exclusive mode. Your query will be opened in Design View."

The database is on my local machine, and the database is not open by another user. I have read through some posts and checked for that error already. I was able to figure out how to do a calculation within 1 table, but now need to do a couple more calculations across a couple different tables. I sort of understand the basics of Access at this point.

Your help is greatly appreciated!

Thanks,
Matt

 

[John Big Booty]

I suspect that what you want is an update query, that will update BackCost to (TicketQty/1000) * 15.

However having said that, I would suggest that you read up on the perils of storing calculated values. In general it is considered bad practice to store values that are derived from calculations using values that are already stored in the DB. The best approach on the whole is to calculate such values only when you need to display them.

 

[mtrueblood33]

Hello. Thanks for the response.

The information is displayed. This database is used to gather info from a user on a website. The information is later displayed on the verification page before ordering.

I need to be able to multiply $10 times by how many 1,000 tickets are being ordered. The ticket quantity is stored in one table, and the calculation for such in another table.

Can I join the two tables together, to then create the query?

Thanks,
Matt

 

[mtrueblood33]

That "$10" was supposed to be "$15".

Thanks,
Matt

 

[John Big Booty]

OK. I think I see what you wan to do.

Try putting

Back Cost: (TicketQty/1000) * 15

in a blank field row of your query design grid.

 

[mtrueblood33]

Great thanks. I will try that.

Just so that I understand you completely, when you say "query design grid", where exactly do you mean?

Thanks,
Matt

 

[John Big Booty]

What you see when you have the query open in design view.

 

[mtrueblood33]

Actually, I think I tried that. If I know what you mean. It didn't work correctly because there wasn't a connection between the two tables (tbl_ticket [where TicketQty is] and tbl_services [where I am doing the calculation with BackCost ])

 

[John Big Booty]

Any chance of posting a cut down version of your DB, so I can see what's (not) going on?

 

[mtrueblood33]

The DB is 1,344 KB. Is that too big to post on here?

 

[John Big Booty]

You are allow 785.9Kb as a Zipped up load try do a compact and repair before zipping it and see what you get. Otherwise make a copy and strip out the portions that are not relevant to your current problem. You could also delete the bulk of the records to make it smaller, either way compact and repair before zipping.

 

[mtrueblood33]

I am going to slim it down and post it here in a second. I really appreciate your help!

Thanks,
Matt

 

[John Big Booty]

I'll watch out for it

 

[John Big Booty]

If you are using '07 are you able to save it as an earlier version, as I don't have access to '07 on this machine.

 

[mtrueblood33]

Ok. Here is the slimmed down database.

Thanks,
Matt

 

[John Big Booty]

Play fair; I'm going to need just a little bit of data in there, otherwise the whole exercise become rather pointless.

 

[mtrueblood33]

Yes. I will save it as an earlier version and resend it.

 

[mtrueblood33]

Well it's currently saved as 2002-2003 version. Do you need it in 2000?

Thanks,
Matt

 

[mtrueblood33]

Sorry I deleted the records to slim down the file size. How much data do you need?

 

[John Big Booty]

Half a dozen or so records should be enough, just so I can get a feel for what the data should look like.

The version you posted was fine. I just wasn't sure if you where using '07 or an earlier version.