ACCESS FEED: Re: [AccessDevelopers] Re: New DB Design Ideas

Minggu, 03 Juni 2012

Re: [AccessDevelopers] Re: New DB Design Ideas

Thanks Duane. That gives me some ideas to work on.

On 6/1/2012 10:56 PM, Duane Hookom wrote:

I would think there would be a table of files and then a table of counts with a single field for the count and a field for storing [Count of what] like Placements, Recalls, Keeper, etc. I would pull the [Count of what] value from a small lookup table that has a record for each type of count.

You may also need to create a table of file tasks that stores a record for every task performed on every file. Again you will need a small lookup table that has a record for each task type.

Duane Hookom
MS Access MVP

To: AccessDevelopers@yahoogroups.com
From: mossj5@comcast.net
Date: Fri, 1 Jun 2012 19:07:16 -0600
Subject: Re: [AccessDevelopers] Re: New DB Design Ideas

I doubt that many others are as smart as you when it comes to Access!.

I work for a Collection company, so I'm walking a thin line here, due to confidentiality. Even if I could upload a sample file, it would be meaningless. It's a fixed character length file.

I need to track what files I download, process, and then upload. I need to record downloaded file names, along with what each file processes. For example, a new placement/biz file is named something like 5282240.dl. After processing, I need to record the Name of the file, the Count of placements and the Session(s). For maintenance files, 22644557.mt, I need to record the Count, Session(s), Amount, Maintenance count, Recall count, Keeper count, and Secondary count. Some files will have the Count, Session(s), Amount, Maintenance count, Recall count, and Keeper counts and some files will have just the Secondary count. Other files will have just the Maintenance count, Recall count, and Keeper counts. I also need to know if the file does not have any of these counts.

I also need to record what time certain tasks are completed or certain files are created and uploaded. Some of the files that I upload and record upload time will also have a confirmation time that the file was uploaded. I need to record that certain SQL scripts have been run and sometimes the results, usually just a count, sometimes a Y/N.

Each of these tasks needs to be completed at a certain time of day. There are also tasks that need to be completed the first of the month, the 15th of the month, and each Friday. For that I would need some sort of popup form as a reminder to complete the task. I have found several samples that should work.

The file I uploaded has each task and what I need to record for each.

I started to design a DB, on paper of course, with a table for each Count; New Placements, New Placement Session(s), Maintenance Count, Maintenance Session(s), Maintenance Amount, etc. That's the part that got messy.

I then designed a DB with the Tables listed in the document. Basically a table for New Placements/Biz, Maintenance, Scripts, etc. I went that route, even though it meant having empty field, since that would satisfy the requirement that says we need to know if each file does not have a certain count. A Null value would indicate such.

I certainly hope this clears up some of the confusion. I usually do not have time at work to write such a long post.

Many Thanks for all assistance,
John Moss

On 6/1/2012 12:49 PM, Duane Hookom wrote:

Sorry, that file means nothing without you telling us about your application and data.
Maybe someone else is smarter than I am.

Duane Hookom
MS Access MVP

To: AccessDevelopers@yahoogroups.com
From: mossj5@comcast.net
Date: Fri, 1 Jun 2012 18:41:25 +0000
Subject: [AccessDevelopers] Re: New DB Design Ideas

I uploaded a PDF with the list of tables and what fields they should be in. The file is called tasks-db.pdf. I can't send any sample files, since they are confidential.

Thanks for your help.

--- In AccessDevelopers@yahoogroups.com, Duane Hookom <duanehookom@...> wrote:
>
>
> I don't think you are understanding normalization. Can you provide about 5 sample records? I really think each type of count should create its own record with a field that identifies what is being counted. You still aren't providing a good description of the data.
>
> Duane Hookom
> MS Access MVP
>
> From: mossj5@...
>
>
> They are just counts. They could be blank some days and have data on other days.
>
>
>
> I was thinking of separate tables for each field, but that gets messy.
>
>
>
> --- In AccessDevelopers@yahoogroups.com, Duane Hookom <duanehookom@> wrote:
>
> >
>
> > John,I don't think you understood my question. I am curious what your data is storing. Are these fields tallies/counts? I wonder if there should be separate records for each. Duane HookomMS Access MVP
>
> > To: AccessDevelopers@yahoogroups.com
>
> > From: mossj5@
>
> > Date: Fri, 1 Jun 2012 05:52:40 -0600
>
> > Subject: Re: [AccessDevelopers] New DB Design Ideas
>
> >
>
> > That's the problem I'm struggling with.
>
> > Some files may have those fields, some may not. But if they don't,
>
> > that info is needed as well.
>
> >
>
> > On 5/31/2012 9:29 PM, Duane Hookom wrote:
>
> > What are these fields for:
>
> > Maintenance
> > Recalls
>
> > Keepers
>
> > Secondary
>
> >
>
> > I wonder if this structure is un-normalized.
>
> >
>
> > Duane Hookom
>
> > MS Access MVP
>
> >
>
> > To:
>
> > AccessDevelopers@yahoogroups.com
>
> >
>
> > From: mossj5@
>
> >
>
> > Toby,
>
> >
>
> > Here's the data each file may or may not have:
>
> >
>
> > Transaction Count
>
> > Session #
>
> > Amount
>
> > Maintenance
>
> > Recalls
>
> > Keepers
>
> > Secondary
>
> >
>
> > If I use a single table with a field for each
>
> > entry, I'll end up with empty fields. For example,
>
> > if file one has these fields:
>
> >
>
> >
>
> >
>
> > Transaction Count
>
> > Session #
>
> > Amount
>
> > Maintenance
>
> > Recalls
>
> > Keepers
>
> > and file 2 has these fields
>
> > Maintenance
>
> > Recalls
>
> > Keepers
>
> > and file 3 has this field
>
> >
>
> > Secondary
> >
>
> > then the first set of fields will be empty for
>
> > file 3 and there will be empty fields for file 2..
>
> >
>
> > But as I type and think this through that may not
>
> > be a bad idea. That way you'll know if the file
>
> > had only transaction or transactions and
>
> > maintenance. A Null valuewould mean either no data
>
> > was entered or it was missed.
>
> >
>
> > Does that make sense?
>
> > Thanks,
>
> > John
>
> >
>
> > On 5/31/2012 4:05 PM, Toby Bierly wrote:
>
> > You should
>
> > be able to setup a table with the
>
> > following fields:
>
> >
>
> > Date,
>
> > Client, File_Name, Count,
>
> > Transaction_Number, Amount, etc.
>
> >
>
> > You would
>
> > enter multiple files on any given date,
>
> > and record the client it was for, and
>
> > the other info you specified.
>
> >
>
> > I'm not
>
> > sure how you are ending up with empty
>
> > rows.
>
> >
>
> > Hope this
>
> > helps,
>
> > Toby
>
> >
>
> >
>
> > -----Original
>
> > Message-----
>
> >
>
> > From: AccessDevelopers@yahoogroups.com
>
> > [mailto:AccessDevelopers@yahoogroups.com]
>
> > On Behalf Of yshopper
>
> >
>
> > Sent: Thursday, May 31, 2012 1:29
>
> > PM
>
> >
>
> > To: AccessDevelopers@yahoogroups.com
>
> >
>
> > Subject: [AccessDevelopers] New
>
> > DB Design Ideas
>
> >
>
> >
>
> >
>
> >
>
> >
>
> > I am starting a new
>
> > project that will track Daily Tasks. One
>
> > of the tasks is to download and process
>
> > files. Each day the number of files will
>
> > change. I need to record the name of the
>
> > file and several other pieces of info
>
> > after the file is processed; i.e. Count,
>
> > Transaction Number, Amount, etc.
>
> >
>
> >
>
> >
>
> > Some days there will be only 10-12 files,
>
> > other days there could be upwards of 70-75
>
> > files. I need to record each file for each
>
> > day. Also, these files are associated with
>
> > 3 different clients.
>
> >
>
> >
>
> >
>
> > How would I design a table(s) that will
>
> > allow this type of data? I have a DB setup
>
> > but it ends up with empty rows on all but
>
> > the busiest days. I would like to avoid
>
> > that if possible.
>
> >
>
> >
>
> >
>
> > Thanks for any tips or suggestions,
>
> >
>
> > John
>