Help with relationsips

I am trying to design a data base that will track our employees daily
register receits. I need to set up the relationships so that each clerk can
have a daily financial report for their shift, but there can only be one
specific date. (In other words I would like to keep track of each employees
register receits for that day but I don't want them to be able to have
duplicate entry's for that date. If anyone could help me I would appreciate
it.

List of tables

Tbl_Employees Tbl_Employee_Daily_Financial
Tbl_Register_One-Ticket_Sales
EmployeeId Primary Key DateId
DateId
EmployeeLastName EmployeeId
Game1SerialNumber
EmployeeFirstName ShiftStart
Game2SerialNumber
Address ShiftEnd
Game3SerialNumber
DateOfHire
Game4SerialNumber
City
Game1sales
State
Game2Sales
Phone
Game3Sales
Zip
Game4Sales

Game1Payouts

Game2Payouts

Game3Payouts

Game4Payouts

Tbl_Register_one_Bar_Sales Register_One_Non_Cash_Sales
Register_One_Payouts
DateId DateId
PayoutId
HotDogSales Register1CreditCardSales
DateId
HamburgSales Register1UsedGiftCertificate
SupplierName
(and so on) Register1CheckSales
TotalCost

Register_Two_Proshop_Sales Register_Two_Non_Cash_Sales
DateId DateId
CartRental Register2CreditCardSales
GolfSpecial
Register2NewGiftCertificateSales
(and so on) (and
so on)

Thank you

--
jro

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200602/1

jro wrote:
I am trying to design a data base that will track our employees daily
register receits. I need to set up the relationships so that each clerk can
have a daily financial report for their shift, but there can only be one
specific date. (In other words I would like to keep track of each employees
register receits for that day but I don't want them to be able to have
duplicate entry's for that date. If anyone could help me I would appreciate
it.

List of tables

Tbl_Employees
EmployeeId Primary Key
EmployeeLastName
EmployeeFirstName
Address
DateOfHire
City
State
Zip
Phone

Tbl_Employee_Daily_Financial
DateId
EmployeeId
ShiftStart
ShiftEnd


Tbl_Register_One-Ticket_Sales
DateId
Game1SerialNumber
Game2SerialNumber
Game3SerialNumber
Game4SerialNumber
Game1sales
Game2Sales
Game3Sales
Game4Sales
Game1Payouts
Game2Payouts
Game3Payouts
Game4Payouts

Tbl_Register_one_Bar_Sales
DateId
HotDogSales
HamburgSales
(and so on)

Register_One_Non_Cash_Sales
DateId
Register1CreditCardSales
Register1UsedGiftCertificate
Register1CheckSales


Register_One_Payouts
PayoutId
DateId
SupplierName
TotalCost

Register_Two_Proshop_Sales
DateId
CartRental
GolfSpecial
(and so on)

Register_Two_Non_Cash_Sales
DateId
Register2CreditCardSales
Register2NewGiftCertificateSales
(and so on)


Thank you

--
jro

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200602/1

On Fri, 03 Feb 2006 21:20:21 GMT, "jro via AccessMonster.com"
u17029@uwe wrote:

I am trying to design a data base that will track our employees daily
register receits. I need to set up the relationships so that each clerk can
have a daily financial report for their shift, but there can only be one
specific date. (In other words I would like to keep track of each employees
register receits for that day but I don't want them to be able to have
duplicate entry's for that date. If anyone could help me I would appreciate
it.

The first step is to get rid of repeating fields! If you have fields
named Game1... Game2... Game3... then your table structure is WRONG.
You're "committing spreadsheet upon a database".

If you have multiple employees selling tickets to each game, and each
employee may sell tickets, and other things, at multiple games, you
need at least *FOUR* tables:

tblEmployees
EmployeeID Primary Key
LastName
FirstName
other info about the person

tblGames
SerialNumber Primary Key
info about the game

tblItems
ItemType Primary Key
Item e.g. Tickets, Hotdogs, Peanuts, ...

tblSales
EmployeeID ' link to tblEmployees, who sold stuff
SerialNumber ' Link to tblGames, what game did they sell at
ItemType ' link to tblItems, what did they sell
Sales ' how many tickets/dollars did they move

Some of your other fields sound like from the fieldname that they
should be calculated on the fly in Totals queries, and not stored in
ANY table. Tbo_Employee_Daily_Financial for example sounds like a
totals query summing all the sales by an employee; there is NO benefit
to storing this information in any table, just add it up as needed.


John W. Vinson[MVP]

John Vinson wrote:
I am trying to design a data base that will track our employees daily
register receits. I need to set up the relationships so that each clerk can
[quoted text clipped - 3 lines]
duplicate entry's for that date. If anyone could help me I would appreciate
it.

The first step is to get rid of repeating fields! If you have fields
named Game1... Game2... Game3... then your table structure is WRONG.
You're "committing spreadsheet upon a database".

If you have multiple employees selling tickets to each game, and each
employee may sell tickets, and other things, at multiple games, you
need at least *FOUR* tables:

tblEmployees
EmployeeID Primary Key
LastName
FirstName
other info about the person

tblGames
SerialNumber Primary Key
info about the game

tblItems
ItemType Primary Key
Item e.g. Tickets, Hotdogs, Peanuts, ...

tblSales
EmployeeID ' link to tblEmployees, who sold stuff
SerialNumber ' Link to tblGames, what game did they sell at
ItemType ' link to tblItems, what did they sell
Sales ' how many tickets/dollars did they move

Some of your other fields sound like from the fieldname that they
should be calculated on the fly in Totals queries, and not stored in
ANY table. Tbo_Employee_Daily_Financial for example sounds like a
totals query summing all the sales by an employee; there is NO benefit
to storing this information in any table, just add it up as needed.

John W. Vinson[MVP]
John thank you for responding.
I'm not sure that I understand, do you mean that I need one table for each
ticket game or just the four tables that you listed here.
We have two different registers and the registers are set up to keep track of
each individual item sold, hot dogs, greens fees snd so on. at the end of the
shift the employee will x out of the register and the register receit will
have the totals, example hot dogs $100.00 we store these numbers in the
tables for later use in the report for each employee for that day. At the end
of the day the manager takes the z out register receit from the register that
has the totals of items for the hole day , example Hot dogs $250.00 Which
will be stored in the database that we currently use. Then a report is sent
to the financial accountant.
The database that we use now I designed, It works well but I'm sure that
there was probably a better way.
I was hoping to add this to the existing database when it was working
properly.I would gladly show anyone the database for there input if they were
interested in helping me. Again THANK YOU

--
jro

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200602/1

On Sat, 04 Feb 2006 14:55:33 GMT, "jro via AccessMonster.com"
u17029@uwe wrote:

I'm not sure that I understand, do you mean that I need one table for each
ticket game or just the four tables that you listed here.

You should have one table for each *type* of Entity - real-life thing,
person, or event. You should NOT store data in tablenames or
fieldnames. You're doing a great deal of the latter; having a table
for each game would be the former. Don't do either!

We have two different registers and the registers are set up to keep track of
each individual item sold, hot dogs, greens fees snd so on. at the end of the
shift the employee will x out of the register and the register receit will
have the totals, example hot dogs $100.00 we store these numbers in the
tables for later use in the report for each employee for that day. At the end
of the day the manager takes the z out register receit from the register that
has the totals of items for the hole day , example Hot dogs $250.00 Which
will be stored in the database that we currently use. Then a report is sent
to the financial accountant.

ok... so the totalling is being done *before* you ever get to the
database. That relieves part of my concern.

The database that we use now I designed, It works well but I'm sure that
there was probably a better way.

I do believe that there is.

I was hoping to add this to the existing database when it was working
properly.I would gladly show anyone the database for there input if they were
interested in helping me. Again THANK YOU

That would go beyond what's reasonable for unpaid, volunteer support,
I fear!

Check out the Database Design 101 link I posted. The first step is to
identify your entities - the ones I see are Registers (a two-row
table); ItemsSold (e.g. hot dogs, greens fees, etc. - one record for
each type of item sold); Employees; and DaysSales. The final table
would be your master table, linked to each of the other three. You
haven't really lost anything by entering data into your "spreadsheet"
design (other than some time) - you can create a properly normalized
structure and use Append queries to migrate the data.

John W. Vinson[MVP]

Your table fields got all mixed up when you put them in two column in the
posting. Please post in one column.

You should have just one field for type of sale defaulting to Cash but also
validate on CreditCard, GiftCertificate, and Check.

You should have one field for Item. Use an Items table listing the
products/services sold and record source for ListBox on your data entry form.

"jro via AccessMonster.com" wrote:

I am trying to design a data base that will track our employees daily
register receits. I need to set up the relationships so that each clerk can
have a daily financial report for their shift, but there can only be one
specific date. (In other words I would like to keep track of each employees
register receits for that day but I don't want them to be able to have
duplicate entry's for that date. If anyone could help me I would appreciate
it.

List of tables

Tbl_Employees Tbl_Employee_Daily_Financial
Tbl_Register_One-Ticket_Sales
EmployeeId Primary Key DateId
DateId
EmployeeLastName EmployeeId
Game1SerialNumber
EmployeeFirstName ShiftStart
Game2SerialNumber
Address ShiftEnd
Game3SerialNumber
DateOfHire
Game4SerialNumber
City
Game1sales
State
Game2Sales
Phone
Game3Sales
Zip
Game4Sales

Game1Payouts

Game2Payouts

Game3Payouts

Game4Payouts

Tbl_Register_one_Bar_Sales Register_One_Non_Cash_Sales
Register_One_Payouts
DateId DateId
PayoutId
HotDogSales Register1CreditCardSales
DateId
HamburgSales Register1UsedGiftCertificate
SupplierName
(and so on) Register1CheckSales
TotalCost

Register_Two_Proshop_Sales Register_Two_Non_Cash_Sales
DateId DateId
CartRental Register2CreditCardSales
GolfSpecial
Register2NewGiftCertificateSales
(and so on) (and
so on)

Thank you

--
jro

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200602/1

I still hold with my earlier comments.

Are these the same data output you get from the register? How do you expect
to transistion from register data to these tables?

"jro via AccessMonster.com" wrote:

jro wrote:
I am trying to design a data base that will track our employees daily
register receits. I need to set up the relationships so that each clerk can
have a daily financial report for their shift, but there can only be one
specific date. (In other words I would like to keep track of each employees
register receits for that day but I don't want them to be able to have
duplicate entry's for that date. If anyone could help me I would appreciate
it.

List of tables

Tbl_Employees
EmployeeId Primary Key
EmployeeLastName
EmployeeFirstName
Address
DateOfHire
City
State
Zip
Phone

Tbl_Employee_Daily_Financial
DateId
EmployeeId
ShiftStart
ShiftEnd


Tbl_Register_One-Ticket_Sales
DateId
Game1SerialNumber
Game2SerialNumber
Game3SerialNumber
Game4SerialNumber
Game1sales
Game2Sales
Game3Sales
Game4Sales
Game1Payouts
Game2Payouts
Game3Payouts
Game4Payouts

Tbl_Register_one_Bar_Sales
DateId
HotDogSales
HamburgSales
(and so on)

Register_One_Non_Cash_Sales
DateId
Register1CreditCardSales
Register1UsedGiftCertificate
Register1CheckSales


Register_One_Payouts
PayoutId
DateId
SupplierName
TotalCost

Register_Two_Proshop_Sales
DateId
CartRental
GolfSpecial
(and so on)

Register_Two_Non_Cash_Sales
DateId
Register2CreditCardSales
Register2NewGiftCertificateSales
(and so on)


Thank you

--
jro

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200602/1

John Vinson wrote:
I'm not sure that I understand, do you mean that I need one table for each
ticket game or just the four tables that you listed here.

You should have one table for each *type* of Entity - real-life thing,
person, or event. You should NOT store data in tablenames or
fieldnames. You're doing a great deal of the latter; having a table
for each game would be the former. Don't do either!

We have two different registers and the registers are set up to keep track of
each individual item sold, hot dogs, greens fees snd so on. at the end of the
[quoted text clipped - 5 lines]
will be stored in the database that we currently use. Then a report is sent
to the financial accountant.

ok... so the totalling is being done *before* you ever get to the
database. That relieves part of my concern.

The database that we use now I designed, It works well but I'm sure that
there was probably a better way.

I do believe that there is.

I was hoping to add this to the existing database when it was working
properly.I would gladly show anyone the database for there input if they were
interested in helping me. Again THANK YOU

That would go beyond what's reasonable for unpaid, volunteer support,
I fear!
John I do agree, but for the last five years I have been volunteering my time
to our local non-profit golf course which the database is for. I have taken
several computer classes to learn access, but by no means do I feel that I
have enough experiance to write a perfect database. It works well, but I
would like a second pair of eyes to look at it, and have tried to get local
people to help, but they are either uninterested or they don't have the time.
I don't expect to have someone redesign the database for me, but I would just
like to be guided in the right direction. By no means do I show disrespect to
you or anyone else who tries to help, it would just be so beneficial if
someone would look at the database to understand what I am trying to
accomplish. Again, I'm not getting a dime for my time.
Check out the Database Design 101 link I posted. The first step is to
identify your entities - the ones I see are Registers (a two-row
table); ItemsSold (e.g. hot dogs, greens fees, etc. - one record for
each type of item sold); Employees; and DaysSales. The final table
would be your master table, linked to each of the other three. You
haven't really lost anything by entering data into your "spreadsheet"
design (other than some time) - you can create a properly normalized
structure and use Append queries to migrate the data.

John W. Vinson[MVP]

--
jro

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200602/1

On Sat, 04 Feb 2006 21:18:27 GMT, "jro via AccessMonster.com"
u17029@uwe wrote:

John I do agree, but for the last five years I have been volunteering my time
to our local non-profit golf course which the database is for. I have taken
several computer classes to learn access, but by no means do I feel that I
have enough experiance to write a perfect database. It works well, but I
would like a second pair of eyes to look at it, and have tried to get local
people to help, but they are either uninterested or they don't have the time.
I don't expect to have someone redesign the database for me, but I would just
like to be guided in the right direction. By no means do I show disrespect to
you or anyone else who tries to help, it would just be so beneficial if
someone would look at the database to understand what I am trying to
accomplish.

I'm just afraid that the suggestions I would have are going to involve
a total rewrite, not just a bit of tweaking. As noted elsethread it's
hard to visualize your table structure (the word wrap completely ate
your display g) but it certainly looks like the source of many of
your problems is the basic table structure - and, obviously, if you
change *that*, you'll need to also change all your queries, forms, and
reports.

If you're willing to risk that, compact the database, zip it, and
email it to me at jvinson at wysard of info dot com
(remove all blanks and make the obvious replacements in the email
address).

John W. Vinson[MVP]