Design question

[ocm]

Greetings,



I start creating the attached access db to simplify the data entry process currently done using Excel. We also purchased a third party application (for data definition purposes) that allows us to import what we already have in place. But, in order to export to the third party application:

1. It’s limited to 10 fields
2. The file has to be in CSV format

Since we’re limited to 10 fields, we currently input multiple fields in a single cell and use save as CSV for export.
What design changes do I need to make in Access to include everything I have in my (frmDataEntry2) so I can create a report that I can export as CSV?

TIA,

Regards

[June7]

Is use of third party app a one-time event or is this a regular routine? Why is the third-party app needed?

Regardless of the 3rd party app limitations, you are not limited to 10 fields in the Access database. Design the database appropriate to the data you need to manage. Exporting to a CSV to accommodate the 3rd party limitation can be accomplished with calculations in query that concatenate fields.

[orange]

When you use Excel, can you supply the 3rd party tool with the proper data and format?
Why did you/someone select Access?
Does the 3rd party tool support ODBC? Have you or others spoken to the 3rd party tool supporter about options?

As June7 asked - What is the 3rd party tool?
If the 3rd party tool has limited interactivity - please explain/describe any decisions to use Excel, then Access.

Does the 3rd party tool do something your company needs? Are there options?

If we are to help you, you're going to have to give us more and relevant info.

[orange]

When you cross post, please advise the readers that you have done so.

Cross Posted
http://www.access-programmers.co.uk/...d.php?t=260009


ocm please see this link about posting

[ocm]

Thanks for your reply and sorry for cross posting.
To answer your questions, end-users were using Excel to document info about data elements and found it to be very time consuming. It was determined to use access forms instead. The third party tool will be used regularly and is needed for other purposes.

Regards,

[orange]

You may want to research data dictionary/data repository.

[ocm]

Thanks orange,

As you suggested, I’ve attached my sample db in mdb format. Is it possible to create a query/report based on my second form "frmDataEntry2"

TIA,

[June7]

Maybe I don't understand purpose of this db. As far as I can tell, this is not a database. Just contains a data dictionary table. There are no tables for DATA. Such as:

StudentID	FirstName	LastName	DOB
1	James	Carson	2/15/2003
2	Amy	Pearson	7/22/2005
3	Darrel	Wang	4/30/2004

[orange]

Is it possible to create a query/report based on my second form "frmDataEntry2"

The quick answer to your question is Yes, But you really can't do this with 1 table.
You need to identify the business rules; then design a database that supports those rules; then create the interfaces for the various users/roles.

Your form is interesting and shows you have done some thinking. A data dictionary is a very complex data base or can be if it is to be used to support development, production, planning etc. There are some small data dictionary/databases available to do limited things. I would not advise you to start building such a data base until you have done more design; and looked seriously at the cost of building, using and supporting versus buying or buyting access to a commercial package.

I have worked with various data dictionaries since 1977 - home grown, Datamanager, DesignManager, Predict, Rochade

There is free access based "data dictionary/documenter" here. I don't use it, but it has been recommended on various forums. Until you know what you really need, it may or may not meet your requirements.

[ocm]

Thanks Orange,

I found the link you provided to be very useful. At this point I’ll follow your advise not to start building such a complex database. In the meantime, I still would like to create a query/report based on my second form: e.g. what I have in italics in my 2^nd form (Business process, Example value(s), Form(s), Field Name, Notes, etc.) will feed to FieldDefinition field. And Source Table, source Field ….Dev Notes) will feed to FieldTechnicalDefinition. So, do I need to create another table w/ the above field name, or is there other workaround?

TIA

[June7]

Okay, I might be catching on to what this is for.

Need fields in table for the italicized items if you want them available for a report.

About the checkboxes for Reportying System Info, should more than one selection be allowed?

[orange]

Here is a data model for a basic data dictionary from Barry William's site.. And this one doesn't deal with Business Processes.

e.g. what I have in italics in my 2^nd form (Business process, Example value(s), Form(s), Field Name, Notes, etc.) will feed to FieldDefinition field. And Source Table, source Field ….Dev Notes) will feed to FieldTechnicalDefinition. So, do I need to create another table w/ the above field name, or is there other workaround?

You need to look at this or other models to get some idea of what tables and what relationships are involved.

For example:

A Record can contain Fields
A Field can have a unique identifier
A Field can have a Name for use in Reports or Forms or Documents
A Field could have an Alias Name for use in Java
A Field could have an Alias Name for vba
A Field could have an Alias Name for C#
A Field can have physical attributes depending on the context
A field value eg CountryCode could be derived from United Nations and have an ISO 3166-2/3 abbreviation
..etc etc.

You have to decide what is in scope of your "Data Dictionary"

Here's a link to some of the tables in the Oracle Data Dictionary

What do you want your dictionary to store?
Who will use it? Designers, Analysts, Programmers, DBA, MaintenanceStaff
Will it be integrated with a Development LifeCycle?
Will you use it for DataModel, Database, Program, Form generation?

This can be a very large topic if you intend to do Information/Data Management for a large organization.