Need Advice. (1 Viewer)

[SykesIT]

I am designing a system for a client where they have carding wool weigh-ins throughout the day and also spinning ones. These can run for days and kept simple so the user can press a button it weighs the wool and then records the weight. The main information customer, shade etc will be duplicated, but they want both to run independent of each other.

I was going to create a form for each and combine on one large form as it will be running on a 24" touchscreen PC.

Regarding the structure, I was going to do a spinning table holding the main info for the spinning and a sub table of weight where each weight will be recorded, then when they start a new job a new spinning record will be created and then weights for that job will be created in the weight table and linked by job id.

My worry is do I create a Carding table exactly like spinning and link to weight table again, but keep the main part separate, to stop confusion or just create 1 table and have a type for spinning & carding then I don't have to duplicate the tables?

I am concerned with 2 forms open at the same time using the same tables.

I hope that make sense it's hard to describe.

Thanks

 

[LarryE]

The first thing is to describe and document the process. In ACCESS we do not duplicate data tables so when you say customers and shadings will be duplicated, that won't happen in ACCESS. We keep one piece of data in one table. Period. Then we link tables together. So it sounds like you have customers who bring wool in to be carded and then spun. Each customer may have multiple dates that this is accomplished. Each carded "batch" of wool is weighed and then sent for spinning. Each carded batch that is spun also has a weight (and other characteristics) attached to it (such as shade). Each carded and spun "batch" is then used in various jobs. I assume each "job" then has certain associated characteristics also.

Just based on what you have said, it sounds like you will need:

1. A Customer table to hold all customer information
2. A ReceivedDate table holding all the dates each customer has brought wool in to be processed
3. A CardedWool table to hold all the weights, shades etc for each batch of wool carded
4. A SpunWool table to hold all the characteristics of each spun batch (final spun weight etc.)
5. A ProcessingJob table to hold the various jobs in which each carded and spun "batch" is used.

The Customer table is then linked to the ReceivedDate table which is linked to the CardedWool table which is linked to the SpunWool table which is linked to the ProcessingJob table.

Not knowing anything abound the actual process, I just took a common-sense guess at what the process is. It would helpful if you could describe the actual process like I tried to do. That process will tell you what tables you will need and what the relationships will be.

 

[isladogs]

Just for info, the maximum width of an Access form is 22.75 inches so if you use one large form it won't (quite) fill the screen

 

[SykesIT]

The first thing is to describe and document the process. In ACCESS we do not duplicate data tables so when you say customers and shadings will be duplicated, that won't happen in ACCESS. We keep one piece of data in one table. Period. Then we link tables together. So it sounds like you have customers who bring wool in to be carded and then spun. Each customer may have multiple dates that this is accomplished. Each carded "batch" of wool is weighed and then sent for spinning. Each carded batch that is spun also has a weight (and other characteristics) attached to it (such as shade). Each carded and spun "batch" is then used in various jobs. I assume each "job" then has certain associated characteristics also.

Just based on what you have said, it sounds like you will need:

1. A Customer table to hold all customer information
2. A ReceivedDate table holding all the dates each customer has brought wool in to be processed
3. A CardedWool table to hold all the weights, shades etc for each batch of wool carded
4. A SpunWool table to hold all the characteristics of each spun batch (final spun weight etc.)
5. A ProcessingJob table to hold the various jobs in which each carded and spun "batch" is used.

The Customer table is then linked to the ReceivedDate table which is linked to the CardedWool table which is linked to the SpunWool table which is linked to the ProcessingJob table.

Not knowing anything abound the actual process, I just took a common-sense guess at what the process is. It would helpful if you could describe the actual process like I tried to do. That process will tell you what tables you will need and what the relationships will be.

So you are more saying 2 tables at the top end.

The issue is carding and spinning can be going on at the same time, so carding can be going and they weigh it could be 50 or more times. The spinning can be progressing where they weigh 4 times in a batch but then reset the and do another 4 weights later that day.

These need to be separate as it's not always one is done and the other is then done, so once carding is done they may be still doing spinning for the customer and then start a new carding job. So I was going to keep independent as they don't want them linking together.

I have attached a relationship example with one main table with a type in rather than 2 independent main tables.

When I said duplicate I mean as they want them independent the customer ID, shade of the wool, count, etc will be on that job but as they are independent processes they may have to duplicate the data.

 

[LarryE]

So you are more saying 2 tables at the top end.

The issue is carding and spinning can be going on at the same time, so carding can be going and they weigh it could be 50 or more times. The spinning can be progressing where they weigh 4 times in a batch but then reset the and do another 4 weights later that day.

These need to be separate as it's not always one is done and the other is then done, so once carding is done they may be still doing spinning for the customer and then start a new carding job. So I was going to keep independent as they don't want them linking together.

I have attached a relationship example with one main table with a type in rather than 2 independent main tables.

When I said duplicate I mean as they want them independent the customer ID, shade of the wool, count, etc will be on that job but as they are independent processes they may have to duplicate the data.

OK as long as each piece of information is kept in only one place. I would think you need to record the day and time the wool is received in from the customer though if you are going to track daily activity or length of time it takes for processing at each stage.

 

[GaP42]

I am designing a system for a client where they have carding wool weigh-ins throughout the day and also spinning ones. These can run for days and kept simple so the user can press a button it weighs the wool and then records the weight. The main information customer, shade etc will be duplicated, but they want both to run independent of each other.

Conceptually, from your Table Relationships, the Weight_Main table is a "job" table. A job may be the subject of many weighings. The Weight_Id PK / FK name is somewhat misleading and may then be more appropriate named as Job-ID or equivalent.
You indicated some concern that the record may be open for days as weighings are recorded at the push of a button. Each "push of a button" I would assume, simply ADDS a record to the Weight_Recorded table. Weighing can occur during carding or spinning. The Weight_Main record is not being updated, although the PK from that table is being applied.
Do you need to know whether any weighing is performed during Carding or Spinning? Is that what you are trying to achieve with the Type field in Weight_Main?
The other activity of updating information related to carding or spinning I assume is then about editing the Weight_Main record. But you also say "they" do not want them linking together... does this mean the data about spinning and carding for a particular job? That would seem ill-conceived. (or is it that there is some concern over not being able to update the same record from the two processes - spinning and carding?)
That would mean you have any number of Weight_Main records, each with weighings, that relate to a single customer Job/Order - Customer ID is not sufficient to report on the "job" and there is no managed JobID. Why not use a master Job which can have multiple sub-activities (Carding/ Spinning) in which weighings may be performed and data specific to those two types of activities may be recorded for the job?

 

[gemma-the-husky]

How do they currently store the information, presumably on spreadsheets?. That ought to give you a guide to where to start considering the process.