Add Existing Fields

[mike60smart]

Hi Steve

Your Main Table needs a lot of work to normalise the fields.

You have lots of Repeating Groups as shown in the attached screenshots.

All of these area should be records in a seperate table and not fields in the Main Table.

[madpiet]

Do (Discrepancy, Discrepancy Type, Disposition, Cause) all describe one "thing"? if so, what? In order to help you fix this, we need you to explain what the columns "describe". I've been down the repeating fields road before. It's gonna be ugly going forward. Better to fix it now. I can help you, but I need information first.

Like do all of the columns with the same maximum subscript (5) belong together?

[madpiet]

Okay... having looked at the form it makes a little more sense...
It's like a batch of manufactured items that you're reporting on defects? So the first 4-5 columns from that table (NCMR Number, N Part Number, N Description, Product Family, Lot Quantity) would go in one table, and then the Discrepancy details about that lot would go into a child table (because one lot can have zero or more discrepancies)

Each Discrepancy has a single type. So the Discrepancy 1-5, Types 1-5 (the whole table thing), and Notes 1-5 all go together? If that's the case then all of those would go in ONE table with the [ID] from the NCMR Main Table as a child. (So it would be Indexed: Yes, Allow Duplicates: Yes, Allow Nulls: No).

What does the N prefix mean?

Mike is right, though. If you want to use this data for anything without going through lots of pain, you should normalize. That's what my questions are about. "What 'thing' is this describing? Is this 'thing'/'entity' related to that one? If so, how?" etc.

[steveo0707]

Hello June7,

When I have VBA code that allows me to Save the current record and I use the Select current record on the printer options box when I go to print. In the Report, the select current record is greyed out when I print. But, I do understand from your note above, that I need to filter that out before printing. Makes sense, I didn't think of that.

Thank you for all your help.

[June7]

I never even noticed that option on printer driver. However, if you want to print without user having to interact with printer driver, that might not be available in VBA methods.

[madpiet]

Hi Steve

Your Main Table needs a lot of work to normalise the fields.

You have lots of Repeating Groups as shown in the attached screenshots.

All of these area should be records in a seperate table and not fields in the Main Table.

Forget form design for a minute. Try to find some records with your table structured like this. I don't think it's going to go well. Or write some queries. That should be really fun. You're not going to OR all 6 or whatever repeating columns are you when searching for something?

[mike60smart]

Hi Steve

You need to step back a bit and forget about trying to print any reports.

You need to sort out your tables first and then think about reports at the very end.

[madpiet]

Hi Steve

You need to step back a bit and forget about trying to print any reports.

You need to sort out your tables first and then think about reports at the very end.

If you normalize first, the rest of this becomes trivial.
you're trying to treat Access as if it were Excel, but with more complex forms. It's not. Do yourself a favor and read a good article on normalization. Maybe "Database Design for Mere Mortals"

Or post a copy of your database with a few dummy records.

I've inherited databases like the one you're building - you're in for a world of frustration if you don't fix it. Let us know when you give up.

[Welshgasman]

Here are a few links that @Micron normally posts.
https://www.accessforums.net/showthr...553#post510553