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?

 

[Pat Hartman]

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

 

[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

 

[Pat Hartman]

@2RUEXX Welcome aboard
Your tables don't make sense. You shouldn't have three tables with the same PK. I suggested a crosstab query because your original report looked like a matrix.

Crosstabs are NOT updateable so although you can display them on a form, you wouldn't be able to update anything.

I agree with the others. We need to see actual tables with actual data. You can obfuscate data if necessary but don't mess with the PK-FK relationships.

 

[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

 

[Pat Hartman]

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

I've posted many times links to my "bound denormalized form" sample that populates a form with data that looks like it comes from a flat table or a crosstab but which comes from a fully normalized schema.

 

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

 

[Pat Hartman]

1. NEVER include an autonumber in a table UNLESS it will be the PK
2. You have no actual relationships defined because EVERY table has JobNumber defined as the PK and that can't be correct. You have essentially created a 1-1-1 relationship in which case there should be ONE table Not three.

Please fix the tables and change the names to eliminate special characters and embedded spaces.

 

[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

 

[Pat Hartman]

Is that bad database design though?

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.

 

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