Trying to tie everything together into one report from database

[zach48191]

Helo,

I am learning MSACCESS 2010 for use at work to create a database instead of excel database for a program. I am having a hard time trying to tie them all together and print one report. I have all the tables that I want, so it is mainly just trying to put it all together.

Here is what I am working with:

So for my tables I have for example

Module 1

Categories:

Part number
Serial number
Board part number
Board serial number
Completion date

Module 2

Categories:

Part number
Serial number
Board part number
Board serial number
Completion date

Module 3

Categories:

Part number
Serial number
Board part number
Board serial number
Completion date



Module 4

Categories:

Part number
Serial number
Board part number
Board serial number
Completion date

Final Assembly

Categories:

Part number
Serial number
Module 1 part number
Module 1 serial number
Module 2 part number
Module 2 serial number
Module 3 part number
Module 3 serial number
Module 4 part number
Module 4 serial number
Completion date

NMR

Categories:

Part number
Serial number
NMR date
Discrepancy
Action taken
Completion date




Ideally I would like to have a report on certain NMRs that will show everything in it for which module it is tagged with. For example module 1 (SN 91) had an NMR for September. I would like to run a September NMR report and it should show the NMR for SN 91 on module 1.

I am trying to take baby steps as I will continue to build from this report generation to more elaborate reports later. More on that when I first figure this out!

Any help would be GREATLY APPRECIATED!

~Thanks
Zach

[ranman256]

Here is where you use queries.
join the tables on the common field, then run.

[zach48191]

How would I join the common fields? I know about relationships, but which field do I connect?

[June7]

That's what primary key and foreign key fields are for.

Looks like you have compound key with Part Number and Serial Number fields. Or maybe just one of those fields is key and the other should not be repeated in all tables. So join Module1 table with the two Module1 fields in Final Assembly and same for others.

Then you have PartNumber and SerialNumber fields in FinalAssembly. Are this primary key? Are they saved as foreign key in NMR?


Really don't understand why you have 4 tables for modules.

A final assembly can have only 4 modules? Will always have 4 modules?


IMO, manufacturing type db is one of more difficult to build. Fairly common topic in forum. Did you search?


Recommend no spaces in naming convention.

[orange]

zach48191,

You may get some insights from links in this post

[zach48191]

Example Database.zip

I have created my database template to illustrate the example I have presented. I have ran queries to facilitate some of the work, but this is fine for a specific field. My concern is do I need to make a query for each parameter of the report and then combine the queries into a report? I have tried this and did not work out as intended. I think my relationships are not exactly like I had planned it.

If anyone could have a look to see if my relationships, and answer my question with the combination of queries into a report would be much appreciated!

Sorry for the long delay, I have been working on other projects and now this one is back up at the top of my list to do!

[June7]

Table relationships and data make no sense to me. NMR_NUM links to Serial_NUM but the values in these fields are not the same. There are no associated records.

Why do Modules 1 and 4 have additional fields not in the others? If fields are all the same should be one Module table, not 6.

Have you searched forum for threads on manufacturing database?

This db is definitely not at a point where you can consider building forms for data entry/edit, much less a report.

[zach48191]

The table relationships I was hoping that the NMR number would be tied to the SN of each unit. Maybe my concept of this is wrong idk.

Each module has different categories associated with them, hence why the different fields on some. There will be more fields that differ between each module, but I tried to keep it simpler this way.

I did a search on manufacturing database, but the majority of them wanted different forms and this one I want 1 form from a lot of queries.

This is why I wanted to start here by using baby steps to get my db up at least.

So quick question then when I do a relationship with one it has to show up in the other table as well for it to be linked?

[June7]

There must be common data for tables to be linked. This is the purpose of primary and foreign keys. If there is no common data, how should a query know which records are related? Understanding keys is crucial to building relational database. You must understand this before proceeding.

Normally, a form can do data entry/edit for only one table - assuming using bound forms.

[zach48191]

OK, I have a question then. Can I run a query off of multiple tables, then run a report on the query?

Attachment 22682

I have updated my database based on your recommendations. Please have a look to see if it is ok.

[June7]

Yes, report can be based on a query with multiple tables. However, if there are multiple 1-to-many or many-to-many related tables, probably should be report/subreports.

The attachment link does not work - errors as 'Invalid'.

[zach48191]

Sorry I tried to copy it and it didn't work here it is again:
Example Database.zip

I prefer query as then I can use it in excel rather than in a form, but have a look at my relationships and see if that makes more sense to you

[June7]

At least the links are on common data.

However, still doesn't quite make sense. Each Module has NMR Number, why not just link Module NMR to NMR NMR_NUM and NMR_NUM links to Final_Assemby NMR?


This still is not a normalized data structure. I would not have 6 module tables nor 6 module fields in Final_Assembly


Advise not to use spaces or special characters/punctuation (underscore is exception) in names.

[zach48191]

I will look into the NMR and change the naming convention for each one to your advise. I would like you to clarify on what you mean with the 6 module tables nor 6 module fields in final assembly. Each module goes into the final assembly to make it a final assembly. The final assembly gets a new PN so I want to integrate the modules into the final assembly so when I want to search for a module, I also want to know which final assembly it went into for future use. That is my logic, what is yours behind this?

As always I really appreciate this feedback you all have provided so far. I will update and upload my updated database tomorrow morning for you to look at June7, its 5pm here I'm going home!

[June7]

I am still not fully understanding relationships. Why is WorkOrder in modules and Assembly? Why is Module_S/N in NMR and NMR_Number is in each Module (a 2-way relationship - makes no sense)?

What exactly is an NMR?

I really need a better understanding of your process.

As I said, manufacturing not easy and I've never built one.