Display different dates for an item horizontally not vertically

[Sam Summers]

Hi guys, the age old problem:

I need to display up to four inspection dates for the 6 right hand column items below.

You will see on the left that there are different buildings but the items may be inspected every week so i need to display up to four weeks dates horizontally for each item.

Many thanks in advance

 

[Ranman256]

this is not a good design for relational databases. Let the db do the work.

you DO want it vertically (its how it works)
BLDGid, PeriodID,Item,Date
2, 1, Faucets, 27/1/21
2, 1, Drains, 27/1/21

 

[Dreamweaver]

You can with a forms text box but don't think you can with a table may also depend on your version of access

 

[Minty]

@Ranman256 is right, your data is not stored correctly.

 

[Dreamweaver]

Ooops Too many hours in the trences @Ranman256 & @Minty

 

[Minty]

@MickJav Unless I'm mistaken that looks like a table, and therefore is a spreadsheet design.
The columns shouldn't be fixed for entities like "Showerheads" etc.

If it's a cross tab query I'll happily take it all back.

 

[Sam Summers]

The screenshot is the table just to show you the data.

What i have to do is display the dates on a subform in a row.

Its the way the user uses it hence why it is designed that way

 

[Dreamweaver]

@MickJav
If it's a cross tab query I'll happily take it all back.

Look like a cross tab to me lol

 

[Minty]

Its the way the user uses it hence why it is designed that way

This will get you in big trouble.
The user doesn't dictate how you store the data, you as the developer do that to make your life as easy as possible.
How you display that data and the user interacts with it is something that the user should have an influence on.

What happens when someone wants to add SprocketWidet6 to the list of things being inspected?
Redesign everything or simply add it to the (vertical) list of Inspection Items - I think you know which one will be easiest.

 

[Sam Summers]

This will get you in big trouble.
The user doesn't dictate how you store the data, you as the developer do that to make your life as easy as possible.
How you display that data and the user interacts with it is something that the user should have an influence on.

What happens when someone wants to add SprocketWidet6 to the list of things being inspected?
Redesign everything or simply add it to the (vertical) list of Inspection Items - I think you know which one will be easiest.

I've been through this before and its only dates that change not the items and i am creating this system to be familiar to the users paperwork system they currently use. Its not making it easy but it looks good

 

[Gasman]

Well if you can stuff them all in one column, use Allen Browne's Concatenate function or theDBguy's SimpleCSV() function which you can search for here.

 

[Sam Summers]

Well if you can stuff them all in one column, use Allen Browne's Concatenate function or theDBguy's SimpleCSV() function which you can search for here.

Thank you. I will take a look

 

[Sam Summers]

I had a look at both but couldn't get either of them working and so found a way which hopefully will do just using the dates as displayed vertically.

Thank you everyone

 

[Minty]

I've been through this before and its only dates that change not the items and i am creating this system to be familiar to the users paperwork system they currently use. Its not making it easy but it looks good

Just to reiterate I'm not suggesting that you don't mimic their existing paper process, just don't store the data like that.
You appear to have discovered already that it is making things difficult for you.

 

[Sam Summers]

Just to reiterate I'm not suggesting that you don't mimic their existing paper process, just don't store the data like that.
You appear to have discovered already that it is making things difficult for you.

I know Minty. It's been a nightmare just because of the way they currently operate so i try to mimic it so the users would find it familiar. It's only dates that are being stored but i have a compromise that will just have to do.
Thank you

 

[Pat Hartman]

The screenshot is the table

Therein lies the problem. It is far better to normalize the schema. You can produce a crosstab if the user wants to see a report. You can also take a look at a sample database I created that lets you have your cake (proper normalization) and eat it too(users get to see a spreadsheet).

I call it a bound denormalized form. The work is done with queries and it will work for a limited number of columns. I developed it for a forecasting application where the users wanted to see 12 months across for each item being forecasted. I show two samples. One with months and a second with expense types.

 

[Sam Summers]

Therein lies the problem. It is far better to normalize the schema. You can produce a crosstab if the user wants to see a report. You can also take a look at a sample database I created that lets you have your cake (proper normalization) and eat it too(users get to see a spreadsheet).

I call it a bound denormalized form. The work is done with queries and it will work for a limited number of columns. I developed it for a forecasting application where the users wanted to see 12 months across for each item being forecasted. I show two samples. One with months and a second with expense types.

Hi Pat thanks for that. That is way beyond my abilities so i think for all that they want, they will just have to go with the solution i have created which displays the dates in column format for each item.
Thank you everyone. I'm sure this thread will help many more.

 

[Pat Hartman]

It is the wrong solution but if you're happy with it, what can we say except the users are not the experts in database design.

 

[Sam Summers]

It is the wrong solution but if you're happy with it, what can we say except the users are not the experts in database design.

The dates are now being displayed in a column which is the way the database stores them. That's the only way now because its only dates that are changing and relate to the period, maintained items and the buildings. The faucets, drains etc are only labels and never change for any of the buildings.

 

[Pat Hartman]

It's your database but the design is incorrect. Do what you want. Just know that the schema violates first normal form. This is not the worst error you could make and the application may have a long life and the repeating group won't cause a lot of extra work unless the user decides he needs a new date column. But it will cause extra work if you need to do any analysis of the dates and it will cause a lot of extra work if you need to add an additional date column.