Change [field name]

[scukaf]

Hi everyone, I introduced myself as I am a new member (although reading this forum for a quite a time now)... guys I have (for me) a serious problem that i cannot solve:

So, I have a table which tracks employees shift schedule (shifts are like 1 - morning shift, 2 - afternoon shift, 3 - night shift and so on) and has field names like [date], [name1], [name2], ... [name10] (don't ask why, the structure of a table has to have fields arranged like this :/

Basically, here's the thing: As employees work 24/7 all of them don't have a weekend free all the time. It happens that a certain employee (because of the shift algorithm) doesn't have a weekend free (Saturday+Sunday) for months. So I created a query that finds, between dates given, how many weekends employee has had.
The thing is that I'd like to manage field names over a form like from a combo box (where i put Row Source Type to Field list) but that doesn't seem to work. So I was wondering if there is a way to do this in VBA (where i lack of a knowledge).

So here it is:

SELECT Count (*) AS [No_Weekends]
FROM
(
SELECT Shift_Table.Date, Shift_Table.[Name1] as Name_Saturday, Shift_Table.Date+1 as Sunday_Date,
(
SELECT A1.[Name1] FROM Shift_Table as A1
WHERE A1.Date = Shift_Table.Date +1) as Name_Sunday
FROM Shift_Table
WHERE CStr(Format([Date],"dddd")
)
In ("Saturday") And Shift_Table.Date Between #1/1/2015# And #12/31/2015#
) AS Weekend
WHERE Len(LTrim(IIf([Name_Saturday],Null,'')=0) AND Len(LTrim(IIf([Name_Sunday],Null,'')=0));

(Query works if I manually put field names, but all of the employees should have access to this DB, and all queries (as long as all other tables) must be invisible to them.)

Many thanks!

--
scukaf

 

[Minty]

It sounds as if you have a serious structure problem with your data which is now causing you an problem. Anytime you see field names with data in them e.g. Name1 , Name2 , NameSaturday etc. you can pretty much guarantee a problem , that is now the reason you have an issue. Can you post up some sample data and what result you are looking for?

 

[scukaf]

Sure Minty:

So the structure of table is like in attachment "Table_preview" :

1, 2, 3 are shifts, empty space is non-working day.

 

[Minty]

Your table layout should be
tblShifts
ShiftDate
EmployeeID
ShiftType

This would allow for as many employees as you like without having to recode and rebuild all your forms when employee 11, 12 or 13 arrives. Also if employee 1 is replaced you will then have no history of his shifts or not know when employee 1 is Fred or later Harry. Your query then becomes much simpler show me weekends only where employeeID = 2

 

[scukaf]

Yes I'm aware of this and I'd usually do something like this, but users who will use this DB will have to see all the names for all the dates exact like I showed you in the attachment (for example dates for current month). As this DB is practically finished and many person/days were spent, rearranging it would cost too much.

 

[Cronk]

"...rearranging it would cost too much."

Making a bad design sort of work might cost even more, and on top of that slower operation and higher maintenance will add more.

Heed the advice.

 

[Galaxiom]

Yes I'm aware of this and I'd usually do something like this, but users who will use this DB will have to see all the names for all the dates exact like I showed you in the attachment

Data storage should not be dictated by presentation requirements. The problems you are experiencing are caused by the data structure being completely wrong. You will continue having to do workarounds and the problems will get worse as development continues. (Database development is never finished because users will always ask for more features.)

The thing is that I'd like to manage field names over a form like from a combo box (where i put Row Source Type to Field list) but that doesn't seem to work.

The field and table names in a query cannot be parameters.

The query would need to be built by using VBA to concatenate the field names and values into a string to form an SQL command then running the command or using it as the RecordSource of the form.

However in this case I would suggest you take the expedient route and create ten separate queries. Use the combo to choose which query to run. Don't waste time trying to do something fancy because this database has no real future in its current structure.

 

[scukaf]

okay, i get the point and obviously I'll have to completely rework database (or make separate query for every [name], which is clumsy - I agree), no matter what users will say. As mentioned, from the beginning the whole db was set wrong, but it was the only way I could have done it.

Guys I appreciate the effort. Thanks.

 

[gemma-the-husky]

Re-engineering a complex system is a major problem.

From where you are, the solution is probably something like Galaxiom said, and bearing it all in mind for future projects. It will mean that some requests being difficult or impossible to provide. If it is working OK, and there aren't many requests for change, then it can be left alone.

Given the select query you gave in post #1, you could easily manipulate that on a form, and replace [name1] or the dates with values from a combo box, or date controls.

If the data was stored vertically rather than horizontally, you could obtain the listing for all names at the same time, now and in the future, with no design changes required as you add new names. That's the point. Avoiding "spreadsheet" thinking.

 

[Galaxiom]

... but it was the only way I could have done it.

The only mistake you made was not asking for help here at the beginning of the design phase.

The data structure is the core of the design and if it follows certain rules it will work very well with very simple interactions. Get this wrong and complexity tends to exponentiate.

The real art of database design is how to build the interface where the data is presented to and interacted with by the user. Virtually anything is possible but it can be complex to implement. But one should only diverge from properly normalised data structures after very careful consideration by experienced developers.

There are a lot of very ordinary questions asked on this site. Unusual requirements for the presentation of the data can be a more stimulating challenge for many of us.

I am sure we can help you with reworking the design. You have been though a learning experience getting to where you are now. Rebuilding your database will be helped by that experience so you are not really starting right from the beginning again on the project.

 

[scukaf]

thanks to everyone on your suggestions. tomorrow i start with my 5-day night shifts and will certainly try to do something with this unfortunate db.

i'll keep you posted

 

[scukaf]

The whole database has been reworked. Thanks on suggestions.

I forgot to thank you all and make this thread as solved...