Hi,

I have created an Asset Database which has the following tables.

tlkpLocation (lookup table to select location while entering Asset Details)
tlkpCompany (lookup table to select company while entering Asset Details)
tblCashRegister (Asset)
tblDesktop (Asset)
tblMonitor (Asset)

Now I want things to appear by Location (the location is defined by Location+Company)
for example, when I select Company “KFC” and location “Kings Park” it should show me the Desktop computer, Monitor, & Cash Register that are in that particular location.
How do I go about creating a query that lists all assets in one location?

Anyone has done something similar before?
Thanks!
-Sam

Attached is the database (Access 2007)

Hi Sam

Can you explain first of all our process?

Are you trying to record information about all of the IT Equipment for a Specific Company in a particular Location?

If you are then your structure needs a lot of work

Your tblRegisters has lots of Fields which are set as Lookups - This is a NO NO in access

Read this Thread for guidance

Come back with any questions

Mike

Hi

The attached Dbase is in response to your previous thread about Linked Forms

The Form that opens has the Links set correctly between the main Form and the SubForm

See the relationship window and look at how the two tables are linked.

You will have to apply this method to any other Main/SubForms you wish to create

Come back with any questions

Mike

1) About lookups in my db. The lookup tables are ONLY AVAILABLE AT THE FORM LEVEL BUT NOT AT THE TABLE LEVEL. Which means user can select the data using drop down menu on the form, but the actual data get saved in the table (not reference to lookup table) - open the main tables (tbl register, tbl desktops) after entering the data to see what i mean.

2) You have created a child form on the main form. i can do it easily , but what i am really after is the ability to edit the subform/child form in a popup form by clicking EDIT or NEW button. - (please see the picture attached to my first post)

Any ideas?

Thank you for answers so far!
-Sam

Hi

I am afraid you are not listening to what I am trying to tell you.

Your table registers for example has the following fields:-

Company
RegisterType
Location
Condition
Status

ALL of these fields you have set as Text - datatype

ALL of these fields you have set as Lookups and when you selct an item from the ComboBox on the Form what you are storing is the actual Name of the Item selected in the ComboBox. THIS IS WRONG

What you should be storing is the ID NR for the Value that you have selected from the ComboBox

ALL of these fields should be named as follows:

CompanyID
RegisterTypeID
LocationID
ConditionID
StatusID

ALL of these fields should be set as Number Data Types

Then at the Form level you create ComboBoxes and specify that you want to store the id in the respective field from the underlying table.

Hope this helps?

You will find that most on UA will NOT help unless you take notice of what is being advised.

Regards

Mike

ARE YOU WRITING THIS REFERENCE TO MY FOLLOWING QUESTION?

I only want assets to appear by LOCATION & COMPANY names.
the lookups & other stuff is not so relavant (i think).

what sort of query would you run to get information from different tables that is related to only onle location & company (say Mc Donald is the company & Kings Park is where one branch is located - how would you get Assets that are at Kings Park - Mc Donamd)?

Cheers,
-S

--------------
Hi,

I have created an Asset Database which has the following tables.

tlkpLocation (lookup table to select location while entering Asset Details)
tlkpCompany (lookup table to select company while entering Asset Details)
tblCashRegister (Asset)
tblDesktop (Asset)
tblMonitor (Asset)

Now I want things to appear by Location (the location is defined by Location+Company)
for example, when I select Company “KFC” and location “Kings Park” it should show me the Desktop computer, Monitor, & Cash Register that are in that particular location.
How do I go about creating a query that lists all assets in one location?

Anyone has done something similar before?
Thanks!
-Sam

Hi

Seeing as Company and Location are in the SAME table then this will work

SELECT tblRegisters.RegisterID, tblRegisters.Company, tblRegisters.Location
FROM tblRegisters
WHERE (((tblRegisters.Company)=[Enter Company Name]) AND ((tblRegisters.Location)=[Enter Location]));


Hope that helps?

PS your statement about the Lookups doesn' t matter well belive me they do

Mike

Edited by: mike60smart on Tue Jun 24 9:09:01 EDT 2008.

Learned it the hard way Mike!!
As you said, the lookups do matter.

I had spent so much time designing the db that i didn't really want to believe that i had done it wrong.
I am making fundamental changes now.