Disappearing values after filtering combobox

[jaryszek]

Hi Guys,


please find in attachment my example database.


I have table t_serverdetails with a lot of numbers values. I create a form MAinForm

1)
.
When you click on serverdeilas_IF_FK combobox (column serverdailas_ID_FK) you can choose values from above table.
And columns Cores and MemorySizeGMultiBytes will be updated also.

When you click on Cores you will see the same combobox but other columns are set to 0 width.

It is possible to solve this in another way?
Or build some list or another table to see what is going on? Or maybe build one more form with datasheet view and see what is going on with it.

Or maybe within combobox there is a chance to change it?

I mean to build more User Friendly view for this?
(for me this is strange to have this in few columns within table but can not imagine how to solve it in other way).

Second, more important problem.
2)

2) When i selecting column Cores i have an got focus event which is restricteing list of all possible values in combobox
(filtering it using code:

     Dim rst As DAO.Recordset
     Dim rsFiltered As DAO.Recordset
     Dim ValueCount As String
     
     ValueCount = [Form_t_customers subform].SystemNameCombo.Value


     Set rst = CurrentDb.OpenRecordset("select * from t_serverdetails WHERE Topology_ID_FK = " & ValueCount)


     'rst.Filter = "Topology_ID_FK =" & [Form_t_customers subform].SystemNameCombo.Value


     Set rsFiltered = rst.OpenRecordset


     Set [Form_t_juntion subform].ComboCores.Recordset = rsFiltered


     Dim ctrl As Control
     Set ctrl = Me.ActiveControl

So when SystemNameID is 1, when i click on combobox in Cores column there will be list from t_serverdetails where Topology_ID_FK = 1.



This is ok for inputing new customers and users.
But when i want to change existing one customer and his system - i have a problem.

I am on Cust1 with SystemName = 1

If i change SystemName field from t_customers subform to second value = 9 and clickec Cores column in t_juntion subfrom all values will disappear !

I do not know why this is happening.
And how to solve this.

When i am unhiding Serverdeilas_ID_FK column within combobox - i see in column Cores Serverdeilas_ID_FK value but i am not seeing Cores value anymore.

I think that these two problems are connected one with each other.

EDIT: please use database7_UPDATE file.

Please help,
Best,
Jacek

[Minty]

I'm confused by what you are trying to achieve here.

The servers are a unique entity on their own, and already have a field for the cores which is specific to that server ID.
What purpose does the combo box serve in this context? It's a bit like having a combo box for your Surname in a PersonTable, it doesn't seem to make sense.

if you had a Server Type, and that Server Type could have a variety of cores then that would make more sense, but even then you would still have that Core value stored as a fixed data point in the actual Server record.

[jaryszek]

Hi Minty,

thank you for answer.

The servers are a unique entity on their own, and already have a field for the cores which is specific to that server ID.

You have right. First of all i didnt have Topology_ID_FK column here. I added this last time because i am having a lot of numbers without any reference in table t_serverdetails.
And i think that problem can be in my relationships here. But still i am not seeing where...

When i was choosing Cores column i had list of all records with memories, cores and sizes from table t_serverdetails.

But when i am choosing SystemName = System1 i want to have only records corresponding with it. So i want to have list from table t_serverdetails filtered by values for System1.

I added new table Machine to show you where i have problem.
So imagine that for each customer you can have set of systems and cpu and furthermore for each system and cpu you can have set of machines, cores, memories etc (like in table t_serverdetails).
I was trying to mirror this in my attached sample database.

So for example for Customer: Cust1 you can assign SystemName: System1 with specific CPU and you can assign System2 with other CPU. And for this set you can assign also additional set of data from table t_serverdetails and t_machine.

I added also additional tables which i have on production to exactly mirror what is the problem here.

Please help,
Best,
Jacek

[Minty]

I'm still confused I'm afraid, and I work for a Data centre maintenance company, so I'd like to think I understand server hardware setups.

Take a step back, and if you can maybe describe in plain English what you are trying to store and process.
Maybe draw a picture of what you think the basic relationships and tables should be.

[jaryszek]

Minty,

thank you.

I attached sample database and this is the simplest way i can show you and which i can. Maybe others will understand?
I do not understand your "old fashion ways" to create database model at all.

The core is here:

So imagine that for each customer you can have set of systems and cpu and furthermore for each system and cpu you can have set of machines, cores, memories etc (like in table t_serverdetails).

I created relationships to mirror it:



Best,
Jacek

[jaryszek]

I am adding version v3 here.

With relationships and i changed Topology_ID_FK in t_serverdetails to Systems_ID_FK (to better adjust whole model).

Please help,
Jacek

[davegri]

See attached. I got rid of all the orphans, and established referential integrity so that orphans will not be allowed.
I couldn't do much more as I don't understand the need and relationship between CPUs, Systems, and Machines.
Your main form has no recordsource, yet has two subforms with no relationship between them. Very confusing setup, but since I don't grasp how the DB is supposed to work, maybe it's OK.

Database7 UPDATEV3-davegri-v01.zip

[Minty]

@Dave - That was my point of asking for an plain description.
I suspect something is maybe being lost in translation, as I don't understand the relationships between the different parts, it doesn't make sense to my understanding of how any system is put together.

To my mind a server may have a number of processors / cores etc, but they are a part of that server.

So you would have the ServerID , with a ProcessorID that identified the Processor and number of cores.
You would normally also indicate the number of processors, and probably record the amount and possibly type of memory - again that could be a lookup table with various memory types and sizes, but I'm doubting the value in trying to maintain that level of detail in a seperate table.

I too don't understand where the Machine ID comes into this.

Maybe if it was a Server rack you would record the server ID's in a RackTable ?

[davegri]

@Dave - That was my point of asking for an plain description.

Aye, I hear that.

[jaryszek]

Hi Guys,

for you for your answers and help!

davegri thank you, but still when i am choosing Cores with changed SystemName my t_juntion subfrom (Cores column) is becoming empty:




Minty:

To my mind a server may have a number of processors / cores etc, but they are a part of that server.

Each customer can have different systems and processors. And for each set of them we can have cores and memories and machines types.
But if even processors would be a part of System-Customer set, this is not solving my problem.

I think my model lacks of filtering in t_juntion subform.

but I'm doubting the value in trying to maintain that level of detail in a seperate table.

Hmm maybe you have right Minty.
I could have server and processors in separate table - assume t_server_cpu table (Server_ID_FK and Cpu_ID_FK) and connect this to server details and processors.

And server_cpu table would refer to t_junction table to configure it with machines and customers_systems table.

But sometimes i have customers without any cpus and servers. So t_junction would be nullabe for them then.

If i can not think about how to avoid disappearing values while filtering. I will reconsider it,

Thank you once again,
Best,
Jacek

[jaryszek]

again that could be a lookup table with various memory types and sizes. but I'm doubting the value in trying to maintain that level of detail in a seperate table.

O i am interesting how to do it in another way.
What i am seeing it is only separate table with bunch of detailed data....

Do you mean lookup fields here Minty?

Best,
Jacek

[jaryszek]

Hi Guys,

i have to say thank you for showing me that my model is not quite good. thank you mostly to Minty.
I had a meeting with my boss and i didn't understand data correctly.

I have to add one more new thing here - data infrastructure.

I have to ask you about help with modelling data.



In attachment please find my new database model.

And now:

1) Customer is the higher level of my data model.
2) Systems is an application level. So system1 can work on different servers.
3) Servers can work in 2 different infrastructures.
4) Servers can have different details and memorysizes.

Main problems here are:

a) t_serverdetails table - each server size and memory are characteristic for different systems - am i showing this correctly? The column Systems_ID_FK in t_serververdetails is helpful here ?
b) Relationing t_server and t_serverdetails - can i do this better?

Please help,
Jacek

[jaryszek]

One additional problem here:

I will sometimes have ServerDetails_ID_FK in table t_Server_serverdetails nullable...
Sometimes i have lack of data here...

Best,
Jacek

[Minty]

I think you are getting closer but still have added complications that i think are unnecessary.

Your description here;

1) Customer is the higher level of my data model.
2) Systems is an application level. So system1 can work on different servers.
3) Servers can work in 2 different infrastructures.
4) Servers can have different details and memorysizes.

Doesn't mention the Machine detail, and I can see no purpose for the machine table.

Your tables t_server, serverdetails and server_serverDetails seem unnecessary, as a physical server can only have one set of specifications, and therefore this should be one simple table.
As for your customer and the null values - either there is a server or there isn't!
A server type could have many different configurations, but that would be a separate set of tables, and seems redundant for your purposes here.

I sorry I don't understand what you mean by a server working in 2 different infrastructures? Can you explain this in business terms?

Systems sounds like application software. Software would be installed onto a server.
You can't have software without a server to run it on. I think your junction table is not correct for this, and that you may need a seperate junction table one for Server/Applications.

[jaryszek]

Hi Minty,

thank you very much.

Doesn't mention the Machine detail, and I can see no purpose for the machine table.

Machine is virtual or bare metal.
And this is characteristic for Server.

So different servers can run on different machines.
So server1 can run on virtual machine and have specific serverdetailsinfo.

Your tables t_server, serverdetails and server_serverDetails seem unnecessary, as a physical server can only have one set of specifications, and therefore this should be one simple table.

this is the biggest problem for me here.

Before i will provide example i want to write that Infrastructure is a set of servers/systems.
So Customer1 will have infrustructure1 where there are bunch of servers with specific characteristic.
Customer2 will have infrastructure2 also with bunch of specific servers.

Returning to the topic...

and therefore this should be one simple table.

It would be enormous one table with a lot of characteristic (a lot of columns).
For example Server1 has 2 CoreSockets, 4 Sockets, Model1, Infracture1, System(software), 4 Cores, 512 GB Memory Size.
How to input data? I know that for Server1 i can have many possibilities.

Because Server1 can have 512 GB or 128 Gb.
Maybe it is better to do it like in picture below or attached database?


Thanks to it i will know that on this server runned with specific memories i will install system1.
Or maybe i should use some kind of trick to have possibility to choose data properly for each server?
Some lookup table? How to solve this technically?

As for your customer and the null values - either there is a server or there isn't!

It is true. Ok. So this is a good approach ! thank you!

Best,
Jacek