trouble with structure (1 Viewer)

[accessuser1023]

I've been asked to design a DB for warehouse inventory and migrations being done inside server farms. But I'm having trouble visualizing what I need for relationships so I can get the detailed info. out of it. My issue is figuring out what to do with all of the child tables that stems from the [application] table. Simple one-to-many structure from top down gives me the wrong query output (as too many combinations appear for info. in the children table that stems from [application]). I think I'm probably thinking a bit too much here, as this is very easy. Can someone help me out here as to how to get rid of the data combos coming out of the these child tables that I don't want? Here's my structure:

=> Customer

=> Data Center Loc.
​

=> Application
​

=> Databases (Used for Application)
​

=> Appliances (Used for Application)
​

=> Network Devices (Used for Application)
​

=> Software (Used for Application)
​

=> Storage (Used for Application)
​

Been working with Access for quite some time, but apparently this one is tough. and I know it shouldn't be. thanks for any assistance here.

 

[plog]

Simple one-to-many structure from top down gives me the wrong query output (as too many combinations appear for info. in the children table that stems from [application]).

What does that mean exactly? Can you demonstrate it with sample data?

Also, what are the structures of the lowest level tables (Databases, Appliances, etc.)?

 

[accessuser1023]

i'm not sure what you mean by "structures" of the lowest tables. as you can see, there are many "lowest level" tables. see the attached query for the output as an example.

as you see there, for 1 customer that has only 1 location but 2 applications running inside that location, I have listed 2 appliances that accompany each application. I have also listed 2 databases that accompany each application. But the DB query is outputting the combination of these records which results in too many records.

I can't remember what feature of the database program I need to use to fix the issue. Subqueries? Subreports? So ideally, I need to see in the output:

***customer has 1 location which has 2 applications running, and each application contains 2 different appliances and 2 different databases.

as the query stands now (and the report), it is showing me that for each appliance, there are 2 databases. that is obviously not right. I think I just forgot how databases work, to be honest with you!

 

[plog]

By structures I mean the names and datatypes of the fields that comprise them. Is that what you gave me in the file? Or did you dumb it down? If you did, provide me with the actual tables.

 

[accessuser1023]

By structures I mean the names and datatypes of the fields that comprise them. Is that what you gave me in the file? Or did you dumb it down? If you did, provide me with the actual tables.

Plog,

the fields in the tables don't matter because those are just accompanying pieces of data. follow me on that? what really matters here is the structure of the tables themselves and the relationships between them. Regardless of what data is in what tables, the problem remains the same. the file only contains 3 fields in each table:

=> ID (auto n)
=> FK (to previously-leveled table)
=> name (descr. of item)

That should be good enough. There is a sample qry and rpt to look at. Besides, I don't have all the fields known to me anyway. I could upload you a SS in XL, but that won't help much. That came from the requester that doesn't know Access very well (like me, apparently!).

 

[plog]

the fields in the tables don't matter because those are just accompanying pieces of data

Let's play game theory for a moment. What do you think I'm thinking by asking for your table structure? With the explanation you've given me for why I don't need it, its obvious you don't think much of my thinking.

Maybe you have me pegged as a field fetishist who gets off at looking at other peoples table structures. Or maybe your afraid I'll sell your ingenious table structure that you can't get to work for millions of dollars. Again, from a game theory point of view, its obvious you don't think much of my thinking based on your reaction to my request for your table structure.

Maybe, just maybe, I didn't ask an irrelevant question. However, without that information, based solely on what you've provided me, here's my answer:

Since your lowest level tables (aka Appliance Child tables: Databases, Appliances, etc.) have the exact same structure (ID, FK, name), you don't need 5 of them, you just need 1 of them, with an additional field-->Type. Essentially you would move the table name into that new field. The data in those 5 tables would go into that one table like this:

ID, FK, name, Type
1, 1, software 1, Software
2, 1, database 1, Database
3, 2, software 1, Software
4, 2, storage 1, Storage
5, 3, software 2, Software

That would be the proper way to structure the database you've explained/provided to me.

 

[accessuser1023]

I'm terribly sorry, as I keep forgetting that you guys always request specifics. I'm more of a large-scaled guy. I do apologize for that. See the new attachment, as that contains the data that you're looking for. This is what was requested, but the structure I think is off. As the applications table has many children stemming off of it. I don't think you can report on this sort of thing in an easy way, can we?

thanks. and again, I apologize for being too general.

 

[plog]

My advice is still valid. Because the tables have the same structure you need to consolidate their data into 1 table with a new field to hold the Type as I described before.

 

[accessuser1023]

My advice is still valid. Because the tables have the same structure you need to consolidate their data into 1 table with a new field to hold the Type as I described before.

that sounds exactly right. what would come off of the type column though? you surely can't have field after field in the same table and leave some of them blank based on what type of item for the application is in the record.

that would defeat the purpose of the DB, no?

 

[plog]

I don't understand what you are asking. Where do you forsee blanks occuring?

 

[accessuser1023]

I don't understand what you are asking. Where do you forsee blanks occuring?

The children of table [applications] do *not* all have the same structure. that's what I'm not understanding from you. Can you explain that? I guess I'm a little bit off track now.

 

[plog]

The children of table [applications] do *not* all have the same structure

I think I'm done here. Good luck.