All Encompasing View

[mhaccessnov]

Hello,

I have an access database with 2 tables and one relationship. I am trying to create a view that will give me the "latest and greatest" but also with specific details pertaining to a column called location.

So I have Date Assigned, Employee, and Descriptive Name of Equipment... but I am trying to incorporate "Location" or "Worksite" as well but everytime I do using "Max" it takes the higher occuring string value and replaces the other values with this one... That is not what I am trying to do..

Here is my Query and I am trying to make this work ...

SELECT tblEquipment.BarcodeId, Max(tblEquipment.[Descriptive Name]) AS [Equipment Description], Max(tblEquipment.[Date Assigned]) AS [Assigned Date], Max(tblEmployee.Employee) AS Employee
FROM tblEquipment LEFT JOIN tblEmployee ON tblEquipment.EmployeeID = tblEmployee.EmployeeID
GROUP BY BarcodeID;

I just want the latest and greatest view of equipment and who has it...

:banghead::banghead::banghead:

Thanks in advance!!

 

[plog]

You'll probably need a subquery or two. When you try to match aggregate results (Count, Sum, Max, etc) from one table with unique records in another, it doesn't work, unless you do those 2 steps individually. So what you do is you first get your aggregate results, then link that to the other data source.

If you need specific help, post some sample data. Include relevant table and field names and then post what should be returned. Use this format for posting data:

TableNameHere
Field1Name, Field2Name, Field3Name
12/11/2009, David, 72
11/9/2008, Sally, 14
1/1/2007, David, 55

 

[mhaccessnov]

Thank you very much!!

tblEquipment

BarcodeID, Equipment Desc, Assigned Date, Employee, "Worksite"
0023, Backhoe, 9/24/2013, Shane, Lumber Yard
0048, Set of Shovels, 9/25/2013, Larry, Office Location
0412, Excavator, 8/22/2013, Bob, Lumber Yard
0023, Backhoe, 9/27/2013, Charlie, Office Location

Note** The Worksite data is usually only 3 or 4 choices...** (Quotes around the worksite is because this is where my issue started coming in)


tblEmployee

EmployeeID, Employee
1, Charlie
2, Larry
3, Bob
4, Shane

I have never done a subquery but I would assume from things I read in the past it has to do with "Criteria"??

Thanks again for any help!!

 

[plog]

That's half of what I asked for. Now based on the data you posted, what do you want your query to ultimately return?

Also, is BarCodeID the unique primary key of that table? Why does it have leading 0s? Is it a text field?

 

[mhaccessnov]

With the original query .. I am trying to get that data but the most current information at any given moment... Including the worksite.. However, when I included the worksite into the query I am getting an aggregate error and when I do not get that I end up with the most commonly assigned worksite becoming the main worksite for any given employee.

No there is an EquipmentID the BarcodeID is just a text field and the actual barcodes have a leading zero.

 

[plog]

The EquipmentID helps a little, but I'm not looking for an explanation of your data, I want actual sample data. Think of it this way--I'm google maps and you want to know how to drive from point A to point B. You can't put in A and then talk about some highway you think you need to travel down and maybe a city you pass through--you put in B so it knows the origin and destination. I'm the same way, I want what data you are starting with and what data you hope to end up with.

Repost your sample data, include EquipmentID values and then post what data the query should return if it is fed the sample data you post.

 

[mhaccessnov]

So,

EquipmentID, BarcodeId, --> Many other columns
1, 0001
2, 0002

I do not need EquipmentID to be shown. I want to view BarcodeID, Description, Employee, Date Assigned, and the Worksite.

I am misunderstanding what you are asking for.

I have a large table that consists of many columns of data... I want to pull only specific data from this table and link it up with an Employee.

tblEmployee

EmployeeID, Employee
1, Charlie
2, Larry
3, Bob
4, Shane

The hangup I am having is including the "Location" field. When I do I have the original issue I spoke of.


Table1 is tblEquipment (22 Fields and 432 Rows of Data)
Primary Key - EquipmentID
BarcodeID
[Descriptive Name]
[Date Assigned]
Location
Foreign Key - EmployeeID



Table2 is tblEmployee (1 Field and 20 Rows of Data)
Primary Key - EmployeeID
Employee


These are the fields I am trying to get to come back with the most current data. Using the EmployeeID to join...

Hope this helps...

 

[plog]

Again, I don't need explanations, I need data. I want some sample data of what you are starting with and then what data the query should output based on that sample data.

Say you owned a bakery and wanted to know the total products purchased by customer and the number of days they purchased stuff on. I tell you to post starting sample data (A) and what the query should show (B) based on that sample data. Hopefully, I would get this:

tblPurchases
CustID, SaleDate, Item, Qty
14, 1/1/2008, Croissant, 3
14, 1/1/2008, Danish, 2
17, 3/3/2010, Danish, 1
14, 2/3/2009, Croissant, 3
17, 3/7/2011, Scone, 1

Expected result:

CustID, UniquePurchaseDays, Products
14, 2, 8
17, 2, 2


See how the expected results I posted is achievable from the starting data? I need that for your database.
I need the origin (A) and the destination (B) to build you a map between the two.

 

[WayneRyan]

M,

Make two queries.

qryMaxes:

Select BarcodeID, Max(AssignedDate)
From   tblEquipment
Group By BarcodeID

Then use that query to get the specifics for that date:

Select A.BarcodeID, A.AssignedDate,
       B.Employee,  b.WorkSite, b.EquipmentDesc
From   qryMaxes As A Inner Join tblEquipment as B on
          a.BarcodeID = b.BarcodeID And
          a.AssignegDate = b.AssignedDate
Order BY A.BarcodeID

Wayne

 

[mhaccessnov]

Sorry, Plog. I understand what you are saying now. I went into this knowing I could not show any of my data. I guess that is where my hangup was.

Ok, I have like 22 fields ... I am not trying to put all of that here... I was stating with this

Table1 is tblEquipment (22 Fields and 432 Rows of Data)
Primary Key - EquipmentID
BarcodeID
[Descriptive Name]
[Date Assigned]
Location
Foreign Key - EmployeeID

That these are the fields I am trying to get. However, I include location and I have issues with seeing the latest "ACCURATE" information.

So, data... here you go...

tblEquipment

EquipmentID, BarcodeID, Descriptive Name, Yada yada, yada yada, Yada yada, Yada yada, Yada yada, Date Assigned, Yada yada, yada yada, yada yada, yada yada, Worksite, yada yada, EmployeeID, yada yada


tblEmployee

Employee (based off of the EmployeeID)


Expected View from the two tables above...

EquipmentID, BarcodeID, Descriptive Name, Date Assigned, Worksite, EmployeeID (linked to tblEmployee --> Employee)


If you cannot help I understand. Thanks!

Again, I don't need explanations, I need data. I want some sample data of what you are starting with and then what data the query should output based on that sample data.

Say you owned a bakery and wanted to know the total products purchased by customer and the number of days they purchased stuff on. I tell you to post starting sample data (A) and what the query should show (B) based on that sample data. Hopefully, I would get this:

tblPurchases
CustID, SaleDate, Item, Qty
14, 1/1/2008, Croissant, 3
14, 1/1/2008, Danish, 2
17, 3/3/2010, Danish, 1
14, 2/3/2009, Croissant, 3
17, 3/7/2011, Scone, 1

Expected result:

CustID, UniquePurchaseDays, Products
14, 2, 8
17, 2, 2


See how the expected results I posted is achievable from the starting data? I need that for your database.
I need the origin (A) and the destination (B) to build you a map between the two.

 

[mhaccessnov]

Hi Wayne,

I am trying to include Employee as well... However, that is a separate table with its own primary key. I think I could take what you have here and try to make it work with "EmployeeID/Employee"....


THanks,

M,

Make two queries.

qryMaxes:

Select BarcodeID, Max(AssignedDate)
From   tblEquipment
Group By BarcodeID

Then use that query to get the specifics for that date:

Select A.BarcodeID, A.AssignedDate,
       B.Employee,  b.WorkSite, b.EquipmentDesc
From   qryMaxes As A Inner Join tblEquipment as B on
          a.BarcodeID = b.BarcodeID And
          a.AssignegDate = b.AssignedDate
Order BY A.BarcodeID

Wayne