"need good structure..." followup
in [Tables]
|
Prev: Microsoft Responds to the Evolution of Online Communities
Next: Can't insert columns into my access db
From: tighe on 5 May 2010 09:28 hope i explain this well. i have a similar setup, as below, but for the "MachineCounter" table i have a finacial period. this period is essentially a month, but items can come be in/out in the period before or after but it still needs to be tracked in that period, so today i, may 5, i might recieve something for the period of April. how do i do this? also i am truly tracking three types of in/out, would it be better to have each item to track be a seperate table and when it is tracked the system auto adds the Period for each record in the seperate table(s)? the one main issue i am having with the seperate table is to be able to edit the tracking table but still have the avialability to track any period at once. "XPS350" wrote: > On 25 mrt, 13:24, akioki <aki...(a)discussions.microsoft.com> wrote: > > Dear, > > Can anyone help me to make good database structure? > > > > What i have are some locations where I have some machines. Every machines > > have 2 counters (IN and OUT). I need to be able to enter state of that > > counters and after that to compare that values with previous entry IN and OUT > > counters. > > > > How I see it seams that somewere I need to keep information wich machine is > > on wich location. Beside that I need to track information about date and time > > of entries. > > > > Situation: > > > > Location 1 -> machine1, machine2, machine3 > > Location 2 -> machine4 > > Location 3 -> machine5, machine6, machine7, machine8 > > .... > > > > I need 3 kind of reports: > > ○ Deference between new IN / OUT and some old IN/OUT counters for every > > machine > > ○ Total IN/OUT for some location > > ○ Total IN/OUT for all location > > > > How I need to organize this datas? > > > > Thanks for helping me, > > akioki > > It seems to me you need thre tables. > > Location > - LocationID > - LocationName > .. > .. > .. > > Machine > - MachineID > - LocationID > .. > .. > .. > > MachineCounter > - MachineID > - Date (date/time?) > - IN > - OUT > > Groeten, > > Peter > http://access.xps350.com
From: KARL DEWEY on 5 May 2010 11:24 I think your MachineCounter table - MachineCounter -- - MachineID - Date (date/time?) - IN - OUT neds to be like this - MachineCounter -- - MachineID - IN (date/time) - OUT (date/time) But I need to know how you define the Financial Period. -- Build a little, test a little. "tighe" wrote: > hope i explain this well. i have a similar setup, as below, but for the > "MachineCounter" table i have a finacial period. this period is essentially > a month, but items can come be in/out in the period before or after but it > still needs to be tracked in that period, so today i, may 5, i might recieve > something for the period of April. how do i do this? also i am truly > tracking three types of in/out, would it be better to have each item to track > be a seperate table and when it is tracked the system auto adds the Period > for each record in the seperate table(s)? > > the one main issue i am having with the seperate table is to be able to edit > the tracking table but still have the avialability to track any period at > once. > > "XPS350" wrote: > > > On 25 mrt, 13:24, akioki <aki...(a)discussions.microsoft.com> wrote: > > > Dear, > > > Can anyone help me to make good database structure? > > > > > > What i have are some locations where I have some machines. Every machines > > > have 2 counters (IN and OUT). I need to be able to enter state of that > > > counters and after that to compare that values with previous entry IN and OUT > > > counters. > > > > > > How I see it seams that somewere I need to keep information wich machine is > > > on wich location. Beside that I need to track information about date and time > > > of entries. > > > > > > Situation: > > > > > > Location 1 -> machine1, machine2, machine3 > > > Location 2 -> machine4 > > > Location 3 -> machine5, machine6, machine7, machine8 > > > .... > > > > > > I need 3 kind of reports: > > > ○ Deference between new IN / OUT and some old IN/OUT counters for every > > > machine > > > ○ Total IN/OUT for some location > > > ○ Total IN/OUT for all location > > > > > > How I need to organize this datas? > > > > > > Thanks for helping me, > > > akioki > > > > It seems to me you need thre tables. > > > > Location > > - LocationID > > - LocationName > > .. > > .. > > .. > > > > Machine > > - MachineID > > - LocationID > > .. > > .. > > .. > > > > MachineCounter > > - MachineID > > - Date (date/time?) > > - IN > > - OUT > > > > Groeten, > > > > Peter > > http://access.xps350.com
From: tighe on 10 May 2010 10:11 Karl, Financial Period, is technically one calendar month, but the tracking may occur before, during, or after that period. we also may be working on multiple periods for a certain client as once. each item will be tracked in all periods. my biggest block is making sure that the period is recorded for when it has been tracked. "KARL DEWEY" wrote: > I think your MachineCounter table - > MachineCounter -- > - MachineID > - Date (date/time?) > - IN > - OUT > > neds to be like this - > MachineCounter -- > - MachineID > - IN (date/time) > - OUT (date/time) > > But I need to know how you define the Financial Period. > > -- > Build a little, test a little. > > > "tighe" wrote: > > > hope i explain this well. i have a similar setup, as below, but for the > > "MachineCounter" table i have a finacial period. this period is essentially > > a month, but items can come be in/out in the period before or after but it > > still needs to be tracked in that period, so today i, may 5, i might recieve > > something for the period of April. how do i do this? also i am truly > > tracking three types of in/out, would it be better to have each item to track > > be a seperate table and when it is tracked the system auto adds the Period > > for each record in the seperate table(s)? > > > > the one main issue i am having with the seperate table is to be able to edit > > the tracking table but still have the avialability to track any period at > > once. > > > > "XPS350" wrote: > > > > > On 25 mrt, 13:24, akioki <aki...(a)discussions.microsoft.com> wrote: > > > > Dear, > > > > Can anyone help me to make good database structure? > > > > > > > > What i have are some locations where I have some machines. Every machines > > > > have 2 counters (IN and OUT). I need to be able to enter state of that > > > > counters and after that to compare that values with previous entry IN and OUT > > > > counters. > > > > > > > > How I see it seams that somewere I need to keep information wich machine is > > > > on wich location. Beside that I need to track information about date and time > > > > of entries. > > > > > > > > Situation: > > > > > > > > Location 1 -> machine1, machine2, machine3 > > > > Location 2 -> machine4 > > > > Location 3 -> machine5, machine6, machine7, machine8 > > > > .... > > > > > > > > I need 3 kind of reports: > > > > ○ Deference between new IN / OUT and some old IN/OUT counters for every > > > > machine > > > > ○ Total IN/OUT for some location > > > > ○ Total IN/OUT for all location > > > > > > > > How I need to organize this datas? > > > > > > > > Thanks for helping me, > > > > akioki > > > > > > It seems to me you need thre tables. > > > > > > Location > > > - LocationID > > > - LocationName > > > .. > > > .. > > > .. > > > > > > Machine > > > - MachineID > > > - LocationID > > > .. > > > .. > > > .. > > > > > > MachineCounter > > > - MachineID > > > - Date (date/time?) > > > - IN > > > - OUT > > > > > > Groeten, > > > > > > Peter > > > http://access.xps350.com
From: Steve on 10 May 2010 12:18
Look at the Northwind sample database. It seems to me you need something like the Order form with an OrderDetails subform. You record the period and other general info in the main form and each item in the subform. For entering the period, you need a Year table and a month (1 to 12) table. You then can use comboboxes in the main form to enter the Year and month. Steve santus(a)penn.com "tighe" <tighe(a)discussions.microsoft.com> wrote in message news:8DCA71F2-D125-4051-AE0F-F1FEE9AE4678(a)microsoft.com... > hope i explain this well. i have a similar setup, as below, but for the > "MachineCounter" table i have a finacial period. this period is > essentially > a month, but items can come be in/out in the period before or after but it > still needs to be tracked in that period, so today i, may 5, i might > recieve > something for the period of April. how do i do this? also i am truly > tracking three types of in/out, would it be better to have each item to > track > be a seperate table and when it is tracked the system auto adds the Period > for each record in the seperate table(s)? > > the one main issue i am having with the seperate table is to be able to > edit > the tracking table but still have the avialability to track any period at > once. > > "XPS350" wrote: > >> On 25 mrt, 13:24, akioki <aki...(a)discussions.microsoft.com> wrote: >> > Dear, >> > Can anyone help me to make good database structure? >> > >> > What i have are some locations where I have some machines. Every >> > machines >> > have 2 counters (IN and OUT). I need to be able to enter state of that >> > counters and after that to compare that values with previous entry IN >> > and OUT >> > counters. >> > >> > How I see it seams that somewere I need to keep information wich >> > machine is >> > on wich location. Beside that I need to track information about date >> > and time >> > of entries. >> > >> > Situation: >> > >> > Location 1 -> machine1, machine2, machine3 >> > Location 2 -> machine4 >> > Location 3 -> machine5, machine6, machine7, machine8 >> > .... >> > >> > I need 3 kind of reports: >> > ? Deference between new IN / OUT and some old IN/OUT counters >> > for every >> > machine >> > ? Total IN/OUT for some location >> > ? Total IN/OUT for all location >> > >> > How I need to organize this datas? >> > >> > Thanks for helping me, >> > akioki >> >> It seems to me you need thre tables. >> >> Location >> - LocationID >> - LocationName >> .. >> .. >> .. >> >> Machine >> - MachineID >> - LocationID >> .. >> .. >> .. >> >> MachineCounter >> - MachineID >> - Date (date/time?) >> - IN >> - OUT >> >> Groeten, >> >> Peter >> http://access.xps350.com |