Queries returns wrong values

[Miszczuk]

Hi All,

I am pretty new in using access, but I need to to able to combine several excel sheets into one - and the information are huge.

My problems is that when I use below setup without the "Notes" tabel, they query works fine, and returns the amount of spares and time used by a single WBS ID. (Customer). But when I add another tabel and link it together, it fucks up. Its returns a shitload of lines with the same data - it looks like its copying its self, over and over again...

Anobody who can help me fix it?

Thanks in advance,

Jesper

 

[jdraw]

Is this part of a bigger database and you are only showing a small piece?
I don't see a table for Customer???
Can you tell us in plain English WHAT the whole database is about?
Database is different than spreadsheet - you really have to get your table structures set up to meet your business needs and then relate those tables based on your business' rules.

If you are just starting with Access, you will experience syntax issues with field names containing embedded spaces and/or special characters #-. The underscore (_) and alphanumeric are acceptable to Access. However, there are some reserved words that will cause issues unless field names are enclosed in [ and ].

 

[bob fitz]

I could be wrong but I think you need to delete the join on WBS ID from WBS ID_mapping to Notes and create a join from WBS ID......sparea & time to Notes
BYW you should not use & in the name of an object (table,Query,field etc) and would be better NOT to have spaces in names, so for example, instead of "First Name" use either "FirstName" or "First_Name"

 

[Miszczuk]

Is this part of a bigger database and you are only showing a small piece?

I don't see a table for Customer???
Can you tell us in plain English WHAT the whole database is about?
Database is different than spreadsheet - you really have to get your table structures set up to meet your business needs and then relate those tables based on your business' rules.

If you are just starting with Access, you will experience syntax issues with field names containing embedded spaces and/or special characters #-. The underscore (_) and alphanumeric are acceptable to Access. However, there are some...that will cause issues unless field names are enclosed in [ and ].

Hi ,

I want to build a bigger database where I can add different sources of data, will mainly be excel sheets, and build up one queries that contains all info.

Right now, I am trying to align two sources "Spares/time" and "Notes". Each of them contains WBS_ID, which are unique. WBS_ID are also the customer number. Notes could have customer entries, which are not in the spares/time table - and the other way around. That is why I created the mapping table, which contains all WBS_ID from all sources.

Each table also contains multiple lines with the same WBS ID. The reason is that a customer can request a sparepart or use technician time several times in a month - meaning all those lines will be in the table. It would maybe be easier if it could be summed by WBS_ID and date, this would lower the amount of entries in the table.

When I join these two tables together using the WBS_ID it is like a loop starts copying the amount over and over again in different month – why I am doing wrong I don’t know, but I have aligned all words and removed all spaces.

I really hope you are able to help me.

Thanks in advance,

Jesper

 

[jdraw]

Can you give readers an overview of your "business" in plain English.
Here's a sample from a tutorial on database design.

Sample Narrative from RogersAccessLibrary

Narrative
ZYX Laboratories requires an employee tracking database. They want to track information about employees, the employee's job history, and their certifications. Employee information includes first name, middle initial, last name, social security number, address, city, state, zip, home phone, cell phone, email address. Job history would include job title, job description, pay grade, pay range, salary, and date of promotion. For certifications, they want certification type and date achieved.
An employee can have multiple jobs over time, (ie, Analyst, Sr. Analyst, QA Administrator). Employees can also earn certifications necessary for their job.

This is the sort of description that can be used to create tables and relationships.

Also, WBS typically means Work Breakdown structure which is a technique for identifying Tasks, subtasks, subsubtasks etc.

Each record in a database table requires a unique identifier (called a Primary Key). A field whose vale distinguishes one record from every other record in the table.

If you are using WBSId as the "link field", and one WBSId can occur multiple times in the table, then that field alone is not going to work as a Primary Key.

The tutorial I mentioned will help, but you have to work through it. There is a solution provided in the tutorial.
Good luck.