General :: Table Structure For Multi Location Warehouse

Apr 30, 2015

I am working with Access 2007. I have a pretty good working knowledge of access. Here is my problem. I have a warehouse which supports four departments. The warehouse floor area is segragated into four areas. Area 1 department 1, area 2 department 2, and so on. Each department uses the same commodity, so I will have four of each item, belonging to each department. The storage locations will be unique and not repeated anywhere in any department. I receive and issue items for all departments.

The problem is how do I structure the tables and queries to receive an item for department 1 adding it to department 1 only and not have the item added to another department with the same item. Also to issue the item from a department 1 without effecting the on hand balance of any other department. I am too the point of creating 4 separate databases one for each department.

View Replies


ADVERTISEMENT

General :: Parking Slot Warehouse Database - Altering Available Options In Combo Box

Mar 1, 2014

I am new to MS Access. So I am making a database for an automobile warehouse. When a new car arrives at the warehouse, it is allocated a specific parking slot (using a combo box available on the 'New Arriving Car' form).

However, once a specific slot has been allotted, the end-user should not be able to allot the same parking slot to other arriving cars, until the original car has left the warehouse so that the parking slot becomes available once again. Currently, the parking slot combo box shows all parking slots whereas it is supposed to display only unoccupied parking slots. How can I enforce this? (Note that there is a separate form called 'Exiting Car', which is filled when a car is leaving the warehouse).

Fyi, each car is referred to by a unique 17 digit code - so this is the primary key. Also, there are 120 parking slots available in all, with the slot identifiers ranging from A1, A2...A12 to J1, J2..J12 (10 x 12 = 120).

View 5 Replies View Related

Very General Table Structure Question.

Nov 5, 2005

i understand the necessity of foreign keys to link tables and create dynamic data structures..

believe it or not i have a degree in this subject, but am finding one little issue quite confusing...i shall illustrate this with an example...

THE RIGHT WAY TO DO SOMETHING - 2 tables.. table1(DRIVERS) table2 (JOBS)

DRIVERS
driverId
name
phone

JOBS
jobId
date
driverId

each job is carried out by one driver, and drivers can do many jobs throughout their lives...obviously. i learnt that in good practice, the above two fields should be used to link these 2 kinds of tables together..

to my knowledge.. to link the two tables like this in access.. i would view the 'Relationships' and drag one field to the next.. so the little line joins them and everyone is happy..

HOWEVER, i have created this database and have used this method...imagine my user adding a job to the database.. all i want them to do is choose the drivers name from a dropdown menu for each job. to acheive this i have used the 'lookup wizard'... this successfully associates the chosen driver to that job and when you query the database, the results are valid.
this link i have created has nothing to do with the Ids as illustrated in the above example. what i need to know is if i link the two tables in the 'proper' way using the Ids.. how can i allow my user to choose the drivers name when entering a job.. rather then choosing the drivers Id number on the form because that would be confusing...
i hope this post wasnt confusing..
am i mad...someone please explain to me???

View 11 Replies View Related

General :: How To Make Structure Of Table

May 20, 2014

I have a question about the fast food project for the restaurant, how to make the structure of the table ! also they want to working by the touch screen , if the database i created by Access and SQL it is worked on it ?

View 2 Replies View Related

General :: Split Database - Unable To Find Table Location

Mar 24, 2014

I'm having a problem with a database that i had split. While creating teh database, i had it stored on my personal drive at work, but wanted to move it to the shared drive so the data could be stored there. this is to deal with cross site network connectivity issues that we encountered with another database here at work.

When splitting the database, i split it on my personal drive and then dragged the back end to the common drive. I realized my error, but when attempting to correct it, i somehow have two of the same file, and no back end?

I receive the error on the attached document when attempting to access any of the tables or information in either of the files, the "back end" that i placed on teh drive, and the "front end" that i have on my personal drive.

I also tried to relink the tables through the linked table manager, but receive the same error when attempting that.

Is there a way to recover from this? I had backed up my file by making a copy prior to splitting, but somewhere in this process, this became linked to these and is having the same issue. Is there a way to recover the old "unsplit" version? I cannot copy over all of teh tables, etc. because i get the same error as earlier.

I can attach a copy of my database for reference...

View 5 Replies View Related

General :: Database Design - Stock Tracking / Set Up Table For Each Location

Sep 10, 2012

I am planning my new DB and am contemplating the best design. It will be used for warehouse stock rotation and control of pallets. I want to track each pallet (product/time in/time out etc) to each pallet space within the warehouse. There are a total of 400 pallet spaces or 'locations' as i will refer to them as.

Now, would it be possible for me to have a table set up for each location? Will access object to having 400 tables in my data base?? Is there a limit?

View 1 Replies View Related

General :: 2 Front Ends And One Table Structure

Sep 10, 2013

I have recently asked about being able to use my table system online. i have just thought. what about creating a front end that works online. still have the front end on the desktop and the table system on the desktop but have both of them connecting to the one table system.

View 8 Replies View Related

General :: Copy Structure From Existing Linked Table In Backend

Jun 9, 2012

I have a need to copy a table structure from an existing linked table in the backend

i have done this by

a) copying the table structure into a new table in the fromt ebd
b) exporting the new table into the backend
c) dropping the table in the front end

just wondered if there was an easy way to do this directly in the backend?

View 1 Replies View Related

General :: Access 2003 Create Multi Column Filtered Table

Jan 4, 2013

create a datasheet from 2 odbc linked tables which I will be exporting to Excel. The resulting datasheet will have four columns, 'Purchase Order Number' 'Stock Code' 'Delivery Date' 'Unit Price'. The information I require in the datasheet is a unique stock code (one occurrence of each stock code) with the most recent unit price based on the last delivery date for each product. The linked tables contain many Purchase Orders for the same stock codes over a time period of a number of years where the unit price has varied.

View 3 Replies View Related

Multi Record/Query/Multi Table/Going Crazy Issues

Sep 7, 2007

I have spent the last couple of days trying to figure out how to make this work.

I have three tables.

tblIntakeMain
[IntakeMainID]

tblIncidentDetails
[IncidentdeatailsID]

tblPersonnel
[PersonnelID]

On the main form I use subforms to link tblIncidentDetails and tblPersonnel to tblIntakeMain. Both subforms can, and do, have many entries. This all works fine. What is not working is the search form I am using.

I am using Gromits most excellent Search Form. The problem is when I create a query, qSearch, to bring together the three tables I get a multiple records which makes the searches very confusing and near useless. Is there anyway around this? Is there something I am missing? Is there another search method I could use that would work in a similar way as Gromits? Please help before the Prozac runs out and I lose my mind--what little it left.

View 5 Replies View Related

Multi-table, Multi-criteria: Avoid Repeating Records

Apr 10, 2008

Hi everyone. Apologies if this has come up before, but the search terms I've tried here and on google keep turning up the wrong information.

At work I manage a large database with many tables. It stores data for participants in a research study. Each table stores the data for a different test, so one participant may have multiple records. Primary keys for these tables are defined by a combination of the participant and date of test fields. (Everything is dependent upon a table that stores the static info for participants, so the database is normalized.)

I want to be able to make a table that lists target participants and dates, and then create a query that looks at this table and pulls all the available data from various tables for those individuals that was recorded within one year of the target dates.

I've successfully made queries that meet these criteria while pulling data from only one table. The problem I'm having is that when I try to pull from multiple tables, each with it's own date field that needs to be used as a criterion, I end up excluding almost all the data, because most of the target participants do not have all the requested data within the target dates.

I've tried being inclusive with my criteria (using ORs), but then I end up with tons of data that I don't want and I need to filter through it, which defeats the purpose of the query.

Any advice on handling this issue, or do I basically just need to create a separate query for each table?

I'm sorry if this is too vague, but it's illegal for me to upload any of my own dataset. I could probably come up with an example if it's helpful, though.

Thanks!

View 7 Replies View Related

Problems Getting To Data Warehouse With Access

May 9, 2006

I am a relatively inexperienced user of Microsoft Access, but my current problem has even the experts at my company stumped. I am hoping that someone here can help.

My company has an Oracle-based data warehouse. I can access its data just fine through TOAD and other applications.

However, when trying to link to the tables through Microsoft Access, it takes an extraordinarily long amount of time. My computer makes a noise as if it is searching for a file and not finding it, and Access goes into "Not responding" mode for five minutes or more. But--given enough time--the data WILL come up.

My Access can retrieve data from its own database, and other networked Access databases, just fine.

Does anyone know what might be causing this problem specifically between Access and Oracle?

Thanks in advance.

View 7 Replies View Related

Order Processing & Warehouse Management

Sep 3, 2007

Hi all. I am considering developing a purchase order and warehouse management database, to be manage a cold store in my workplace.

What i have in mind is a system where the details of each pallet coming in is recorded (weight, quantity, location of pallet in warehouse etc.) is recorded, marked off against the corresponding purchase order number and then a label is printed with a barcode to quickly identify that pallet.

Handheld barcode scanners would be used to record when a pallet leaves the warhouse, and where its going to(despatch, production etc.). Ideally the scanners could be used to give forklift drivers order picking info aswell.

I'm fairly confident i can design the database, however, i have never done anything with barcodes, scanners or handheld devices. Is this even feasible with access? How do i go about doing it, any advice, example databases etc. greatly appreciated.

View 1 Replies View Related

General :: Access Runtime BE Location

Apr 11, 2013

In Microsoft article 291264, it says "In a run-time application, the front-end and back-end databases must be stored in the same folder."Is that really true or am I misconstruing the article?I want to use runtime clients with a split back end on a share drive.

View 2 Replies View Related

General :: Modifying Record And Saving In New Location?

Dec 4, 2013

I'm working on a database that will be used by two different groups. Group A provides a list of items to be investigated by Group B.

I'm wondering if it is possible to:

i) have Group A create an excel file of items to be investigated (let's call the file tblNEW)

ii) open a record in the file through a form (let's call the form frmINPUT) to allow Group B to input investigation findings

iii) save the modified record into a separate table of all the work that has been done (let's call this table tblWORK)

iv) delete the investigated record in tblNEW once it gets saved into tblWORK

View 1 Replies View Related

General :: Choose Backend Location From A Form

Dec 18, 2014

I'm making updates for the front end then i send it to 10 pc's in the office then i should change the backend location via linked table manager for every pc

So is there any way to let the user choose the backend location when he open the database nad then it will automatically update the tables link

View 4 Replies View Related

General :: Changing Database Location For Sharing

Dec 29, 2014

In the past I have only done data input into Access databases that have already been created. They have been simple text fields only. I was hired on contract to finish data input for a database that serves to catalogue material resources. The database itself was supposed to have been completed by a former contractor but I'm discovering bits and pieces that weren't finished. I don't have any background in IT or coding.

So, this database eventually has to be shared in a "locked" format so that other entities can view our resources (like a catalogue) without changing them. My next step is to add images to each of the items being catalogued (there are about 100 images). The former contractor began a table labeled "Pictures" in which there are 2 columns: PictureID and PicturePath, the latter being a path starting with C:. The issue is, his picture paths include the hard drive name so that it looks like this:C:HardDrivePicturesPictureID.jpg

I need the database and its images to be share-able on other computers. And I need to use relative paths to connect the images to the database so I'm not tremendously increasing the size of the database.does the location/path of the database need to be changed so it can be shared, and if so how do I do that?

Second question: I have all the images I will be using in a folder on my desktop; how do I save them in a "fixed subdirectory" of the database so that they get moved with it whenever it is sent out to another user?

If I change the database name as I'm working on it (I've been saving a new database each day, so that if I screw up something I can go back to the most recent working one) will that mess up my picture paths?

View 5 Replies View Related

General :: Attachment Field - Specify Location To Search

Jan 14, 2013

Using an attachment field - can we specify the location to search?

We have an Access 2007 DB and need to attach files from only 1 location - we hope to attach a copy of these files into the database efficiently.

I have a few sample DB's that use VB but I am hoping there is a quick and easy solution for this.

View 2 Replies View Related

General :: Small Application Not Working Since It Was Moved From Its Old Location?

Nov 27, 2012

I do IT support for different companies.

I have had a client moving to a new server a couple of months ago.

They have a small access application that reads csv files and exports them into Sage format that can then be imported into their accounts program - it also reports the total invoiced (whatever is inside the data CSV file) and has a printer friendly report with all the invoices inside said data CSV file.

The issue I have is that since the little app was moved to the new server - it stopped working.

I think somewhere in the code / options there is place to state the working path for the app. I think the app is trying to read the input data file from the old shared folder and thus not working anymore.

I can post the code and a sample CSV file. Specially because I did find a place with the old path and changed it to the new file share and it still didn't work.

View 4 Replies View Related

General :: Copy Network Folder To Another Location And Rename It

Jul 17, 2013

I'm looking for copy folder functionality and I can't find anything.

In access 2007 when I push a button on a form, I want to copy a network folder to another location and rename it.

View 1 Replies View Related

General :: Update Front End With New Back End File Location

Dec 12, 2013

Access 2010

Objective?: I'm attempting to change the file location of the back end portion of my split database.

What I've done so far: I've used the linked table manager to update the back end location of all tables in the front end portion of the database.

Symptoms: When making a change to the OLD back end: If I change the name or move the old back end and then try to re-open the front end, Access opens as if I'm opening the application itself without any database associated with it until I restore the OLD back end to its appropriate state.

When making a change to the NEW back end: If I change the name or move the new Back end and then try to re-open the front end, I get a: "Could not find file" error message until I restore the new back end to it's appropriate state.

I've just discovered a query that I think put me one step closer to resolving this issue. [URL] .....

When I run this query:

Code:
SELECT MSysObjects.Database
FROM MSysObjects
GROUP BY MSysObjects.Database, MSysObjects.Type
HAVING (((MSysObjects.Type) In (4,6)));

It returns 2 results:
1. The path of my old back end
2. The path of my new back end.

Unfortunately I'm not able to update the values from the Datasheet, but this does confirm to me that the old back end is still some how being used by the front end even after my tables have all been updated.

View 5 Replies View Related

General :: Control System To Put Stock That Come Into Specific Location

Jun 18, 2015

I have created a stock control system where i put stock that come in into a specific location i can put the same item over several locations depending of the size of the location or i can can put more than one product in a location .

The problem im having is when i do a order that say remove half of all the stock in it is split over more than one location i cant seem to get it to remove the stock from the location to show space available.

View 5 Replies View Related

General :: Copy A Folder To Location Selected By The User?

Sep 4, 2012

I have a folder with Access files, subfolders, and some .vbs files in it. It requires one person to navigate through a setup process. Once the setup is completed, they need to copy the folder and its contents to a location on a shared drive, which is unknown to me and will be chosen by the user at the site. There could be many sites.

It basically works like this:

Files are downloaded to C:Program Files.

A vbscript is run and extracts a folder directly to the C:Drive and opens it. I'll call it C:Test.

The user manually moves the BE file to a shared drive.

The user opens the FE file and is walked through using the Linked Table Manager.

The user then follows a series of forms to input data.

After this, the FE closes and another DB opens.

The second DB walks them through the Linked Table Manager and closes.

Option #1 At this point I would like to have a script and have it copy that folder, C:Test, and its contents, and have a dialog box open telling the user to paste it in a location where all users have access to.

Option #2 A script that will backup the folder and all its contents to a shared location selected by the user.

Either option will l allow others to navigate to that location, copy the folder, and paste the folder to their C: Drive.

View 2 Replies View Related

General :: Database Opens As Read Only When Opened From Shared Location

Jul 15, 2012

We have a database on a PC located on our network. The database is located in this computer's shared folder. Another database grabs data from this database occasionally. We're running into a problem where we cannot open the DB located in the shared folder (permissions granted) without it being a read only instance even with no users accessing the DB.

View 6 Replies View Related

General :: Changing Over To New Database Structure

Apr 18, 2013

We are currently using Access 2007 for all our database needs but so many different people have had access to changes things that now they are so convoluted that my boss is wanting me to build a brand new database... Is there a way to take all the tables in one database and migrate them over to the new database that I am building?

View 3 Replies View Related

General :: Import Csv Files With Same Named Structure?

Apr 9, 2014

I need to daily import files that start with same name, but the remaining part will be Bill of lading ID (e.g. TEST_BOL 1234.csv). The next day will be TEST_BOL 1235.csv) and so on. I need for Access to import the file based on the "TEST_BOL" into my database and then delete the file from the folder. I don't know how to initialize Access to look for the prefix and import in.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved