Recreating job board in Access

[2RUEXX]

Yes. Breaking tables because you have too many columns is indicative of a bad allocation of attributes to begin with.

Here is a sample using the crosstab query. It needs adjustment though to work in the real world. Reports don't naturally grow columns. You either need to fix the number of columns by using the column headings property of the crosstab OR if you have a table that defines the SubCategory, you can open the crosstab query and add that table with a left join. That should produce a recordset with all the columns even when the recordset doesn't contain data with all values. HOWEVER, reports are limited to two pages in width (22") so if you have more columns than will comfortably fit in 22", you would have to export the crosstab to Excel and format it there.

Ok, thanks! That is looking pretty close to what we want. Would it be possible for me to use a query like this then as the data source for a form so I could style it how we want it?

 

[Pat Hartman]

What do you mean, style it like you want it? I formatted it as your sample, using the query. Some of the parts are hard coded since I didn't see any table values that seemed appropriate. Look at the "base" query. I think that may be what you want to format rather than the report. All I formatted in the report was the font and size for the "box"

You probably also need criteria in the query. The query includes the full date because that is what you need to sort properly when you get to year end. Otherwise you'll end up with Dec 23 following Jan 24 because 12 is bigger than 1. Or if you have a full year, you'll see Oct following Jan because 10 comes after 1 and before 2.

You can display the crosstab in an unbound form or report by simply dragging the query onto the form or report. That may even give you the sideways "grow". However, the width of a report is limited to 22" and that is firm. I don't remember the form limit. It may be the same. So, the whole thing is fairly simple if the number of columns is limited and won't exceed the width of the form or report.

 

[CJ_London]

When you say use a recordset, do you mean in the form?

Yes. But looks like you are following pat’s solution, she has done this more than me, and I don’t want to duplicate effort

 

[2RUEXX]

What do you mean, style it like you want it? I formatted it as your sample, using the query. Some of the parts are hard coded since I didn't see any table values that seemed appropriate. Look at the "base" query. I think that may be what you want to format rather than the report. All I formatted in the report was the font and size for the "box"

You probably also need criteria in the query. The query includes the full date because that is what you need to sort properly when you get to year end. Otherwise you'll end up with Dec 23 following Jan 24 because 12 is bigger than 1. Or if you have a full year, you'll see Oct following Jan because 10 comes after 1 and before 2.

You can display the crosstab in an unbound form or report by simply dragging the query onto the form or report. That may even give you the sideways "grow". However, the width of a report is limited to 22" and that is firm. I don't remember the form limit. It may be the same. So, the whole thing is fairly simple if the number of columns is limited and won't exceed the width of the form or report.

Ok, thanks! So, forgive my ignorance, but as you have it in your example, the cross tab query only shows the First record for each MCJSubCategory. How can I get the query to show every job that exists within a MCJSubCategory for a given ship week and not just the first one?

 

[Pat Hartman]

The example is working with the tables in your sample database and as I mentioned, the design is incorrect. You have forced a 1-1-1 relationship and so there would never be "many" in the lowest table. There is no "many" in the test sample either.

Did you try the query on your actual data? If it doesn't produce the correct results, the relationship between the tables is incorrect.

Although you can bind a form to a crosstab query, the data will NOT be updateable since crosstab queries do not produce recordsets that allow updates.

 

[LarryE]

Hello,

At my work, we used to have a physical job board which had a bunch of tags to represent each job we we're working on. Each tag had customer information and the machine information and the job #, etc. Now, we use a query in MS access for this information so that it is electronic as opposed to having a physical board. I am wanting to develop a form in access which can look similar to the physical job board in the sense that the form would be like a table with rows and columns of box-like tags and each tag would contain the jobs information. I've attached a photo of what it is I am looking to create. As you can see, the columns are based on machine type and the rows are based on grouping assembly weeks together.

Is this possible in Access? View attachment 107784

@2RUEXX:
Do you need help with constructing your application table and field foundation properly? As Pat Hartman and others have pointed out, no matter what kind of query you construct, it cannot give you what you want if it is built on a faulty foundation.

It appears from the files you have attached and what you have stated, you are attempting to use ACCESS as you would a spreadsheet, which of course, will not work. You should build your tracking system based upon your business model.

From what I can tell so far, your business model consists of:

1. Customers who issue Purchase Orders for your product(s)
2. Each PO may have multiple line items, a PO Number and a Date
3. Each PO item is assigned a Job Number
4. Each Job Number is assigned an Asssembly Week
5. Each Assembly Week, you construct your product using one or more Machine Types
6. Each Machine Type contributes to a finished product that gets shipped

Does this sound correct to you? If not, please make corrections so someone can assist you.

 

[2RUEXX]

@2RUEXX:
Do you need help with constructing your application table and field foundation properly? As Pat Hartman and others have pointed out, no matter what kind of query you construct, it cannot give you what you want if it is built on a faulty foundation.

It appears from the files you have attached and what you have stated, you are attempting to use ACCESS as you would a spreadsheet, which of course, will not work. You should build your tracking system based upon your business model.

From what I can tell so far, your business model consists of:

1. Customers who issue Purchase Orders for your product(s)
2. Each PO may have multiple line items, a PO Number and a Date
3. Each PO item is assigned a Job Number
4. Each Job Number is assigned an Asssembly Week
5. Each Assembly Week, you construct your product using one or more Machine Types
6. Each Machine Type contributes to a finished product that gets shipped

Does this sound correct to you? If not, please make corrections so someone can assist you.

Hi Larry,

Yes, that is basically correct as it pertains to machine sales. We also sell parts, but the job board is specifically for machine orders.

The PO is first assigned a sales order and the sales order is then assigned the job #.

 

[2RUEXX]

Yes. Breaking tables because you have too many columns is indicative of a bad allocation of attributes to begin with.

Here is a sample using the crosstab query. It needs adjustment though to work in the real world. Reports don't naturally grow columns. You either need to fix the number of columns by using the column headings property of the crosstab OR if you have a table that defines the SubCategory, you can open the crosstab query and add that table with a left join. That should produce a recordset with all the columns even when the recordset doesn't contain data with all values. HOWEVER, reports are limited to two pages in width (22") so if you have more columns than will comfortably fit in 22", you would have to export the crosstab to Excel and format it there.

Hi Pat,

I was curious as to what is the better way of dealing with a table that has too many fields rather than making a new table with the same primary key. Thanks!

 

[LarryE]

Hi Larry,

Yes, that is basically correct as it pertains to machine sales. We also sell parts, but the job board is specifically for machine orders.

The PO is first assigned a sales order and the sales order is then assigned the job #.

If that's the case, then a design something like the following is needed, although I don't know ALL of the fields that are required, the tables and their relationships might work:

Of course, you will need input forms in order to enter data into each table and I can help you with that if you wish, but the basic design follows generally accepted rules. You keep each piece of information in one place only and use referencial integrity (Primary Keys and Foreign Keys) to keep track of all of it.

For example, as you can see, each customer may have multiple Orders and Jobs so the TblJobs holds Sales Order and Job Number data. It has a Foreigh Key CustomerID which is related to the Primary Key CustomerID in the TblCustomers table. When we build the input form for Sales Orders and Job numbers, we will automatically fill in the CustomerID from the TblCustomers table. That process gives us the link for each customer and sales order and job.

Also, you NEVER use special symbols (#, -, +,@ etc. etc.) in table or field names so I got rid of your # symbols in the old field names.

This example is VERY preliminary, but you can get the idea of how relational systems work.

 

[Pat Hartman]

I was curious as to what is the better way of dealing with a table that has too many fields rather than making a new table with the same primary key. Thanks!

Breaking up tables is not arbitrary. You need to analyze each piece of data and identify entities and attributes. Entities are things like customers, employees, partnumbers, vehicles, Jobs, and attributes are pieces of information about an entity. Most entities have a "name" attribute. CustomerName PartName, Model, FirstName, LastName, JobName, etc. Beyond that anything goes. The thing to watch out for are repeating groups. Look for attributes that are suffixed with numbers. They almost certainly belong as rows in a separate table rather than columns in one table.

Do some reading about the first three normal forms. If you don't understand what the description means, move on and find a different site. Way too many get all wrapped up in relational algebra which is too obtuse for a novice to grasp.

I can't tell much from the three tables but:

1. Shipped seems to be redundant. You can tell whether or not an item is shipped when it has a value in ShipDate.
2. Your names are too loosy goosey. They have no discipline so I can't tell if they are foreign keys or text.
3. I have no idea why there is a separate Freight table or what the category and subcategory even mean in this context. These look like attributes that belong in the shipping header table whatever that is called.
4. The third table also doesn't make any sense but it might have something to do with "movement" through the shipping process.

And, as I said earlier - NEVER include an autonumber UNLESS it will be the primary key.

 

[LarryE]

@2RUEXX
What makes you believe that you have too many fields in a tble? Did ACCESS give you a warning message?

 

[2RUEXX]

@2RUEXX
What makes you believe that you have too many fields in a tble? Did ACCESS give you a warning message?

The example database I uploaded has much less than the actual tables. In the actual MCJ table, we have around 67 fields. Access didn't warn us but the table was already so large so that new fields have been added to tables like MCJFreight which I realize now doesn't seem to be good practice.

Based on what we have though, I have made the crosstab query and it is so close to what I'm needing. However in this crosstab query, I am only able to show the first job within each subcategory and ship week. See this attached example that Pat provided. It only shows the first or last job tag depending on how you setup the Total field for the OptionsDesc field. How can I get the query to show every job that exists within a MCJSubCategory for a given ship week and not just the first one? Perhaps it is not possible based on our table structure?

The attached photo shows what I'm looking to do based on our current data.

 

[LarryE]

The example database I uploaded has much less than the actual tables. In the actual MCJ table, we have around 67 fields. Access didn't warn us but the table was already so large so that new fields have been added to tables like MCJFreight which I realize now doesn't seem to be good practice.

Based on what we have though, I have made the crosstab query and it is so close to what I'm needing. However in this crosstab query, I am only able to show the first job within each subcategory and ship week. See this attached example that Pat provided. It only shows the first or last job tag depending on how you setup the Total field for the OptionsDesc field. How can I get the query to show every job that exists within a MCJSubCategory for a given ship week and not just the first one? Perhaps it is not possible based on our table structure?

The attached photo shows what I'm looking to do based on our current data.

If your table structure followed the ACCESS relational model, you probably could do what you want, but your model doesn't use relational concepts so, no it won't. I believe Pat is doing the best she can do for you using your current design, but it will never give you what you want until you re-design the structure from the beginning. I am working on a model that may work for you using the relational model and will attach it for you to study soon.

I have one additional question. When you view the tag form you have created, what do you want to do with it? Just view it on a screen, print it on a report for viewing or what?

Are you taking about the MCJFreight table? Why is there 67 fields? Please go ahead and attach the real table so we can see what is in there.

 

[Pat Hartman]

I told you back in post #25 that your structure was incorrect. your relationship is 1-1-1 so there can NOT BE many in the right most table. You need to rethink the schema so you can figure out what occurs "many" times for a job.

 

[LarryE]

@2RUEXX
Attched is an example file using relational theory. I took your example data and converted it so you can see what a relational system looks like. It uses input forms where you keep data in only one place and can see everything in one place. I was able to create a job tag report like you wanted and there is a button at the bottom to open it. The input form appears when you open the file and disappears when you open the report, but then re-appears when you close the report. I hope this gives you some idea of what Pat and I and others were talking about. Good Luck.

 

[2RUEXX]

I told you back in post #25 that your structure was incorrect. your relationship is 1-1-1 so there can NOT BE many in the right most table. You need to rethink the schema so you can figure out what occurs "many" times for a job.

Hi Pat, I am studying Larry's example and it is making more sense now. I was confused at first but am working through understanding it better. Thanks for your help!

 

[2RUEXX]

@2RUEXX
Attched is an example file using relational theory. I took your example data and converted it so you can see what a relational system looks like. It uses input forms where you keep data in only one place and can see everything in one place. I was able to create a job tag report like you wanted and there is a button at the bottom to open it. The input form appears when you open the file and disappears when you open the report, but then re-appears when you close the report. I hope this gives you some idea of what Pat and I and others were talking about. Good Luck.

Thanks Larry! I am analyzing it and I may reply later with questions.

 

[LarryE]

It appears those products are industrial wrapping machines. Do you manufacture them? I asked because maybe the machine and its options should be selected and assigned with the order and not with the Finished Products form and table.

 

[2RUEXX]

It appears those products are industrial wrapping machines. Do you manufacture them? I asked because maybe the machine and its options should be selected and assigned with the order and not with the Finished Products form and table.

Hi Larry,

Yes, they are stretch wrap machines. This is our company: Highlight Industries | Home

 

[LarryE]

Hi Larry,

Yes, they are stretch wrap machines. This is our company: Highlight Industries | Home

OK, yes I thought so. Please understand the file I attached is JUST AN EXAMPLE. There are many duplicate records that were converted and some of the fields will certainly belong in a different table.

Each ACCESS application table design is always driven by the business process. So that said, if you wish to continue down this path using ACCESS, I have a few more questions:

1. When a customer issues a Purchase Order, do they ever order more than 1 machine from you?
2. Does each PO result in a single sales order number? Or a can a single PO result in multiple sales order numbers?
3. Does each sales order number result in a single job number? Or could it result in multiple job numbers?
4. What does the B or BB designation mean? You had it in a table with a # name, which we cannot use in ACCESS. Is it a machine number of some sort?

The answers will determine which tables need to be developed.