Access :: How To Query A Simple Table.... Or So I Thought!

I Thought It Was Simple(subformStuff) But Maybe Im Just Stupid.

Feb 15, 2005

Ive got a form, that has inputs to a table, and it also has 2 subforms that link to another table, the subforms are different forms but they link to the same table, this is bacause I need to be able to enter two different items off the table that may be different items, the reason Ive used subforms is because there is the need to have the part number for the item, as well as the weight, this will be a growing list as the Dbase expands.
now I have the two subforms with a combobox for part number, and the weight field looks up because of the combo,

back on the main form I need a entry that adds up the 2 weight fields, and enters them on the main table that the main form writes data to, can someone help?

Ive been heatbutting my monitor for a week or two now, and my limited know how on Access is beginning to show :-)

Thanks.

View 3 Replies View Related

Whats Your Thought On This?

Aug 30, 2004

If I am going to get a Invoice/Statement programme made for me should it be in MS Access or Visual Basic, Thanks for your thoughts, Bob Vance

View 2 Replies View Related

Train Of Thought Required. How Do I...?

May 11, 2005

Hi all,

Hope this is the correct forum, and I am not breaking any rules of etiquette. :rolleyes:

I am after some guidance on how to start to tackle a problem, rather than any advice on the details of how to do it. That I want to work out for myself in order to try and improve my mastery of Access.

I have a team responsible for completing a number of scheduled reports.
These reports are scheduled for different frequecies ie :-
some are scheduled Weekly on a particular day,
some are scheduled Monthly on a particular date,
some are scheduled Monthly say on the 1st Monday of the month.

My goal, amongst others, is to be able to, from the switchboard,:-

a. Print a list of reports scheduled to run in the current work week.
b. Print a list of reports scheduled to run between any 2 inputted dates.

This is just part of what I want to acheive from this database, but as we all know Rome wasn't built in a day!! ;)

Grateful for any help / advice / directions,

Cheers,

fos605

View 1 Replies View Related

Cartesian Product I Thought I Have Prevented

Mar 12, 2007

Hi All,

This post is a bit long, but it is actually really simple. I have two tables(DEDPARM1 and DEDETAIL1). I ran a “CREATE TABLE” query using these two tables, to create a NEW table called “Biweekly_Temp_Table.”

DEDPARM1 sample data
SSN |Name |Type |Amount
555-99-8888 |Joe |FAE |2000
555-99-8888 |Joe |FAR |20

DEDETAIL1 sample data
SSN | Name | Type | Amount
555-99-8888 | Joe | FAE | 1000
555-99-8888 | Joe | FAR | 20

BIWEEKLY_TEMP_TABLE results
SSN | Name | Type| faeAmount | farAmount
555-99-8888 | Joe | FAE | 1000 | Null
555-99-8888 | Joe | FAR | Null | 20
This result makes sense, b/c it is what I wanted.

Now here is the problem. I ran another query(qrySingleRecord) against the BIWEEKLY_TEMP_TABLE to combine the TWO records into ONE. It works, BUT the faeAmount will equal 2000 and the farAmount equals 40. Why is Access doing this? I know something called Cartesian Product will occur if I query against both DEDPARM1 and DEDETAIL1. However, in this case I am NOT querying against these two tables; the whole purpose of creating a new table(BIWEEKLY_TEMP_TABLE) is to try to prevent Cartesian Product. Please help.

Thank you!

Joe

View 6 Replies View Related

Simple Table Question

Sep 19, 2005

Ive done this before in College but it was along time ago and i cant remember what i did

Bus say I have a table with Businesses and i need each business to be able to have multiple records associated with it.

So i have two tables one named 'businesses' and one named 'orders' how do i make sure that when a business makes an order the data in 'orders' is associated witht the releveant business in the 'businesses' table

View 3 Replies View Related

Can Anyone Help With This Simple DB Table Structure?

Jan 26, 2006

I really suck at table design and coding, and access. I'm a techie that usually stays away from all this development stuff; it makes my head hurt. I'm a lot happier knee deep in an Exchange migration than developing even the most basic of databases as I suck at it (think I mentioned that already) However, i've been roped into doing a tiny little project for a friend. Below is the process I went through for the data / table design. When I started to create the relationships it got messy with the many to many relationships. Am I over complicating it or am I on the wrong track? Any assistance from you super-clever developer dudes would be greatly appreciated and would affirm your superiority over us humble techies yet again. :)

I started with the raw data:

Supplier (e.g. Builder's Supplies Co.)
Item (e.g. Plywood, Sharp Concreting Sand)
SupplierItemCode (e.g. WWE3428X)
Unit (25KG, 9x2440x1220mm)
Cost

Here are the main relationships:

A Supplier can have many Items, SupplierItemCodes and Costs
An Item can have many Suppliers
An Item can have many Units (e.g. Sharp Concreting Sand comes in many different weights (Units))
A Unit can have many Items (e.g. the unit of 25Kg can have several different types of sand or other items associated with it)

So what I did was to create four tables (* - primary key, ( ) foreign keys):

I created the ID fields in each table even though Supplier, Item, Unit and SupplierItemCode are all unique values that could be used as primary keys (or would that be bad practice; I told you I sucked at this?).

Supplier Table
-------------
*SupplierID
Supplier
(ItemID)

Item Table
----------
*ItemID
Item
(SupplierID)
(UnitID)

Unit Table
----------
*UnitID
Unit
(ItemID)

SupplierItemandCost Table
-------------------------
*SupplierItemID
SupplierItemCode
SupplierItemCost
(ItemID)
(SupplierID)

View 2 Replies View Related

Simple Lookups In Table

Dec 4, 2006

Simple questions I hope.

1.
What I'd like to do is do a lookup based on 2 fields in my table.
I'm tracking inventory for the company I am currently working with.
I have a computer table with 3 relevant fields:
compID, areaId, locationId

computer table
compID = PK for this table
areaId = FK from area table
locationId = FK from location table

area table
areaId

location table
locationId
areaId

There is a distinct relationship between area + location.
I want a lookup for the locationID, based on the area they have selected.
Thanks for any help on this one. On to the next question related to this.

2. As mentioned above I have an area table controlling the general departments (for lack of a better word). When an area is selected in the table, they have the ability to do a lookup for the relevant locations (implemented in the combo box).
I'd like the user to be able to add a new location item in the computer table, and have that value be added into the location table with the corresponding areaId as well. Am I looking for cascade update or something of this sort?
Thanks anyone. This is my first post here, I'm quite new to microsoft access as a database tool.

View 1 Replies View Related

Table Structure Of A Simple List

Jul 13, 2006

I haven't built a relational DB in years and I realise I'm pretty damn rusty at it. Any help would be appreciated.

Basically I've compiled a list of software applications our offices use, and I'm compiling which PCs have which software installed. Ideally I'd end up building a form with checkboxes for common software apps and text fields holding serial numbers or login IDs where applicable. But I'm stumped already on just the table layout. I currently have:

tblSoftware:
SoftID (Autonumber) - Primary Key and identifier for individual software apps
Software (Text) - Software title
Description (Text) - Any additional information (Licence, etc)

tblComputers:
NetID (text) - The Network Identification of the PC, also acts as Primary Key
User (text) - Name of user assigned to the machine

and having a One to Many relationship between NetID and SoftID. This should be a simple DB but I've been separated from all my manuals and the net is proving more useful for specific fault finding.

How can I set up the tables and relationships so that a query can be used to update the software for each computer?

View 3 Replies View Related

Help Please On Table Design For Simple Inventory DB

Feb 12, 2007

Hi, I was asked to help create a very simple Access DB for a small restaurant to help them keep track of inventory.

The only things that the owner wants to keep track of are:
1.Item Catalog
2.Date and Quantity of Item IN or OUT
3.Current # of Items

My problem here is that there are 5 Item categories, each with a relatively lost list of Items under each one.
1.Drinks
2.Ice Cream
3.Kitchen Supplies
4.Grocery
5.Etc.

Will it be advisable for me to create an Item Catalog TABLE for each of the category instead of piling all the Items into just one Item Catalog TABLE?

The Designs I’m currently considering are:

A.)

TABLE SET 1 : Catalog of Items
Fields (ID, Name)
•TABLE Drinks
•TABLE Ice Cream
•TABLE Kitchen Supplies
•TABLE Grocery
•TABLE ETC
---------------------------------------------------
TABLE SET 2 : Movement IN/OUT of Items
Fields (Movement ID, Name, Date, IN-Amount, OUT-Amount)
•TABLE Drinks
•TABLE Ice Cream
•TABLE Kitchen Supplies
•TABLE Grocery
•TABLE ETC

B.)
TABLE 1: Catalog of Items
Fields (ID, Category, Name)

TABLE 2: Movement IN/OUT of Items
Fields (Movement ID, Name, Category, Date, IN-Amount, OUT-Amount)

I’m really more inclined to choice A because I just re-learned Access last month and will be creating my first serious database just now. I don’t want to choose a more complex table structure (choice B) that might cause problems with the DB later (as it will really be used to keep track of real-life inventory and might screw up their operations if it goes haywire). Is Design A viable? Or flawed?

Another question I have is with the structure of the Movement – IN/OUT table. Is this workable? My thought is just to let the user enter something like:

Drink [date] [IN] [OUT]
Coke [date] [2] [0]
Coke [date] [0] [1]

In the database, then compute current Quantity of the Item using a query (Sum[IN] – Sum[OUT]) . Will this work? I’m a bit apprehensive with this because I’m thinking I should have another TABLE that will store the actual inventory Quantity instead of the Quantity just being computed through query. But doing another TABLE for Quantity does complicate stuff a lot since it will have to determine which Quantity value gets added to or subtracted from based on many criteria. Will it be OK just to use the query to compute Quantity left?

Thank you so much for any help on this!

View 14 Replies View Related

List Box Not Populating Table When Set To Simple

May 15, 2006

created a main form called "frmPatientDemographics" that contains

txtFirstName (Text Box)
txtLastName (Text Box)
txtDOB (Text Box)
txtCountry (Text Box)
cboGender (Combo Box)

it's record source is a table called "tblPatientDemo" that are populated based
on the information place in the above text boxes

Here is my problem

I have a subform called "frmPatientLanguageSub"

Source Object......... frmPatientLanguge
link Child Fields..... PatientID
link Child Fields..... PatientID

When list box Multi Select is set to "Simple" and I add a new patient and click on
multiple selections in the list box it does not populate the "tblPatientLanguge"

But if I change the Multi Select is set to "None" it populates the "tblPatientLanguge"
for that patient

Any Ideas why access populates the "tblPatientLanguge" if I set the Multi Select property to "None" but not for "Simple"?
If so How do I make access poplulate when the table when muliple selections are selected in the list box?

View 7 Replies View Related

Table Design For Simple CONTACTS Database

Aug 20, 2005

Hello All,
At work we have a large and messy Contacts list so I decided to set one up using a database.
At present I have 3 tables:-
Companies (custID,companyname,address,etc)
People (nameID,firstname,middlename,lastname,custID)
Phones (phoneID,phonetype,areacode,number,?????)

My problem is this, Some of the phone nos belong to the individuals
and some belong to the company. If a person is replaced at a company I need to reasign the company phone nos to the new person whilst retaining the individuals and their personal phone nos. If a company is deleted I need to delete only the company phone nos. and if a person moves within the company I want the company nos to reasign to the new replacement but keep the personal nos of the individual. Now I see its going to be more complex than I thought.

Can anyone help me with the table layout and links. (nb this is only a simple database relating a person to a company without using departments etc.)
Its main use is to provide phone nos names and addresses quickly.
Many Thanks
Peter

View 4 Replies View Related

Display Total Table Entries? Simple...

Jul 5, 2006

Hey guys.
Seems simple enough, I just can't figure out how to do it. How can I display the total number of table entries in a textbox on a form? thanks!

View 1 Replies View Related

Simple Like Query

May 11, 2005

Hi there,
I have a really simple question.
I have a field in a table that contains comments (text format). I want to run a query where I can get only the records that contain the word "high" in the comments. There may be some typos so I would like records returned that also contain something like "high". High is not the only word in the comments.

Does this make sense?

Thanks,
Row

View 4 Replies View Related

Simple Query..

Jul 26, 2005

Hi guys, wondering if you could help me, I can't seem to get this right..

I have two tables in question:

Items: StyleTypes:
------ ------------
ItemID ItemType
ItemType StyleType
Style
Lots of Attributes

ItemType is a list of items (Table, Desk, Bookshelf, Chair)
Style is a type of style for the item (Circular table, Radial Desk)

I want to create the Items.Style field based upon a query of the StyleTypes. So if a user types "Desk" in Items.ItemType then the Items.Style field will be limited to only those Styles which match that selected item within the StyleTypes table.

Think thats clear enough, but my attempts have all failed, hope you guys can give me a push in the right direction, thank you!

View 4 Replies View Related

Probably A Simple Query....

Apr 5, 2006

Hi all,

I have very little knowledge of databases and even less about writing queries.

The problem:
I have a database that lists cities (such as Paris, London, New York etc) as rows and employment status (Full-time, Part-time, Unemployed etc) as columns.
I need to go through each city and record which employment status is greatest (for each city) and present it in a new column. I can do this manually but I�m sure a query can be created to automate the process.

Hope that all made sense. Can anyone suggest ideas on where to start?

Many thanks,

Carl

View 1 Replies View Related

Simple Query..need Help If Possible

May 6, 2006

hi
have just designed a rather simple music database for my dad to use at home as he has large collection.
have a table with the heading"artist" and another one with "album" ...my problem is that on the table there maybe 1 or more instances with the same artist but with different albums...when i run a simple query for ..say artists starting with the letter"a" i may get about 10 cases of the same artist appearing in my query when all i want is one...(depending on if i may put in say one artist like abba having 10 albums etc)...i am entering each artist with the album each time so i may enter the artist in say 10 times....depending on how many albums etc

if you understand all that...i would be grateful if someone could help me with a simple query
alan

View 7 Replies View Related

Simple Query

Jul 4, 2006

I have a query that returns the uncompleted jobs from a single table ( its a calls database)
all i need is query that returns the amount of uncompleted jobs onto the switchboard I.E whatshername has X amount of uncompleted jobs , i have done a search and most of the solutions are very complicated for what i want to do .....anyone any ideas ?? pls

View 2 Replies View Related

Simple Query, For You - Not Me

Jul 6, 2006

Thanks in advance to anyone who helps.

SQL and Access are both new to me. I have purchased some books (which are still in the mail), and have gone through this forum (which seems to be a little more advanced for me at this moment).

I have a database that contains Business_Unit (Store Numbers), Post_Date (Last Date of Inventory), and other misc. stuff. Anyways, My goal is to pull the latest date of inventory for each individual store before the last ninety days. What I have so far:

SELECT DISTINCT informix_shrink_head.business_unit, informix_shrink_head.counting_event_id, informix_shrink_head.post_date, informix_shrink_head.delta_cost, informix_shrink_head.delta_count, informix_shrink_head.count_reason_cd, pcw_loc_master.closed
FROM informix_shrink_head, pcw_loc_master
WHERE (((informix_shrink_head.post_date)<=Date()-90) AND ((pcw_loc_master.closed) Is Null))
ORDER BY informix_shrink_head.business_unit;

However, I get the same store multiple times and for multiple post dates.
I do not even know if this can be done in one simple swoop, or if I need to create another table and re-query that? Any assistance or comments would be much appreciated.

Thanks,
A Gator

View 1 Replies View Related

Simple Query

Sep 11, 2006

Hi guys. I have been away a while and boy am I rusty.

I would like to show how many available licenses there are. Here is what I have:
Software (Office 2003)
license purchased (8)
license used (2)

I added an expression along the lines of:
Available: Sum([tblSoftware]![Number of licences purchased]-[tblPC-LicenceRelation]![Number of licences])

This gave 14 and not the desired result of 6!!!
Simple I am sure!! :o
Phil.

View 4 Replies View Related

Please Help - Simple Query

Sep 26, 2006

Hi Guys,

I have a database that collects information on products when engineers return them, what I need to do is when they have submitted the form, email it to 3 people so that the paperwork can be raised.

I understand that I will need to create a query to display just the last record and then email it.

So the idea is when a record is created and the engineer clicks submit the last record then gets emailed to the three people.

Please be gental with me im still quite new to all this.

How do i create a query that just displays the last record?:confused:

Thanks in advance.

Rich

View 1 Replies View Related

Simple Query Help

Nov 21, 2006

I built an update query that adds two strings of text together with a _ inbetween.

eg

field1 = Hello
Field2 = Jamie

after update= Hello_Jamie

This works fine but when i attempt to run the code in SQL on an after update instance i can't get it to compile

The SQL straight from the working Update query is:

UPDATE tblPID SET tblPID.PIDPIDNoRev = (tblPID.PIDno)+"_"+(tblPID.PIDRev);

I have tried quite a few different ways and believe it to be a probelm with the speech marks or underscore

DoCmd.RunSQL "UPDATE [tblPID] SET [tblPID].PIDPIDNoRev = (([tblPID].PIDno)+"_"+([tblPID].PIDRev));"

anyone got any ideas? i'm sure it's pretty simple

Thanks, Jamie

View 2 Replies View Related

Simple Query Help

Dec 29, 2006

HI,

I am sending ZIP file in attach with files in it. I would like some help to change the 'Q_test' query so that I can get the result shown in the excel file.

Regards,
Elio

View 1 Replies View Related

Simple Query Help

Jan 4, 2007

Hi,

I think i am being a bit thick, all I want to do is create a report of projects without products....

I have a table of Projects (customer name, details etc...)
I have a table of Products (product name, config, details etc....)

Every project will eventually have one or more products allocated to it...

How can i query the database for:

'select all projects created without any assigned products... yet'

I'm having a post xmas block!!!

Thanks

View 1 Replies View Related

Simple Query

Mar 9, 2007

Hi there..

I need a simple query...

I want all the records to appear which have some text in that particular field. So if there is no text in that field, it won't come up. What would I need to write under that field in the query?

View 5 Replies View Related

Simple Query

Nov 16, 2007

Hi,

I am trying to run a query to retrieve all rows in all tables using a row called Project Status. I am however not able to retrieve any data what so ever with the wizard and by using the limited sql I know. I have attached a copy of my tables if it helps.

The relationships are as follows: Release to PCR - One to many, Sponsors to PCR - One to many. The column i want to filter on is Project_status located in the PCR table.

Does anyone have any ideas how i can do this plz?

Thanks in advance for any help!

Ket

View 1 Replies View Related