Access database question

Question

Hi, I am creating an Access database for our office.  We have one main sheet that contains "check" information such as date entered, check number, vendor ID, vendor name, Site, etc. for 5 different sites.  Each site has to add more info for their own checks. 

So I am creating access database that still have the one spreadsheet (all the checks info goes on the one sheet) and by creating query for each 5 different sites, we can disseminate the "split" database with each site on it to give to each site.  My question is that is it ok for them to adjust/add/delete in query and that will automatically link to my one main sheet that I keep at headquarter?  Also, each site have multiple people who has to go in and adjust the same query database.  Is that going to be ok with simultaneous entry? 

I did not want to create five different tables and we have to enter each sheet because we get large amount of checks but don't know which site until we research each of them. So it's easy to paste into the main sheet and enter all the info to include site info and query automatically dissaminate the site info on each sheet.  Thank you for your help.

Answer 1

Again, Access is not a spreadsheet. Access is a relational database and you should use it relationally as it was intended. Using Access with a single table is a waste of time. 

You refer to a query, but with a single table I don't know what you need a query for. You need to understand how a relational database works and how to normalize a database. And the first thing you need to do is stop thinking and referring to it as a "sheet". 

At the very least, based on what you have said, you need at least three tables:

tblChecks

CheckID (PK Autonumber)

CheckNumber

VendorID (FK)

CheckDate

CheckAmount

SiteID (FK)

tblVendor

VendorID (PK )

VendorName

other Vendor info

tblSite

SiteID (PK)

Sitename

You may need more. 

As for only allowing the sites to edit their own, you should have a login that identifies which site and then filter the form soo they only see their site.

Answer 2

I understand - it looks like spreadsheet.  I collect all the checks and annotate information (with location) and have to give each location's checks so they can make inputs, etc. but the main form should still be connected to the query and update itself automatically.  So we can't use query to update the info?  I tried and it works (link to the main sheet) but we are not supposed to?  Thank you for your help..

Answer 3

I don't have relationship yet.  I have one table as main sheet with all the information, and have queries that for each site using "criteria" function.  I just want to keep the main sheet for us and give each site only theirs and have them make the inputs for their checks information.  Thank you for your help...

Answer 4

Access is NOT a spreadsheet. Access tables are not spreadsheets. That is the first thing you need to understand.

Second question are each of these sites on the same LAN or do you have to send then copies of the application to get their data?

Third, you said you don't know which site will do the research, so how is this determined?

Fourth, you do data entry in forms, not queries.

So to help you we need more info.

Answer 5

Please post a screenshot of your Relationships window. I'm afraid you missed a few important relational database design points.