Query showing multiple record

[maabbas]

Hi guys, need little help, please see the attached MS access database, what I am trying is to make a database, based on our production schedule, we add orders on daily basis and plant scheduler check the order status and ending inventory, and schedule the product. if you check the attached database in crosstab, I am unable to link the tblproduction to crosstab query properly,

please help me out.

 

[plog]

You can only link similar datatypes. Why is ProductID a text field in tblOrder? That's the issue, its numeric in tblProduction

 

[maabbas]

You can only link similar datatypes. Why is ProductID a text field in tblOrder? That's the issue, its numeric in tblProduction

They both are texts, please check.
ProductID is text in tblProduct
ProductionCode is text in tblProduction.

 

[plog]

There is no ProductID field in tblProduct. I am also confused as to what query we are talking about. What query do I need to look in? What is the issue?

 

[maabbas]

There is no ProductID field in tblProduct. I am also confused as to what query we are talking about. What query do I need to look in? What is the issue?

In the crosstab query, there is only one record in tblProduction,

ProductionID 9
ProductionDate 08/18/2014
ProductionCode 236
ProductionQty 50

In the query its showing in all the product, I want only to show once in the product 236

 

[plog]

Then you need to link your 2 data sources. They aren't joined in any manner.

 

[maabbas]

Then you need to link your 2 data sources. They aren't joined in any manner.

How I am gonna link

 

[plog]

I don't know, its your database. Usually datasources "relate" to each other--a primary key in one table is a foreign key in another. Maybe its time to take a step back, throw away database jargon and explain in english what you are trying to accomplish.

 

[maabbas]

I don't know, its your database. Usually datasources "relate" to each other--a primary key in one table is a foreign key in another. Maybe its time to take a step back, throw away database jargon and explain in english what you are trying to accomplish.

Here you in plain English but its my second language. we have a Excel Spread Sheet, where in Row 1 has dates from January 1 to December 31, Column A has product code, any order we received we enter in the date where is the product.
the problem with the Excel sheet many time the user (which are different location) deleted some data, also the file size is getting big.

I want this attached spread sheet in Access,

 

[plog]

Your English is fine, "plain english" is just a saying, it means in simple words.

You cannot recreate that spreadsheet in Access, you can only have (I think) 255 columns in a query. If you were to use all the dates of the year, 365, the query wouldn't run.

For your issue of multiple records, its because you aren't linking your 2 data sources. There's no line between them.

 

[maabbas]

Your English is fine, "plain english" is just a saying, it means in simple words.

You cannot recreate that spreadsheet in Access, you can only have (I think) 255 columns in a query. If you were to use all the dates of the year, 365, the query wouldn't run.

For your issue of multiple records, its because you aren't linking your 2 data sources. There's no line between them.

I do not want to show all the dates, only those date which has order. I will work it out and let you know.

 

[maabbas]

I do not want to show all the dates, only those date which has order. I will work it out and let you know.

Here is the relation, will you please let me know, I still have same issue

 

[plog]

That relationship is even worse. You still have an unjoined data source (Query1), and the sources that are joined are cirucularly joined. There should be but one path between the data sources, it shouldn't form a loop. This should be fixed removing the link between tblOrder and tblProduction.

How come there is no link between Query1 and the other 3 data sources?

Honestly, it may be best just to give me sample data and what you expect as the result, instead of posting attempts.

 

[maabbas]

That relationship is even worse. You still have an unjoined data source (Query1), and the sources that are joined are cirucularly joined. There should be but one path between the data sources, it shouldn't form a loop. This should be fixed removing the link between tblOrder and tblProduction.

How come there is no link between Query1 and the other 3 data sources?

Honestly, it may be best just to give me sample data and what you expect as the result, instead of posting attempts.

Here is my attachment, what I want is query 1, only show one Production data, if there is an order for that product or not

 

[plog]

Here's what I need. Using just the data in the tables (tblProduction, tblOrder, tblProduct) show me what data you want ultimately returned. I already have the tables and the data in them, so show me explicitly what fields and data the query should produce. Use this format:

Field1Name, Field2Name, Field3NAme, ....
value1, value2, value3, ...

 

[maabbas]

Here's what I need. Using just the data in the tables (tblProduction, tblOrder, tblProduct) show me what data you want ultimately returned. I already have the tables and the data in them, so show me explicitly what fields and data the query should produce. Use this format:

Field1Name, Field2Name, Field3NAme, ....
value1, value2, value3, ...

Order ID, Order Date, Product ID, BegInventory, OrderQty, ProductionQty, Ending Inventory((begInvetory+ProductionQty)-(orderQty))

 

[plog]

Nope. I need actual values. Not calculations, tell me what the actual values are based on the data existing in the table you posted.

 

[maabbas]

Nope. I need actual values. Not calculations, tell me what the actual values are based on the data existing in the table you posted.

Alright the actual value.

Order Date, ProductID, OrderQty, ProductionDate, ProductionQty

 

[plog]

Nope again. Those are field names, which is half of what I need. I also need the actual values of what the query should produce based on the data in your tables.

For example , these are the field names and values in tblProduction:

ProductionID, ProductionDate, ProductionCode, ProductionQty
9, 8/18/2014, 236, 100
10, 8/19/2014, 10101, 200

You need to manually provide me with the values your query will produce.

 

[maabbas]

Nope again. Those are field names, which is half of what I need. I also need the actual values of what the query should produce based on the data in your tables.

For example , these are the field names and values in tblProduction:

ProductionID, ProductionDate, ProductionCode, ProductionQty
9, 8/18/2014, 236, 100
10, 8/19/2014, 10101, 200

You need to manually provide me with the values your query will produce.

OrderID, OrderDate, ProductID, OrderQty,ProductionDate, Production Code ProductionQty
20 8/18/2014 236 1000 08/18/2014 236 100
21 8/17/2014 236 2000 - -


if there is no production its display 0 and is there any other product e.g. 10101 is produce only display the production date, production Code and Qty.