I have been working on an already existing database, however the way it was created is not the best possible one.
My problem specifically is that the months are written as strings and therefore there are problems, for example in one string January could be "IAN." or "IAN" (the database is in Greek by the way IAN. stands for IANOUARIOS) or a mix between Greek and English letters.
I have decided to change the database so that the months are written as numbers (1 for January 2 for February and so on) and create a foreign key to a different table that will hold the relationships (key 1 will be equivalent to "IAN." key 2 will be equivalent to "FEB." etc)

I am facing two problems in order to implement this
a) When I create a new row I cannot populate it(I am used to working with excel and there I would use a macro to do all the work but here there do not seem to exist such facilities). I would do it by hand but unfortunately the database has well more than 12000 entries so that would take me ages. Can someone suggest a way to make the process a bit faster?
b) I have made a new database far shorter in order to test my program before using the original database. When the user accesses the form where she enters new data I have made a combo box in the place where the month should be which now holds the values for the month and has as a source the table of months which I have named MONTHS (:. However when I enter values for a new entry, nothing is written on the month column of the database (I kinda expected that) is there a way to make the form look up the MONTHS table get the value corresponding to that month and enter that value on the entry that is being written?


I'm sorry for any mistakes and confusing parts in my descriptions but English is not my mother language.

Thanks in advance

Hi,

   You need to do some ETL on this puppy. (Extract, transform, load). But the answer to this one is easy.

   First, do a query against the month column ONLY, and set Unique Values to Yes. This will give you one entry for ALL the possible ways each month was entered. This will give you information for writing the update query next.

   Create the new look up column in the table with the months, and link it to your new Month table.

   Then write your update query to update the new look up column to 1 WHERE Month = 'IAN' Or Month = 'IAN.' etc.

   It will take a few mintues to do this, but it will get done very quickly for each iteration. Just keep the designer open and keep changing the criteria and update until you are done. This makes the query designer work like SQL Query Analyzer.

HTH

mmcdonal

Sorry, I didn't answer your second question.

The thing is, if you have the months look up column now, you don't need the old month column, so no update is necessary.

If you still want to store this information twice, then do this for the Before Insert and Before Update events of the Form:

If IsNull(Me.MonthCombo) Or Me.MonthCombo = "" Then
   MsgBox "Please enter a month", vbCritical
   Else
   Me.OldMonthColumn = Me.MonthCombo.Column(1)
End If

This is assuming that the look up combo box has a number in Column 0, and the month name in column 1. Column 1 should be displayed on the form.

HTH

mmcdonal

Thanks again for the help mmcdonal and here's some more questions ( you didn't think you'd get rid of me that easy did you?(:)

I think you misunderstood what I said, in the form where you enter the month there will be a combo box where you select the month, however I do not wish for it to have the values 1 2 3 4 etc. in it, I want it to get the values from the MONTHS table, but when the user presses the button to store those values it should store 1 2 3 not IAN. FEB. etc

Create a month table with these columns:

tblMonth
MonthID - text
sMonth - text

Add these values (in the proper language, of course):

1 Jan
2 Feb
3 Mar
4 Apr
etc

Then create a combo box that looks up to this table.
Hide the PK column, and display only the month in the combo box.
Make sure the bound column for the combo box is 1.
Column Count: 2
Column Widths: 0"; 1"
Bound Column: 1

Like that.

When you look in the table, you will see the month displayed, so likewise it will be displayed int he form.

However, the table is only displaying that value. It is in fact holding the PK column.

You should make it text instead of number since you are not carrying out arithmetic functions on it. Since it is not a number, it will sort like this:

1
2
3
4
5
6
7
8
9
10
11
12

And not like this, which would happen if this were a number column:
1
10
11
12
2
3
4
5
6
7
8
9

Otherwise, if you used a number, you would stoere these values:

01
02
03
04
05
06
07
08
09
10
11
12

HTH

mmcdonal

P.S. The reason I used sMonth instead of Month as the column name is that this is a reserved word (in the English version of Access anyway) and you shouldn't name columns with reserved names.


mmcdonal

again thanks a lot for the help, it worked perfectly and since I'm on a roll with questions here's one more

I made a report where the month would show, currently it shows the month number however I want it to show the month name
I used the following expression in the control source
SELECT MONTHS.MNAME FROM MONTHS, SYN WHERE DB.MNTH = MONTHS.MNUMBER
but I keep getting an error
DB is my database MNTH is the month number in the database which refers to the MNUMBER in the MONTHS database

what am I doing wrong?

it seems I found a solution but it created other problems
I made the control source for the report to be a query containing the following statement

SELECT SYN.AR_MHT, SYN.ONOMA_EPWN, SYN.PATRWNYMO, SYN.KAT, MONTHS.Mname, SYN.CHK_Y, SYN.XARTIA
FROM MONTHS, SYN
WHERE (((SYN.CHK_MHN)=[MONTHS].[MNUMBER]));

this works ok by itself but the problem it created is the following

I have a form which takes arguments one is a month argument the other is a year argument, when the user enters a month and a year and clicks the button it represents all the clients with that month and that year, it works like this:

Wquery = "((DB.MNTH)= " & Me.Mhn1 & " AND (DB.YR) = " & Me.Yr1 & ")"
stDocName = "PAYMENTS"
DoCmd.OpenReport stDocName, acPreview, , Wquery

however when I run it this way I get a message to enter a value for MNAME or DB.MNTH

is there something I can do?

DOH I fixed it, it just goes to show that I should really pay more attention

well anyways I'm sorry for spamming and thanks again for the help