Queries & Report Help

[homeschoolforreal]

Hi! I have been going back and forth with my instructor about my last Microsoft Access project in which I can't seem to create proper queries. I have included the instruction material below and attached my Access file. If anyone could help me, or give me step by step instructions on how to properly create them I would greatly appreiciate it.

Create a simple query on the Inventory table. Thequery should include data entries only for items that havebeen repaired. Include the following fields:1. Item Number2. Item Name3. Item Category4. Item Manufacturer5. Purchase Date6. Warranty Type7. Warranty Length8. Repair9. Repair Date10. CommentsThe data within the query should be sorted alphabetically byItem Category. Save the query as Repaired Items.

Based on the data contained in the query Repaired Items,create a report with the Report Wizard using all the fields fromthe query. No grouping levels are required in this report. Sortin ascending order by Item Name (Figure 5). The reportshould have the following format:• Columnar layout• Portrait orientationTitle the report Repaired Items List.

Create a multiple-table query on the Inventory and Merchantstables. To create this query, you must establish a relationshipbetween the two tables (Figure 6). The common field betweenthe tables is Merchant ID. Both fields should be set to text intheir respective tables. Now you may begin to build yourquery. Include the following fields from the Inventory table:1. Item Name2. Item Category3. Item Manufacturer4. Purchase Date

Include the following fields from the Merchants table:



1. Merchant Name
2. Merchant Address
3. Merchant City
4. Merchant State
5. Merchant ZIP

The data within the query should be sorted alphabetically byMerchant Name. Set the criteria cell for the Item Categoryfield to search for “Appliances.” Save the query as ApplianceStore Purchases.Based on the data contained in the Appliance StorePurchases query, create a report with the Report Wizardusing the following fields from the query:

1. Item Name
2. Item Category
3. Item Manufacturer
4. Purchase Date
5. Merchant Name
6. Merchant Address
7. Merchant City
8. Merchant State
9. Merchant ZIP

View the data by Merchants. No grouping levels are requiredin this report. Sort detail records in ascending order byPurchase Date. Format the report as follows:• Stepped• Landscape orientationTitle the report Appliance Store Purchases List

[Homegrownandy]

What is the problem you are having when you try doing this?

[rpeare]

For this step:

Create a simple query on the Inventory table.

You just need to know yes/no fields (repair) are stored as a -1 for yes (field has a checkmark in it) 0 for no (field has no checkmark) and may also be null (have nothing). In your query you are looking for a -1 value to indicate the item has been repaired.

For the second of your two requests I don't see anything super complex there so, like homegrownandy I don't know what problem you're encountering creating it.

[homeschoolforreal]

Whenever I create a query it still shows all the information, not just the information I want it to show if that makes sense.

[Homegrownandy]

https://www.youtube.com/watch?v=O7I_QpF4BT8 might help.

I'm assuming you are putting all the fields in the query

[Homegrownandy]

" for items that havebeen repaired" there must be something in your database to differentiate between items that are repaired or not. What is that? how do you know if its been repaired?

[rpeare]

Here's the fine line, this is an assignment so people don't want to do the work for you or the learning doesn't occur. I am going to suggest you generally look at how to use criteria in a query. Criteria let you pull subsets of data out of your tables. So if I were going to search for a last name of 'smith' from a table I would have

"smith"

in the criteria

if I were searching for a specific date I would have

#1/1/2017#

in the criteria

if I were trying find a specific number in a number field I would have

1000

in the criteria

[ssanfu]

Some reading for you. Important!!

Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp

Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/Autonumbers


Some naming suggestions:
Use only letters and numbers (exception is the underscore) for object names.
Do not use spaces, punctuation or special characters in object names.
Do not use look up FIELDS, multi-value fields or calculated fields in tables.
Do not begin object names with a number.
------------------
Bad field name: "RA#" (has the hash mark")
Good/better field names: "RANum", "RA_Num", "RA_Number"

Bad field name: "Bagged Y/N" (has space and slash")
Good/better field names: "BaggedYN", "Bagged_YN", "Bagged_Y_N"
================================================== =================


Now that that is out of the way, lets look at your tables.
First, the Merchant table.

Why all of the Memo type fields? They should ALL be TEXT types. You can set the Field Size to 255.
Note that the PK field (Merchant ID - I would name it MerchantID_PK) is an Autonumber type. (good)



Now the Inventory table:

Again with the memo fields! IMO, the only field that should be a Memo type is the Comments field.

Look at the "Merchant ID" field. It is a MEMO type??? Really??
What is the data type in the Merchant table? Remember? Hint - AUTONUMBER!


Then you have an instruction:

Create a multiple-table query on the Inventory and Merchants tables. To create this query, you must establish a relationship between the two tables (Figure 6). The common field between the tables is Merchant ID. Both fields should be set to text in their respective tables.

This is so wrong (IMO). IF Merchants.MerchantID_PK is an Autonumber, then to set a relationship, the data types must be the same. Since an autonumber is a Long Integer, then Inventory.MerchantID_FK MUST be a Long Integer.

You cannot create a query between Merchants.MerchantID_PK and Inventory.MerchantID_FK and set a relationship between autonumber and memo data types.
And setting both fields to TEXT is a very bad mistake (IMO). Read the two links again......



Fix the tables, then you should be able to create the queries.......

Good luck