Talk to me like Im 6

[MinglePingle]

Hi-ya Folks!

The Enterprise is broken up by Geographic Regions (30+ unique regions IDs) and each building has its own unique number within that Geographic region.

Issue: certain vendors service only some of the buildings within a region.
Solution: Create query to identify only the vendor contact information for that building within that region and display Building Address that those vendors service.

Region ID = # Value
Building ID = # Value
Vendor ID = # Value

I created 3 tables in excel and uploaded them to Access with the following unique IDs:

Table: Building Addresses = Region ID_Building ID
Table: Vendor Contacts = Region ID_Building ID_Vendor ID
Table: Vendor Invoices = Region ID_Building ID_Vendor ID

I used the query wizard to join the following:

Building Addresses-->Vendor Invoices via Region ID_Building ID
Vendor Invoices-->Vendor Contacts via Region ID_Building ID_Vendor ID

Once establishing the above relationships, I have the fields chosen to return the Vendor contact information and the Building Address (fields already created via the excel tables I uploaded). However, when I enter the criteria (Region ID and Building ID [both are separate fields that I created an additional column joining them both via excel]) nothing is returned.

Can anyone please explain what I'm missing?

 

[arnelgp]

you only have building ID on the table, where is the address field?

 

[plog]

Please complete the Relationship Tool within Access. Bring in all your tables, expand them to show their fields, link them appropriately and then post a screenshot of it back here.

 

[MinglePingle]

I've attached the design view of the query I'm trying to run.

Building Address Table has all pertinent Enterprise information regarding each building including its manager. This table has a field for each part of the address and I (in excel) joined all the columns together to create the 'Total Address' field.

In my query

DSV_ID = Division Store Vendor ID (uniquely created for this exercise)
DS_ID = Division Store ID (uniquely created for this exercise)

Does this help clarify? Also, thank you for your help!

 

[arnelgp]

regions
    region_id
    other_description

building addresses
   region_id
   building_id
   other_description

vendor contacts:
   vendor_id
   region_id
   other_description

invoice header
   invoice_number
   invoice_date
   vendor_id
   general_info

invoice_detail
   invoice_id
   building_id
   other_detail_info

 

[MinglePingle]

I believe I also need a form (?). Reasoning:

I'd like the user to type in the Region ID / Division ID as well as the Building #/ Store # and yield the following results:

Vendor Emails and Building/Store said Vendor services

 

[arnelgp]

right click the line between "scrubbed contact" and "scrubbed invoice".
on its property set "include all records from "scrubbed contact" and only matching record from scrubbed" invoice.

right click the line between "scrubbed invoice" and "store".
do the same as the above step (include all records from "scrubbed invoice and …).

 

[MinglePingle]

right click the line between "scrubbed contact" and "scrubbed invoice".
on its property set "include all records from "scrubbed contact" and only matching record from scrubbed" invoice.

right click the line between "scrubbed invoice" and "store".
do the same as the above step (include all records from "scrubbed invoice and …).

I did that and now little arrows point to the left of each table. However, When I enter in the criteria (Div and Store), nothing is returned.

 

[arnelgp]

reversed the arrows.

 

[plog]

You need to step back from queries and forms and focus on your tables. Read up on normalization (https://en.wikipedia.org/wiki/Database_normalization).

You have not set up your tables properly. Although you didn't complete the Relationship Tool, I can see major issues with your tables.

1. Numerated field names. When you start prefixing field names with numbers (First, Second, Third, etc.) its time to put all that related data in a new table. Instead of 4 columns to accomodate 4 contacts you would have 4 rows of data in a seperate table set up just for that data.

2. Duplicated data. Data should be stored in just one table. In the small peak you've given into your tables I see 2 tables with State fields, 2 tables with Store Number fields, and every table has DS_ID field. Those are all wrong. Those fields should only exist in 1 table.

Again, read up on normalization; apply it to your tables; set up the Relationship Tool; and post back an image of it to this forum and we can help you set your tables up correctly. Most likely Arnelgp's table structure he posted earlier will put you on the right track for your specific needs.

 

[Pat Hartman]

Welcome aboard.

This sounds like a class assignment. We are happy to help you to understand Access and give you implementation suggestions but you will still need to implement our suggestions.