Search in field names of table

[JiTS]

Hi,

I am stuck with the following problem:

[problemsituation]
I have a table tblContracts with the fields week1, week2, week3... week52. In these fields are prices.

I have a query for a report which shows the orderinput per week.

[problem/question]
In the query is a weeknumber present.
I want to search in the table tblContracts in the field names of week1, week2, week3, etc. and then pick the price for the looking contract.

For example:
Weeknumber 12 is present. The query will search in the table tblContracts in the field names for '12'. It finds 'week12' and picks the price ($ 10) which is in the field of the right contract.

[own solution]
I think something with DLookup or DCount but these functions will search in the fields which are filled in and not in the field names... or am I thinking wrong?!?

Please help...

Thanks,
JiTS

 

[Mile-O]

[problemsituation]
I have a table tblContracts with the fields week1, week2, week3... week52.

am I thinking wrong?!?

I quoted a small part of your problem situation and left the field names - that's your problem. You have a repeating group that violates First Normal Form (1NF).

 

[JiTS]

Ok...

now:

tblContracts
=========
Contractnumber
Week1
Week2
Week3
...
Week52


Solution:

tblContracts
=========
Contractnumber
Weeknumber
Price


Better?!?


*SMiLE*
JiTS

 

[Mile-O]

Solution:

tblContracts
=========
Contractnumber
Weeknumber
Price


Better?!?

Yes, that's better.

One little thing:

tblContracts
=========
ContractID - (PK) AutoNumber) *
ContractNumber - Indexed, No Duplicates
DateOfContract - Date/Time
Price - Currency

Autonumbers are better for creating relations than (I suspect ContractNumber to be Text) Text fields as they take up lest space and are faster to index within queries.

Rather than store the weeknumber, it's better to store an actual date as you can then calculate the WeekNumber from this and you also have the added luxury of being able to query over any date range, over different years.

In a query:

WeekNumber: Format([DateOfContract], "ww")

will give you the week number. You can use the expression on a form or in a report, too.

 

[JiTS]

Please take a look at the attachment.

What you see is what I made with my current table tblContracts.

I am wondering if it's still possible to create the situation just like the attachment with the new table tblContracts?

Conclusion:
What I want is that the user is able to fill in each week seperatly, just as in the attachment.

 

[Mile-O]

I've already said that you should store the date and not the week number as you can calculate the WeekNumber any time as the example attached demonstrates.

As it seems you are not even storing the year. If you total all the week 1 entries you will get all the week 1 entries for every year in the database.

 

[JiTS]

Goodmorning Mile-O-Phile!

Actually the form that you saw is a subform of a mainform.
I attached a picture of the whole form including the subform.

Maybe it will give you a better look to my problem...


Thanks,
JiTS

... damn, I still need some hot coffee!

 

[Mile-O]

Conclusion:
What I want is that the user is able to fill in each week seperatly, just as in the attachment.

I attached a picture of the whole form including the subform.
Maybe it will give you a better look to my problem

I don't understand why you are persisting with this Week1, Week2, Weekn business. If that's what you want then I can't help with generating records that do not yet exist for the purposes of data entry.

 

[JiTS]

Well, I changed my way of thinking and I am using your idea in my database now.

At this moment I have another problem, which you can find in the 'Form' section of this forum.


*SMiLE*
JiTS