Importing POS Data

[Ernst Guckel]

Hello,

I have two questions that I am a little stumped on:

1. I import menu item sales from my POS system into the office PC. The
data comes in like so: StoreNumber,SalesDate,MenuItem,PrintName,SoldCount.
The problem is that for a while now I have been importing w/out PrintName.
MenuItem is just a number and means nothing to the end user. Mistake on my
part when designing but anyway... I want to go back through and ADD
PrintName data to the records that are missing... I thought of having a
lookup table with the names but new items are added and deleted often and
there is the possibility of the same MenuItem # being used for different
Promo products... By keeping PrintName and MenuItem together I know that on
this date I sold 32 of #119 called 'Medium Coke'

2. We also import Employee hours worked each day. The problem is that the
database uses a uniqueID for each employee (EmpID) but the POS uses
EmployeeNumber. I want to import the data but as it does I need the import
to lookup the employee number to find the assosiated EmpID then insert the
data. The other thing to know is that an OLD employee number has the ability
to be resused at some point. We rotate arround a set of numbers. IE Store
#1 uses 322## for their numbers so after 99 employees they start over. Each
store has this setup. So I cannot use EmployeeNumber as the uniqueID in
access.. it would conflict with an old employee sometime in the future...

Any thoughts on either of these would be great..

Ernst.

 

[Klatuu]

As to # 1.
It is a horrid design, but you are stuck with it. Although it violates
database normalization rules, your only solution is to include the print
name. Since the @#$RFSF#we! POS system allows a menu item number to be
reused, even if you changed your lookup table to the current item, that would
show incorrectly for past items.

# 2.

Since an employee number from the POS system may be reused, you need a way
to know what employees are current. In this case, add a field to the
employee table to identify Active employees. So your table would have the
EmpID, the EmployeeNumber the POS system uses so you can associate them, and
an Active/InActive field (A Yes/No - Boolean field would do) Then you would
have to use queries to show only the active employees to match to the
imported time records.

 

[Ernst Guckel]

As to # 1.

It is a horrid design, but you are stuck with it. Although it violates
database normalization rules, your only solution is to include the print
name. Since the @#$RFSF#we! POS system allows a menu item number to be
reused, even if you changed your lookup table to the current item, that would
show incorrectly for past items.

I know. This is the limitation of the POS system. Is there a way to have
access go back and fill in the PrintName for the previous records?

# 2.

Since an employee number from the POS system may be reused, you need a way
to know what employees are current. In this case, add a field to the
employee table to identify Active employees. So your table would have the
EmpID, the EmployeeNumber the POS system uses so you can associate them, and
an Active/InActive field (A Yes/No - Boolean field would do) Then you would
have to use queries to show only the active employees to match to the
imported time records.

I have an EmpStatus field to determine if an employe is Active/InActive. Is
there a way to import the data into the current table? Here is the layout:

tblEmpPayroll - StoreNumber, EmpPayDate, EmpNumber, LastActivity,
ConsecutiveDays, EmpHours, EmpAmount, EmpOTHours, EmpOTAmount

but tblEmployees' uniquieID is EmpID. I can Insert EmpID into tblEmpPayroll
but there is no data comming from the POS datafile. How do I link the data
to the employee without using EmployeeNumber as the 'LinkedField'? It can be
used to lookup 'Active' employees though...

Thanks.

 

[Klatuu]

Now I'm confused. Your first post said EmpID is the primary key for the
Access table and the table you are imporing as an EmployeeNumber field that
can be reused, but I see your table layout has EmployeeNumber but no EmpID.

Can you be a bit more specific about each table and what you are doing with
it?

 

[Ernst Guckel]

ok tblEmpPayroll is the table that I am storing the imported payroll data
from the POS system. tblEmployees is the employee data table with all of the
other usless data about employees. There is no EmpID field in tblEmpPayrol
because the POS system does not have that field. EmpID is the primary key in
tblEmployees. There is no Primary key in tblEmpPayroll. I am not using the
data at this point just importing it becuase I cannot link tblEmpPayroll to
tblEmployees.

 

[Klatuu]

I described how to do that in my previous post.
Add the EmployeeNumber the POS system uses to tblEmployees. It then becomes
a foreign key to tblEmpPayroll.

 

[Ernst Guckel]

Its already there. I cannot use that as the foreign key because there will
be some poin tin the future when old employee numbers will be reused.

 

[Klatuu]

If you filter on Active employees you can use it.
Yes, more than one employee in your table will have that number, but only
one of those employees will be Active.

 

[Ernst Guckel]

Ok I get it. I'll try it out. Any ideas on Question #1?

Ernst

 

[Klatuu]

Since menu item numbers can be used for multiple descriptions, there is no
way you can get them 100%. At best, you can use an update query to plug in
the "normal" or "usual" item, but there will be some incorrect. It would
probably take a manual review and fix to get them all correct.