Compare Field Names

[Rjc8513]

I have a table named "WEEKS", consisting of one field ([WEEK NO]) that contains the week numbers of each week of the year, 1-52.

Our fiscal year always begins on Oct. 1 and ends the following Sep. 30. Our work weeks begin on Sat. and end the following Fri. So, when a month ends in the middle of a week, such as Feb., the portion of of the week that is in Feb. is Week No. 22A. The portion of the week that begins Mar. is Week 22B. Together, these "split" weeks make up Week 22. Hope this makes sense.

The "WEEKS" table is the "master" for the current year. When I import a year's worth of data I need to be sure that the week numbers in the imported file match the "master" week numbers in the "WEEKS" table. For example, if there is a Week 26A and a Week 26B in the import file but there is only a Week 26 in the "master" table, I need to combine the data in the 26A and 26B fields in the import file and rename the combined field as Week 26.

I have tried to loop through the values in the [WEEKS NO] field in the "WEEKS" table but I don't know how to structure a comparison and how to combine the data.

Thanks in advance for any help anyone can provide.

 

[BoxHead]

You could use the Val() function to eliminate the A or B and group on that field.

Make a query of all fields from the imported table. Click the Epsilon symbol to show the Totals row in the query grid and change the [WEEK NO] field to read WeekNum:Val([WEEK NO])

In the Totals Row, leave that field Grouped, but change the Group By default value to Sum on the fields you want to combine.


HTH


John




When Galileo theorized that Aristotle's view of the Universe contained errors, he was labeled a fool.
It wasn't until he proved it that he was called dangerous.

 

[Zion7]

As boxhead said,
a couple of other search suggestions may be;


from the import file

If IsNumeric(Mid( WEEKNo,2,1)) Then
intWeek = Left(WeekNo,2)
Else
intWeek = Left(WeekNo,1)
End If



From tblWeeks

If DCount("pkWeeksID","tblWeeks","WeekNo Like '" & intWeek & "*'") = 1 Then

combine ...

 

[Rjc8513]

Thanks for the responses BoxHead and Zion but, I don't think that will solve the problem. Some weeks must remain as "A" and "B" weeks, while one or two must be combined, depending upon the "master list" contained in the "WEEKS" table.

So, I was looking more for code that would loop through the field names in the import file and compare each field name with each of the records in the "WEEKS" table. Then if Week 26 exists in the "WEEKS" table but 26A and 26B exist in the import file, 26A and 26B in the import file would be combined.

Thanks again for your help.

 

[Remou]

How would this suit?
- Import the table
- Set up a query with the weeks table to get 'no matches'
- I'm not sure about this bit, but perhaps the val function suggested by boxhead could be used at this stage to update 26As and 26Bs to 26, etc.

 

[BoxHead]

Rjc8513,

To clarify, you have some 'split' weeks in the master table. If a split week in the import table exists as a split week in the master table, you want it to be included as a split week. If a split week in the import table does NOT exist in the master table, you want he split weeks combined.

Is that right?

You could use DCount to check for the presence of each week number from the Import table in the Master table.

WeekNum:Iif(DCount("[WEEK NO]","WEEKS","[WEEK NO] = '" & [myImportWeekNum] & "'")>0,[myImportWeekNum],Val([myImportWeekNum]))


HTH

John







When Galileo theorized that Aristotle's view of the Universe contained errors, he was labeled a fool.
It wasn't until he proved it that he was called dangerous.

 

[Rjc8513]

To clarify, you have some 'split' weeks in the master table. If a split week in the import table exists as a split week in the master table, you want it to be included as a split week. If a split week in the import table does NOT exist in the master table, you want he split weeks combined.

Is that right?

You are correct in the above, BoxHead. But, the week numbers in the "WEEKS" table are records in the "WEEK NO" field while the week numbers in the import file are field names.

 

[BoxHead]

Well that's interesting.

I believe you need to get the data into a normalized format before you are going to be able to anythin with that.

I'd need you to describe the structure and data of the import table to understand how to get it fixed.

Take a look at

http://r937.com/relational.html

If you have any control over how the data is captured, you would be doing yourself a favor to make some changes.

John