Exporting records based on date

[justinwright]

Honestly all this is new to me, so I'm in the process of fiddling with it when I have spare time so maybe I'll think of some specific questions at some point, but thank you for the help so far.

[HiTechCoach]

There is a lot to learn about how to properly design/normalize a relational database. It takes lots of practice and years to learn it.

I could just do it for you but then you would not learn near as much or maybe nothing.

I will be here if you have any questions.

[justinwright]

Okay so this is a total necro, but I didn't want to post a new topic since it was basically me revisiting the same issue after a good while.

I've looked over this thread a thousand times and fiddled with it a lot, and I think I have a little better grasp of what I'm asking about now.

HiTechCoach, I was attempting to use the HTC_DocMan4 db that is posted on your web site for externally stored document management.

The general premise here is that I'm trying to associate one record (field named NUMBERONE) to multiple attachments (as you mentioned, a one to many relationship). Though it doesn't seem like it matters, it might be prudent to mention they will be either .jpg, .bmp, or .pdf.

Right now the NUMBERONE field is only in the tblTracker table, if that matters. The other tables (bimDocsMgr, bmDocsMgrIndex, and bimDocsMgrRelated) are all designed as they are provided on your site.

I have the main form, frmMain, bound to the bimDocsMgr table (the "main form" is more of just a shell form to try to test things) with the frmDocMan pasted onto it as a subform (using drag/drop with the control wizard enabled).

The ideal scenario would be as follows:

Right now I have an input (seperate form entirely) for entering info for the NUMBERONE field, as well as the number itself. When this is input into the table (not the doc management table, a table called tblTracker), it creates a directory inside the folder that is being operated on named after the value for NUMBERONE (ie if the value was 123, after initial input there would be a folder inside the main directory called 123). It's on a shared drive at S:\Tracker\123, where the back end split when I split it will be located at S:\Tracker\.

I will work on looking at the FSO objects you mentioned for moving/manipulating data myself (though I'm sure I'll be posting some question that stumps me at some point, but I won't trouble you with that), but my question was more related to usage of the HTC_DocMan4 example db.

How would I restrict the view of the attachment list to ONLY those in the folder for the NUMBERONE value in question? For example, opening the form for 123 will show all the attachments associated with the 123 value for NUMBERONE, also located in the \123\ folder. It would NOT show attachments for the 456 NUMBERONE value, located in the \456\ folder. Whenever I try to use it, it is listing all of my attachments, regardless of record or folder location. I'm familiar with DoCmd.OpenForm and using the where clause to restrict a record; am I on the right track by assuming that something like this is used on frmMain?


There will be multiple people accessing this at once, maybe as many as 4 or 5 people at the same time (not inputting, just using other misc lookup functions, reports, etc).

[HiTechCoach]

It is possible to do what you want. The key is getting the foreign key (hte table tblTracker's primary key) stored someplace in the DocMgr table to create the relationship. Where are you storing the foreign key to tblTracker?

[justinwright]

I didn't want to use the autonumber thing, so I set no primary key. I know this is bad DB design, but I didn't see much way around it.

Now that I think on it, though, I can set the NUMBERONE field itself as the primary key (since there will never be duplicate values of NUMBERONE).

[HiTechCoach]

I didn't want to use the autonumber thing, so I set no primary key. I know this is bad DB design, but I didn't see much way around it.

Now that I think on it, though, I can set the NUMBERONE field itself as the primary key (since there will never be duplicate values of NUMBERONE).

You can always use the auto number for the primary key. In your case I think it is critical to do.

In 1000+ of database I have created (Access and SQL Servers) the primary key has always be system assigned.

Best Practice Rule: Every Table must has a primary key. In the better database engine this is a requirement.

Since Access does not require a primary key for every table, it is the most common cause of database issue I have to fix for databases created by others.

In a well designed database you would NOT use the NUMBERONE as primary key or in any relationships. It will be an indexed filed with No Dups. The primay key would be hidden from the user. It would appear to the user that the NUMBERONE field is the primary key. This is a common practice in well designed database systems.