Solved Need help regarding my databse. Seeking experts advice. (1 Viewer)

[MajP]

The junction table is a one time fill (until you get more components and configuration). Populating that is really an admin function. If you need help in making a form to assist in that, I can help. Once that is correctly populated anyone can add new units.

 

[MajP]

I demoed a form that will easily allow you to build configurations for unit types and assign component to rooms in a configuration. If you never built one of these many to many forms, it is confusing until you see it once.

 

[wikihow]

Oh Thank you so much, I will check it and reply back.

 

[wikihow]

Getting a VB Code error upon opening one of the form. Do the code need any corrections to the references?

 

[MajP]

That subform works with the mainform and not designed to be open stand alone. I added code to ignore that.

The main form should allow you to navigate to a Unittype. Then add rooms to the unit type. Then you can select a room and add components.

So here you can add/edit/delete rooms to Type A. If you click on Ensuite you can add/edit/delete components for the ensuite of type A configuration.

 

[wikihow]

Thank you MajP. I am creating forms to enter data like create a client, or create a project. and then I will use these forms to connect with a project and might make some additions in it. Thank you once again. You provided me best idea for the table structure.

BTW, in tblRoomConfiguration there are rooms and quantity of rooms. I don't think quantity is needed since we already defining all rooms in a unit type. There will be no 2x Bedroom 1. I will be going to delete that part. I hope it won't break any related link for that.

I will create some basic forms and will get back to you for some expert advice.


This is just one module or first part of database yet i.e. to summarize a projects with unit schedule. Further, I will be working with the product catalogue with different standard, specifications and finishes with different prices and those products will take place in the components table as we have it now.
After that I will have to develop a quotation module, which can save all quotes, with first, revised or final / approved versions. Further a Production module for the factory and installation module and more..
This database will imitate a complete procedure of the work in organization. A complete structure from start to end.


I want to ask you a question. I was told that the access database can be migrated to SQL servers. Can we still use the access fronted if the backend database is migrated to SQL server? is it possible or we need to develop a different front end for SQL?

 

[MajP]

BTW, in tblRoomConfiguration there are rooms and quantity of rooms. I don't think quantity is needed since we already defining all rooms in a unit type. There will be no 2x Bedroom 1. I will be going to delete that part. I hope it won't break any related link for that

As I mentioned I put the quantity field in because at one time you mentioned tracking things like doors and windows. If you are going to do things like that, I doubted you would want to enter 12 windows as seperate records. I also doubted that things like doors and windows would have components so I added that quantity field. If you are only tracking rooms, then get rid of it.

I was told that the access database can be migrated to SQL servers. Can we still use the access fronted if the backend database is migrated to SQL server? is it possible or we need to develop a different front end for SQL?

Many people use Access as the FE with a Sql server back end. Very common.

 

[wikihow]

Hello MajP,

I have created some forms for entering data in database. Can you check and let me know if there can be any useful functions or improvements possible. I am using queries as data source instead of direct tables. I have heard that it can provide us some flexibility later on. Is this right?

Now I have to prepare the different products for the database. But first, I need your advice on how we can arrange price for a one same product but different specification or size. For example, if we are quoting wardrobe it consists of 2x main items, Doors and Internal joinery. Doors are different types, different height etc. I need to arrange price for same product but different specs or size.

Like doors for 1mt wide wardrobe will be $100, but if wardrobe is 1.5 mt wide then it is $190, if wardrobe is 2mt wide, then price is $230 and so it goes more for greater sizes.
Then comes next type of door, which will have its own pricing slabs. One product i.e. for example doors can itself have may types of sub-types or finishes and different size off course.

What do you think that would be the best way to organize such pricing? with multiple table structure or tables with some code? btw, I am no good at VB. Just starting with Access at the moment.

 

[MajP]

I will get back to you later today. This can go from the simple to pretty complicated depending on your needs and desires.

 

[MajP]

I am looking at the wardrobe spreadsheet. Not only are the wardrobes different sizes but they have choices for end panels, fillers, internals ...
Again, my question has to do with the level of detail you want to track. Do you need to have costs on each of the sub components or is it enough to create a record with an estimate for a specific configuration and cost? If the latter then I would think the table is pretty easy.

In your component table you could have fields like

CompID - Autonumber
CompName - Some descriptive name of the component package
ComponentType (wardrobe, mirror, laundry door)
ComponentDescription ' Long description of the item and the subcomponents: 4 Drawers, x Extras, ...
ComponentEstimate

So for example you are making a wardrobe 2400 X 2100. So maybe it has a Name W24x21_A and estimate 1100. Maybe you have another 24x21 but it has different internal and extras so it is W24x21_B with estimate of 1500. In the description field you describe the details for that wardrobe This would be the simplest.

If however you need more detail the amount of tables could grow fast. For example Mirrors. If you need to store info that it was 4mm vs 6mm (besides just a note in the description field) then you are likely going to need different table than wardrobes or other components. A possibility be an entity attribute value (EAV) model. The EAV model is extremely flexible, but takes some code and understanding. Basically it turns columns into rows by storing the attributes (which is normally a field name) as a row and the value for that attribute. This way you would have one table that could store different types of components for components with very different properties in one table.

The above picture is not very good because it shows the same kind of attributes for all three entities. But imagine entity 1 was a wardrobe. It would have fields like Name, length, width, Internals, extras. If 2 was a mirror it would have L and W, but also GlassThickness (4mm or 6mm).

Your thoughts.

 

[pbaldy]

FYI, I moved this to a more appropriate forum.

 

[wikihow]

Thank you pbaldy, What would that mean? Can I still communicate here or I need to do something else?

 

[MajP]

What would that mean? Can I still communicate here or I need to do something else?

Yes it is just a tag for filtering. If you click on just Forums in the navigation bar it gives a list of different topics. If it deals with queries it should be in the query forum, if it deals with code in the module and code forum, etc. Not sure what this was in originally but now it is in General (could be Tables since you are focusing on table structure). Often people will put questions in the New User section designed just for introductions.

 

[pbaldy]

Thank you pbaldy, What would that mean? Can I still communicate here or I need to do something else?

Here is fine. As MajP suggested, the thread used to be in the introductory forum, which shouldn't contain technical discussion.

 

[wikihow]

Hello MajP, I have another problem in a form. Main form name is "frm_04_Add_Items_To_Unit_Types" (it is to be used to add items to Units types and it will populate automatically in all units of same unit types). I have added numbers to keep track of form and their related subforms.

Attaching image, where I am updating records in a subform (red highlight) in datasheet view and I need to reflect that addition in another subform (blue highlight) in datasheet view. I have added a button to refresh the blue subform, but instead i want it to update automatically, as soon as a record in red subform is saved. Tried adding a after update macro to the quantity field of red subform, but I am unable to refer to the correct path between subforms.

Also in the red subform, I can see items in ItemID_FK, but I was unable to select them. I figured out that I need to press Ctrl+S to save record and then it let me choose the item from ItemID_FK dropdown list. For the purpose, I added a macro to the field (on getting focus --> save record). I am unable to figure out why it does not let me choose until i save record. Can you please review and figure out what is the problem?

Thank you so much in Advance...

Appreciate all your help in guidance.

 

[MajP]

Attaching image, where I am updating records in a subform (red highlight) in datasheet view and I need to reflect that addition in another subform (blue highlight) in datasheet view.

You need to likely force a requery of subform bluee in subform red after update. You need a line of code something like
me.Parent.subformBlueControlName.Form.Requery

The parent of a subform is the form it is on (unless in a tab control then it returns the tab page)
So in a subform
me.parent: gives a reference to the parent form
Me.Parent.SubformBlueControlName: gives a reference to the sub form container on the parent form
a subform control has a source object which is usually a form (can be a report)
Me.Parent.SubformBlueControlName.Form: gives a reference to the actual form in the subform control
A requery will bring in all added or deleted records.

 

[MajP]

See if this fixes some issues. I changed the conditional formatting. Added the requery. Got rid of the Macros. I fixed the add Item, but not completely sure why this happens.
I would think you would design this with one more subform by splitting out the "rooms and items" into two subforms.
A: UnitTypes
B: Rooms linked to types showing the rooms for that unit (splitting out the current subform)
C: Items for room (splitting the current subform)
D: Your current master list

 

[wikihow]

Thank you.
The correct expression was "Me.Parent![subfrm_04_qry_All_Item_List].Requery" for my case.


I will check your changes and get back asap. Thanks again.

 

[poliadisa]

Thank you for your reply. Sorry i am not expert in access. Yes, I have used one to many relationship and not many to many. You may be able to get better idea from the database. I am attaching herewith. There is chain of relationship i.e. Client --> project --> unit types --> units --> rooms (rooms in unit). I am just stuck because o Lucky Patcher Kodi nox f apartment grouping. I will try to work as you have advised above with a junction table and will get back to you.

Thanks again for helping out.

, I have two linked tables. One parent and one child table with more records. Parent table would have some records of similar items, for those child table will have to have same record except the primary key. I want to avoid time to type same information in child table again and again for similar parent table records.

 

[MajP]

@poliadisa,
Please start your own thread, even if you think the issue is similar. It is too confusing to address multiple questions in one thread. You should then delete this post from this thread.