Changing names and labels

[Datagopherdan]

I'll throw something together for you real quick, so you can get an idea on how to set it up. Are you running Access 2007?



Dan
Access Development

[Geewaagh]

I'll throw something together for you real quick, so you can get an idea on how to set it up. Are you running Access 2007?

Dan
Access Development

Dan,

Thanks so much for helping! Yes, I am running 2007.

Greg

[maximus]

Dan,

You are absolutely right. I feel that it is not necessary to use excel. can you give me a detail description of what you want to do then most probably we can give you a solution. anyways did you try out my solution.

[Datagopherdan]

Here is a real quick sample of how you might set it up. It's in a cross-tab query and the date range is hard-coded in the query. In the real world, you would want to allow the user to select the date range that they would want to use. Also, the cross-tab query is just a real quick sample, so the dates are not in chronological order or anything but hopefully it should be enough to give you an idea on how to set it up.

Dan
Access Development

[Geewaagh]

Here is a real quick sample of how you might set it up. It's in a cross-tab query and the date range is hard-coded in the query. In the real world, you would want to allow the user to select the date range that they would want to use. Also, the cross-tab query is just a real quick sample, so the dates are not in chronological order or anything but hopefully it should be enough to give you an idea on how to set it up.

Dan
Access Development

Dan,

Either I dont know what I am looking at or I am not explaining myself very well. Both are likley.

Attached is a picture of my execl sheet.

I have a fixed two week schedule for my staff. For the most part, I have a fixed patent list.

I wanted to do an access data base so I could do simple reporting for my staff. I only pictured a few rows, but I have 150 patents. I wanted to be able to print out a report that showed just one staff member with their patents. I can do this in access, I just cannot get the dates to be right in the headings of my tables or reports.

I want to be able to have a column that says M-1 this week, M-8 next week. It is basically the same data, just dates change.

Sorry to be such a pain, I just dont understand how I use the sample you send me.

Greg

[Geewaagh]

Maybe I am still not explaining fully.....

My schedule is really all about a Mon-Fri, two week view.

My patents need repeating visits. A given patent needs to be seen every M-W-F for example.

So, basically, I am really just looking at a M-T-W-T-F week 1, M-T-W-T-F week 2.

However, sometimes there are slight changes weekly, so I have to publish a M-T-W-T-F schedule per week so everyone can see changes.

The dates I am trying to add makes clarifications to what M-T-W-T-F I am talking about when I publish.
Does this help understanding what I am trying to do?

Greg

[Datagopherdan]

Greg,

That was just a real quick sample to show you the basic setup of it. You would need to add something like Format([YourDateField],"ddd") in the query to get the actual days.

[Geewaagh]

Greg,

That was just a real quick sample to show you the basic setup of it. You would need to add something like Format([YourDateField],"ddd") in the query to get the actual days.

Dan,

See attached schedule. If you run the week 1 report and use "SKF" for the nurse, you will see what I am trying to do.

I just want to be able to change the lables easily on the report for a new week.

Can this be done? Havee a programed it all wrong?

The basic foundation is for me to have a patent name and say "this patent needs to be seen on Mon, Wed, Fri. Once that info is in, I need to assign nurse intials to those days.

I need a report that says, this week: Nurse "A", you see these patents on these days.

Any ideas on how to do this?

Greg

[Datagopherdan]

Hi Greg,

I looked over your database and it can be done but you have to set it up completely different. You would just want one field for your dates and then in your query make separate fields for the days of the week based on the date field.

I would try to put something together for you more in detail but I just don't have the time. I have a few other projects on my plate right now that are approaching deadlines, so I'm tied up with those.

Sorry, couldn't help you out more with it.

Dan
Access Development

[Geewaagh]

Hi Greg,

I looked over your database and it can be done but you have to set it up completely different. You would just want one field for your dates and then in your query make separate fields for the days of the week based on the date field.

I would try to put something together for you more in detail but I just don't have the time. I have a few other projects on my plate right now that are approaching deadlines, so I'm tied up with those.

Sorry, couldn't help you out more with it.

Dan
Access Development

Dan,

No worries! Thanks for the effort!

I am watching a bunch of videos on crosstab now. Maybe this is my answer.

I do have a question. Based on my database, I have created a "DaysWeeks" table. It contains one column of data called Dates. I have data in it like "Mon-12", "Tue-13", etc.

What field would I use to link these dates with my patents/nurse so I can use this data as headings in a crosstab?

Greg

[Geewaagh]

Dan,

You are absolutely right. I feel that it is not necessary to use excel. can you give me a detail description of what you want to do then most probably we can give you a solution. anyways did you try out my solution.

Maximus,

I can see now how your solution would work but it makes daily use of the table confusing.

Now, when I am working on the schedule, I have to remember that Text1 is monday for the first week. All my queries say text1,text2, etc.

It does make the report function work well.


This really should be VERY simple. ACCESS cannot make a caption for a lablel linked to a field name? Really?

greg

feeling disappointed and wondering off to excel to figure out how I can do basic reporting..... =-(

[Datagopherdan]

Greg,

Access can dynamically make label captions based on actual field values. You would not want that though. You would want to have bound text boxes, so those column names change dynamically, which you would be very hard pressed to do in Excel.

Excel is a fantastic tool for financial matters, however for storage of data and flexible reporting, a relational database such as Access is the way to go. Excel is not designed to act as a database in that capacity.

Dan
Access Development

[maximus]

Sorry to say you did not understand the code at all Greg. after all the explanation that I gave you on control name and control source. I will try explaining it again.

When you set the controlsource of a textbox it will dynamically update itself when every you change the name of the field. what do I mean by this:

If you have a text box in a report and the controlsource of the same is Mon-12, when you change the field name in the Table to Mon-13 the Controlsource is dynamically updated to Mon-13. Thus When you add my code on to the Onopen event of the report. It will always Caption the labels according to the controlsource of the respective textbox.

another thing we we use the code to change the caption of labels and it has nothing to do with queries that you will build out of the tables.

My question is have you tried out the sample database that I had sent to you. I think you have not.

Try it out. I think you have already set up your mind that you will migrate to excel.

Lets not under estimate Access due to lack of understanding and knowledge.

I am sure Dan will agree with me on this point that the code that I had written is capable of captioning the labels when the name of the fields are changed. The code is not required to change whenever you change the name of the Fields.

[Geewaagh]

Sorry to say you did not understand the code at all Greg. after all the explanation that I gave you on control name and control source. I will try explaining it again.

When you set the controlsource of a textbox it will dynamically update itself when every you change the name of the field. what do I mean by this:

If you have a text box in a report and the controlsource of the same is Mon-12, when you change the field name in the Table to Mon-13 the Controlsource is dynamically updated to Mon-13. Thus When you add my code on to the Onopen event of the report. It will always Caption the labels according to the controlsource of the respective textbox.

another thing we we use the code to change the caption of labels and it has nothing to do with queries that you will build out of the tables.

My question is have you tried out the sample database that I had sent to you. I think you have not.

Try it out. I think you have already set up your mind that you will migrate to excel.

Lets not under estimate Access due to lack of understanding and knowledge.

I am sure Dan will agree with me on this point that the code that I had written is capable of captioning the labels when the name of the fields are changed. The code is not required to change whenever you change the name of the Fields.

Maximus,

This post as been edited b/c I am a VERY slow learner! I finally understand your database! Thank you, thank you, thank you!

[maximus]

Please mark this thread solved as someone else with the same problem may find a assured answer.