Move unique values from one table to another

[smellyalater]

hey guys i am wanting to move unique values from one table to another.

My table has

ID, Number, date/time, unique

the number field will have mobile numbers, i want to be able to press a button to run some vba code and it will find the unique numbers and put a 1 in the unique field if that makes sense?

Any good examples or best way to approach this guys?

Thanks!

 

[plog]

You don't.

You've described something that doesn't need to be done in a properly structured database. Perhaps you can explain the big picture to us and we can help you achieve the ultimate goal instead of this intermediate step.

 

[smellyalater]

You don't.

You've described something that doesn't need to be done in a properly structured database. Perhaps you can explain the big picture to us and we can help you achieve the ultimate goal instead of this intermediate step.

There is no real big picture.

There is just multiple mobile numbers going into a table loaded every hour and I just want to run some code to put a 1 beside the first unique one and 0 beside the rest if that makes sense?

Thanks

 

[plog]

This sounds like a job for a simple query, not a system of adding 1's to a table. Again, if I knew the big picture I could help you in the most efficient manner--I suspect your company doesn't stay in business simple putting 1's next to records in a table.

Could you provide me with some sample data? I would need 2 sets:

A. Sample starting data from your table. Include table and field names and enough data to cover all cases.

B. Expected results. Show what you hope to end up with based on the data you provide in A.

 

[smellyalater]

Hi Plog

Thanks for the fast replies.

Here is what I am going to start with in my table:

ID, number, date/time, unique
1, 07712312312, 14/04/2016 13:02:34, blank
2, 07712312312, 14/04/2016 13:21:13, blank
3, 07712312312, 14/04/2016 13:23:56, blank
4, 07712312312, 14/04/2016 13:02:34, blank

I want to get this:
ID, number, date/time, unique
1, 07712312312, 14/04/2016 13:02:34, blank
2, 07712312312, 14/04/2016 13:21:13, 1
3, 07712312312, 14/04/2016 13:23:56, 0
4, 07712312312, 14/04/2016 13:52:34, 1

I want to use vba to do a datediff to make sure there is 15 minutes between each number coming in, so that it is unique again and gets a 1. If it is less than 15 minutes then it is not classed as unique therefore gets a 0, then move the the next number do the checks and put the values in.

Let me know if this explains this a bit a better?

 

[plog]

This does not require a 'unique' field in your table-- this can all be done with a query. However, since you didn't provide a table name and your field names are poorly chosen (number is a reserved word and date/time contains a slash) I'm going to give you generic advice to achieve this.

You need to build a query and create a caclualted field that uses the DMax function (http://www.techonthenet.com/access/functions/domain/dmax.php) to retreive the prior time for each record. Then you can use the DateDiff (http://www.techonthenet.com/access/functions/date/datediff.php) to determine the minutes between the prior record's time and the current record's time. If its over your threshold, you put a 0, if not you put a 1 and if there is no prior time you leave it blank.

 

[smellyalater]

This does not require a 'unique' field in your table-- this can all be done with a query. However, since you didn't provide a table name and your field names are poorly chosen (number is a reserved word and date/time contains a slash) I'm going to give you generic advice to achieve this.

Sorry Plog I just wrote that up quickly.

The table names are:

tbldata and the actual field names are ID, mobile1, dtm

tbldataunique and the fields are ID, mobile1, dtm, unique

I want to do something like this:

If datediff(minute,previousTIME,currentTIME) >15
Update table
Set unique = 1
Else
Update table
Set unique = 0

Hope that makes more sense than before?

 

[plog]

Nope, make the same amount of sense. Does my advice make sense? Because it seems you are actively trying to not hear it.

At least 3 times I have explained how a query is what you need, every time you come back with references of using VBA to update your table. Perhaps you should post in the Modules & VBA section and someone there will give you the code you need to do this in an inefficent manner.

 

[smellyalater]

I think I will do that Plog, thank you for you help but I would rather do it through VBA.

Thanks!

 

[The_Doc_Man]

There is at least one conceptual fly in your ointment. When you say you want to mark the first entry that is unique, here's the catch... there IS no first entry.

You need a query that includes an ORDER BY to define the order of entries to be considered, because in the absence of an explicit ordering, queries are based on set theory which ACTS LIKE everything happens at once. You have at least two potential ordering candidates - the record number and the date/time field.

Depending on the selected ordering, ANY of the records with the same number field could be the candidate for being marked as unique.

The other part of this question is why bother to move doodlum-squat? Opening a table gives you a recordset. Opening a SELECT query gives you a recordset. Assuming you could mark the "right" marker, do you need anything else from that table, like the time of the first call on record for that number?

If all you needed was the unique numbers, do a SELECT DISTINCT mobile1 FROM tbldata ; = which would give you the unique numbers.

If you wanted the earliest entry for each number,

SELECT mobile1, MIN(dtm) AS firstdtm FROM tbldata GROUP BY mobile1 ;

If you wanted more than that, you are probably looking at layered intermediate queries.

Your problem description changed from its initial implications when you added that snippet of code that breaks your day into 15-minute intervals.

I'm going to toss one of the Old Programmer's Rules at you - if you can't do it on paper, you can't do it in Access. Which SHOULD be taken to mean that if you can't describe your problem well enough in English (or pick your other native language if not English), then you aren't ready to write code anyway. You HAVE to be able to articulate the exact intent of the code, whether you are doing it by yourself or trying to explain it to us.