Help with relationsips

  • Thread starter Thread starter jro via AccessMonster.com
  • Start date Start date
J

jro via AccessMonster.com

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 said:
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_Register_One-Ticket_Sales

Tbl_Register_one_Bar_Sales
DateId
HotDogSales
HamburgSales
(and so on)
Register_One_Non_Cash_Sales



Register_Two_Proshop_Sales
DateId
CartRental
GolfSpecial
Click to expand...
(and so on)
Register_Two_Non_Cash_Sales
DateId
Register2CreditCardSales
Register2NewGiftCertificateSales
Click to expand...
(and so on)
 
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.
Click to expand...

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 said:
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
Click to expand...
[quoted text clipped - 3 lines]
duplicate entry's for that date. If anyone could help me I would appreciate
it.
Click to expand...

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]
Click to expand...
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
 
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.
Click to expand...

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.
Click to expand...

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.
Click to expand...

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
Click to expand...

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.
 
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?
 
John said:
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.
Click to expand...

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
Click to expand...
[quoted text clipped - 5 lines]
will be stored in the database that we currently use. Then a report is sent
to the financial accountant.
Click to expand...

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.
Click to expand...

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
Click to expand...

That would go beyond what's reasonable for unpaid, volunteer support,
I fear!
Click to expand...
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.
 
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.
Click to expand...

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]
 
Back
Top