Recreating job board in Access

[2RUEXX]

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? 

 

[MajP]

Some version of that is likely possible, but we would need to know how this is stored and the data related. Can you post a relationship diagram or the table structure?

 

[arnelgp]

this can be done in Report.
almost same format in the Army (Production Control Board).

 

[2RUEXX]

Some version of that is likely possible, but we would need to know how this is stored and the data related. Can you post a relationship diagram or the table structure?

Hello,

We have 3 SQL tables from which we get the job order information. I make a query with each table connected by the job# and pull the relevant fields from each table. I'm not sure if this helps or if you need some more details. Let me know. Thanks!

 

[2RUEXX]

The most likely solution would be a crosstab query sorted ascending by week for a given week range.

Hi Pat,

Would the crosstab query at some point be made into a form or how would that work? Thanks!

 

[2RUEXX]

this can be done in Report.
almost same format in the Army (Production Control Board).

Hello,

If it is a report though, the user wouldn't be able to interact with the data, right?

 

[CJ_London]

We have 3 SQL tables

To be clear you mean access tables or sql server tables? Sql is a language

id also say that editing a 3 table query may not be possible- and a crosstab is not editable anyway

assuming your 3 table query is editable, you might be able to do this with a form using multiple sub form controls (you only need one sub form) and using the link child/master properties of the subform control to determine which row is displayed

might be an idea to upload a db with some example data

 

[CJ_London]

attached is a simple example of a job board - it doesn't necessarily do what you want since that isn't very clear to me from what you have provided so far but should point you in the right direction for you to achieve what you want. It is based on 3 tables from the Northwind template, just to demonstrate how a 3 table query might be updateable

@Pat Hartman - it does use a crosstab to populate the linkmaster fields since it saves a lot of coding

 

[CJ_London]

I didn't say that three-table queries are not updateable.

I wasn't quoting you, I was referring to my comment

editing a 3 table query may not be possible

 

[2RUEXX]

Hello,

Thanks for the replies. There's a lot that I don't know about access and visual basic, etc. so I appreciate the help. Most of my experience is with just basic queries and forms in access and I don't know visual basic.

I've attached a sample database which should give a better idea of where we are at right now. It has the three tables and a query based on them. And then I've made a form to represent a job tag.

Now I just need to know if it is possible to make a main form which takes this "sForm-JobTag" form and displays multiple of them for each record in the query. And I'd like it to be sorted by the MCJSubCategory field such that every column is a group of MCJSubCategories like Predator SS Std, Predator XS Std, SYN HP TT, etc. And then they are sorted by assembly week (assembly date) oldest to newest, like shown in the picture I posted at top.

Let me know if this makes sense and what other information would be helpful.

 

[2RUEXX]

The reason why there are three tables is because the actual MCJ/PRTBackorder table has so many fields that and so we now have extra tables to handle additional fields for any queries which require those additional fields (such as MCJCategory and MCJSubCategory). The versions of the tables I have made for this sample database are similar to the actual tables we use, but the actual tables in our db have many more fields. Is that bad database design though?

I've updated the example and it is attached.

 

[CJ_London]

Now I just need to know if it is possible to make a main form which takes this "sForm-JobTag" form and displays multiple of them for each record in the query.

Did you look at the example in post #10?

 

[2RUEXX]

Did you look at the example in post #10?

HI CJ_London,

Yes, thanks! So, how can I arrange that by the MCJSubCategory and Assembly week like shown in the image I posted?

 

[CJ_London]

you need to provide more information.

How many subcategories are there? - if more than 4 or 5 (the number you can show across the screen) how do you want to handle that? How many job boards to appear below (what is the maximum?

Do you want users to scroll down a form taller than the screen? or page it as per my example?

But in principle, the cross tab I used to populate won't work. Instead use a recordset that sorts by subcategory and week

And you'll need some additional text boxes across the top to display the subcategory

 

[2RUEXX]

you need to provide more information.

How many subcategories are there? - if more than 4 or 5 (the number you can show across the screen) how do you want to handle that? How many job boards to appear below (what is the maximum?

Do you want users to scroll down a form taller than the screen? or page it as per my example?

But in principle, the cross tab I used to populate won't work. Instead use a recordset that sorts by subcategory and week

And you'll need some additional text boxes across the top to display the subcategory

Ok, there are more than 4 or 5 subcategories. I was thinking maybe each assembly week could be a separate page of the form?

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

 

[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?

 

[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?

 

[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 #.