How do I generate a unique code from multiple tables?

[NKA]

I currently have 3 tables - one with week (WW) (1 - 5), one with month (MM)(Jan - Feb) and one with year (YY)(2003 - 2010). I dare say there is a better way...

What I am trying to do is generate a unique code (and store in a table) which concatenates YYMMWW for every possibility.

ie:
Year 2003; Month 01; Week 01 (030101)
Year 2004; Month 01; Week 02 (030102)
etc

I am sure this can be done very easily using a piece of code along the lines of "Do this while not EOF...."

Does anyone know/understand what I am looking for and how I can do this quickly?

NKA

The answer's always easy - if you know it! ;-)

 

[smitan]

Set rstY -> tblYear (Field F1)
Set rstM -> tblMonth (Field F1)
Set rstW -> tblWeek (Field F1)
Set rstR -> tblResult
do until rstY.eof
do until rstM.eof
do until rstW.eof
rstR.AddNew
rstR!ResultField=Right(rstY!F1,2) & rstM!F1 & _
rstW!F1
rstR.Update
rstW.MoveNext
loop
rstM.MoveNext
loop
rstY.MoveNext
loop
rstW.Close
rstM.Close
rstY.Close
Set rstW=Nothing
Set rstM=Nothing
Set rstY=Nothing

That's about it. Didn't test it.
Hans

 

[JeremyNYC]

I didn't check the code, but it'd be quite easy to just create a list of days in excel and then build a formula that converts it to your format in the column next to that and then copy and paste that into Access.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[TonyJollans]

Hi NKA,

If you already have the components in tables then, sticking in Access, a simple make-table query something like ..

SELECT Right([tblYY].[YY],2) & Right("0" & [tblMM].[MM],2) & Right("0" & [tblWW][WW],2) AS NewField
INTO NewTable
FROM tblYY, tblMM, tblWW;

.. should do it. Your might want to constrain it with a WHERE clause if your tables have other data as well.

Enjoy,
Tony

 

[NKA]

Hans (smitan)

I am trying to use the code you have provided, but am not sure what variables I need to declare to make it work (bit of a novice). Also, I am getting errors in the first 3 lines - I assume "->" should be "=". I am then getting an error when I change F1 to the fieldname (ie; year, month_id or week). It's telling me argument not optional!

Stuck now! But I believe this is the way I want to go with this. Can you help anymore?

Cheers

NKA

The answer's always easy - if you know it! ;-)

 

[smitan]

OK, I thought you knew how to make a recordset.

Dim rstY As Recordset

Set rstY = CurrentDb.OpenRecordset("Select * From tblYear&quot

That is the combination. Declare and Set.
Hans