Moving a single field from one table to another (1 Viewer)

[Niroth]

Hi guys,

I have another question I need some help on, how can I transfer a field from one table (or query) to another? So I have a sales table with a field "counter" which keeps count of the sales for each customer (cust A 1,2,3,4, custB 1,2,3,4 and so on). Now I think storing it this way is inconvenient big time, and I thinking of storing just one value in my customer Table, but first I need to move the maximum value of this field into my customer table to keep continuity. How would you do it?

Niroth

 

[DCrake]

If you are simply storing the number of sales transactions you have then why not simply create a query to do that instead of storing the value in a field. More accurate.

David

 

[Niroth]

Can't do that because we can create a sale and then delete that sale for some reason later, but the number will have to keep increasing, that's why I created the field counter to keep count instead.

 

[Atomic Shrimp]

So there's no way to check what you sold the customer in the past? What does the system do then?

Edited to add:

Sorry - maybe that was a bit harsh. But I think you may need to broaden the scope of this problem - updating a calculated value into a field in a table isn't generally a great idea, because a)you'll end up doing just as much work to update it as you would to calculate it on the fly when you want it and b)something will one day go wrong and the stored calculated value will be incorrect, and difficult to know how to amend.

You said that historical sale records get deleted... why? If you want them to disappear out of view on some form or other, you could set it up to only show records within a date range, or add a yes/no field to the table to mark dead records, then set your form up not to show them.
If you keep all the historical sales records, it's easy to calculate the accumulation of them whenever you want it.

 

[Niroth]

Thanks, I have thought of those problems, but in this case, it's simply more suitable.

And I might have found the solution here for those who might be interested later on:
http://www.tek-tips.com/viewthread.cfm?qid=1539067

 

[Atomic Shrimp]

That code looks like it's creating new fields - sort of like a crosstab, but setting it into the table structure - which might not be what you want.

Could you just clear up a bit of confusion I have with your data... are you saying you've got a table containing a field in which there are multiple, comma-separated values? - and you want to pick the largest/latest of these values out of the field, then store and update only that value?

 

[Niroth]

No. That was meant as different records with consecutive values. I haven't have time to implement the code yet, but from what I could see, it seems to be what I need - copy the value from one table, and create a new table with value from that field. I don't think it's crosstab.