By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,317 Members | 1,295 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,317 IT Pros & Developers. It's quick & easy.

Looking for an easy way to make a local table from a linked table that has calculated

P: 2
I have a split database that is accessed by multiple users. The back end tables are rather large and contains calculated fields. Some users only need to view the table data but it is rather slow filtering. I would like to create a local copy using a make table but I can't include the calculated fields. I would like to find a work around for this issue.

Please Help!!!
Jun 23 '14 #1
Share this Question
Share on Google+
6 Replies


Expert 100+
P: 1,240
Jason, there is no way to help you without knowing more. What are these calculated fields? How often would they need to be refreshed? When you say "rather large", how large is that? What is the backend datatbase? Is it designed well and indexed properly? Has everything been done to assure the network speed is optimum?

That is to say, why fiddle with byte counting if you haven't got the low hanging fruit of gigabit speed hardware set up properly. I'm sure someone here can point you to a tutorial on DB/network optimization. Start there and worry about making local copies of the data only as a last resort.

Jim
Jun 23 '14 #2

P: 2
lets just simplify. forget about the split database, backend/frontend, size mumbo jumbo.
I have a table that I want to copy. I would like to use the make table query but it will not work if there are calculated fields in the original table. I can use the make table query if I exclude the calculated fields, but I want to include them.

The table has three fields " Start Meter" , "End Meter", and " Actual Length"

the Actual Length field is calculated with ABS("Start Meter" - "End Meter" )

can I make an exact replica of this table using the make table query?
Jun 23 '14 #3

twinnyfo
Expert Mod 2.5K+
P: 3,482
jason,

If you want to make the table, but can't include the calculated field, then don't include that field. Personally, I try to avoid any type of calculated field, unless I have to use the calculated data more frequently than the non-calculated values.

Although, if you want to transfer the data without the calculation, it is as simple as:

Expand|Select|Wrap|Line Numbers
  1. SELECT tblTableName.* INTO tblNewTableName
  2. FROM tblTableName;
Are there specific issues you are coming acros that are preventing you from doing this?

Using VBA, you can actually create a table with the same properties as your first Table, but that is more advanced than what you may be asking for.
Jun 23 '14 #4

Expert 100+
P: 1,240
It sounds like you are describing a query, not a table. In any case, if you make a table two table columns and a forumula, the result will be 3 table columns. Static data in the 3rd column, so if column 2 End Meter changes, column 3 will not change along with it. But I see no advantage to storing that 3rd column when the calculation is so simple.
Jun 23 '14 #5

twinnyfo
Expert Mod 2.5K+
P: 3,482
True, Jim. But, I agree most with your post #2, not to re-create the table in the first place. That will not speed things along, but may instead cause bloating of the DB.
Jun 23 '14 #6

NeoPa
Expert Mod 15k+
P: 31,768
Jason,

Your post (#3) does clear up some questions but you'll see there's still some confusion. The advice given is pretty sound generally, but it would help to know if you're referring to a Query rather than a Table, or otherwise what type of table you're referring to that handles calulated fields.
Jun 23 '14 #7

Post your reply

Sign in to post your reply or Sign up for a free account.