Is this even possible? (1 Viewer)

peterod

Registered User.
Local time
Yesterday, 22:22
Joined
Jan 13, 2011
Messages
31
First post in a long time and im struggling to get my head back in to it. I think i am barking up the wrong tree.

I have 2 tables.

1 simply contains project numbers

the second contains specification elements and a yes no check box table

The result i am trying to achieve is a check box form which will prepare aspec for project number by means of simply using the check box.

I reckon this should be straight forward but I will be honest, when you have not used access for years and then try and jump back it, its not as easy as you remember.

All thoughts appreciated, even negative.
 

Attachments

  • CVTF REV 2 2019.accdb
    704 KB · Views: 127

June7

AWF VIP
Local time
Yesterday, 21:22
Joined
Mar 9, 2014
Messages
5,424
Most anything is possible with enough code.

Could you explain what you mean by "prepare aspec for project number by means of simply using the check box"? Should that be "a spec"? And "prepare" means what? Create new records somewhere?
 

Cronk

Registered User.
Local time
Today, 16:22
Joined
Jul 4, 2013
Messages
2,770
#peterod


Is what you are wanting is to select one or many of the specifications for a project and then have a report for the project listing the selected specifications?


If so, rather than using check boxes, I'd use a multi-select list box.


Search on something like "How to retrieve selected items in a multiple selection List Box"

 

jdraw

Super Moderator
Staff member
Local time
Today, 01:22
Joined
Jan 23, 2006
Messages
15,364
peterod,

Without knowing your business or processes, I can only offer general comments.

1- Access does not work well with field names that include embedded spaces. So regardless of your approach, you should remove all spaces from field names.

2- I recommend you write a description of your business that shows what is involved, when and who uses/needs what etc. Start at a 30,000 ft overview and gradually add detail. It will help you understand your requirement --and you know your business better than any reader.

Always identify What you are trying to do before suggesting How it might be done.

Here's a link to steps for designing a database.

Good luck.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 01:22
Joined
May 21, 2018
Messages
8,463
I did not look at your db, but from what you said I interpret
1) A Project can have many assigned specifications
2) A specification can be assigned to many projects
3) You would like to pick a project and then check specifications and have them assigned to a projects

If that is correct then this is a many to many and you need a third table
Code:
tbl_Project_Specifications
  ProjectID_FK ' a foreign key to table projects
  SpecID_FK    ' a Spec foreign key to table specifications
  OtherFields   ' Fields that uniquely describe that project spec relation

You can use that yes no field or a multi select listbox. Either way requires code to load the previous selections for display and to do an insert/delete in tbl_Project_Specifications when you select or unselect. I prefer to use a From To listbox. Where you have two listboxes. On the left are the un-selected choices and the right are the selected choices. Then have buttons that move from one list to the other. I find that more intuitive and easier to see what is and is not selected.
 

jdraw

Super Moderator
Staff member
Local time
Today, 01:22
Joined
Jan 23, 2006
Messages
15,364
I agree with MajP re M:M.

General table structure and relationships

Project -->ThisProjectHasTheseSpecs<---Specs
 

peterod

Registered User.
Local time
Yesterday, 22:22
Joined
Jan 13, 2011
Messages
31
thanks for the replys. I will try to define it a little clearer.

I work in a design office (architectural - timber frame) , we have specifications for each project which should be consistent and they are constantly inconsitant or even incomplete. Each project only has 1 specification but there are many items in the specifcation.
The specification is basically the paramters of our materials being supplied. Because it is currently being done from a word document things are often overlooked or forgotton by the estimator which causes issues.

What I am attempting to do is make our specifications consistent and faster to create. I envisage is our estimator opening a database form which is a checkbox list of each of the items that we can supplymhe just checks a checkbox. I am basicly trying to make a project specification builder as 1 part of the database. I am also looking to get rid of countless paper files that are scattered all over our office.

The relationship is 1-many as 1 project can have many specification items.


I did this in a previous office and the database became integral to the way the office ran, we logged design hrs against projects which made time reporting easy for invoicing etc, but I did this many many moons ago and it was a 1 time learn what i needed affair, ill be honest if forgotten much of what I learned then.

Ill take the advice above and keep reading, but having written this, id still appreciate any further comments from you guys. This forum is a great help aswell.

I have a plan, with tables noted, and relationships mapped out, i wanted to figure out this aspect before tackling the wider database.

I have bought access for dummies book and im reading it through so ill get there eventually, but i feel like I might be starting this on the wrong foot.

Thanks again for any comments you have.
 

Attachments

  • Capture.PNG
    Capture.PNG
    79.9 KB · Views: 113
Last edited:

peterod

Registered User.
Local time
Yesterday, 22:22
Joined
Jan 13, 2011
Messages
31
i see its a many to many now after re-reading above. oops. ill add a junction table in. Majp. Do you have a sample kicking about or a link i could use to see how this would be achieved.

I feel stupid now!:(
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 01:22
Joined
May 21, 2018
Messages
8,463
The relationship is 1-many as 1 project can have many specification items.

I still think it is a many to many

Code:
Tbl_Projects
  ProjectID '
  other Project Fields

Code:
Tbl_SpecItem
  SpecItemID
  SpecName
  Other Generic fields for a SpecItem
I am assuming a SpecItem is like

1 Roof Trusses
2 Floor Joists
3 Wall Panels
4 Exterior Siding
5 Hard Wood Flooring


Code:
Tbl_Project_SpecItem
  ProjectID_FK
  SpecItemID_Fk
  'Specific Parameter fields for that Project and Spec Item
  'Mayb
  Dimension
  Quantity
  Units
So you pick a generic Spec Item and assign to the many to many Tbl_Project_SpecItem. Here you put the details for the Project and spec
So if Project 123, needs 100 2X8 Roof trusses and 789 needs 1500 SF solid 3/4" hardwood then maybe it is something like

Code:
ProjectID_Fk  SpecItem  Dimension      Quantity  Units
123               1       2"X8"x12'     100      Boards
789               5       3/4"          1500       S/F
However you are on track with getting your tables correct and not worrying about Forms. Post an image of the relation. If you get the table correct than you can do whatever you want.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 01:22
Joined
May 21, 2018
Messages
8,463
This demo was the user wanted to track their music. He wanted to assign a musician to each track and each instrument. It is a little more complicated relation because a musician can play on many tracks, and many tracks can have a musician. Also a musician can play many instruments on a single track (many to many to many).

However this version uses a multiselect listbox to add and remove items from the many to many table. You can Select a track from the list of tracks, select a musician from the list of musicians, and select and instrument. Then assign them.

https://www.access-programmers.co.uk/forums/showpost.php?p=1588756&postcount=9

As I said I am not a real fan of the multiselect, but if you get your tables correct you can do the same thing with checks or other interfaces.

I still have not take a look at your db, I will take a look and see if I can provide something more relevant. I forgot that you already provided something.
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 01:22
Joined
May 21, 2018
Messages
8,463
Looked at your DB, and because of the naming convention (or lack of one). I was confused
1) As mentioned no spaces anywhere. If you want a space in a table name or field name use an underscore (Project_Name). Or use camelcase ProjectName or projectName
2) Most people identify tables, forms, reports, modules with some kind of identifier. I like three letters
tblProject, qryAllProjects, frmSomeProjects, subFrmMyProjects, rptMonthlyProjects, mdlCalculations. As long as it is consistent and understandable. Nothing worse than a form, query, report all with the same name
3) People will debate that field names do not need to be unique across tables, but nothing is more confusing to me than every table with a PK of ID. I recommend Project_ID, SpecificationItem_ID, etc. I also like to differentiate foreign keys with and FK Project_ID_FK.
4) People will debate this one, but I like to use natural keys when it makes sense. You have plot numbers that are numbers, and project CV numbers that are numbers. If these are unique and rarely changing and available at the time of creation, it makes more sense to me to use these as PKs. You have plot ID of 14 and plot number of 14. I think this will be real confusing when you have a plot id of 22 and plot number of 20 when a record gets deleted.
5) Your table names appear to reference the key of the table. I have never that before. It should be descriptive of the entity the table holds. I would think Project Numbers should be tblProjects with fields uniquely describing a project and Plot Numbers should be tblPlots.
I think a Project has a CV number that uniquely identifies a project. I think your junctions should also be descriptive of the joined tables and not the keys. “Junction CV No Plot No” could be something like jncTbl_Projects_Plots that describes the table (entities) and not the keys.
 

peterod

Registered User.
Local time
Yesterday, 22:22
Joined
Jan 13, 2011
Messages
31
I will revise based on your comments. Thankyou.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 01:22
Joined
May 21, 2018
Messages
8,463
Here is a demo. Not certain I got you relationships correct, but will give you an idea how you can select from a list and assign to a many to many. The first demo is a multiselect box to add or remove spec items from a project. The second demo uses a form and your boolean field includedInSpec.

The ideas for any method are basically identical just implemented differently. On the current event it has to clear out the previous selections and then load the correct selections for that Project. Then when you select a spec item it has to do an append query to the junction table. When you unselect it has to do a delete query from the junction table.
 

Attachments

  • SpecDemo.accdb
    708 KB · Views: 101

Users who are viewing this thread

Top Bottom