Trouble extracting data from multpile tables (poorly designed relationships?)

[jerickson.mn]

Hi All, I've just started in a position where I will have to do loads of reporting, and I'm finding my Access skills sub par at best.



Here is my dillema... The common theme between the tables (shown in the example) is the EEID. I would like some information from the "Macro Usage" table (EEID, Date, Macro Name) and some info from the production table (Hours in Production, Volume, VPH, and VPH to goal)

I can not for get access to generate a query combining information. Any ideas?

[Access_Blaster]

jerickson.mn,

What you have here is poorly designed tables. So what follows is a poor relationships, querys, reports and so on.

1. The field name Date is a Access reserved word so is Time.
2. You Don't have a primary key. (EEID) this might serve as one, not sure.
3. You have Date in two tables.
4. Spaces in your field names will make coding harder.."Hours in Production" should be HoursInProduction.
What I am try to say here is your putting the cart before the horse..if nothing else get your tables RIGHT. Life will be a whole lot easier for you.

[ConneXionLost]

In your relationship diagram you have both tables and have joined them by "EEID" and "Date"; however, in the "Production" table the "Date" datatype is text, while in the "Macro Usage" table the data type for "Date" is Number. This is the reason your query won't run.

Here are some other pointers:

- Don't name your fields with reserved words like "Date" or "Time".
- Use Primary keys in your tables.
- It's usually and advantage to match fields with the data types they hold. e.g. Date fields with Date/Time, or numerical fields with Number.

Cheers,

[Access_Blaster]

Double barrel!!

[ConneXionLost]

Yup, but you were quicker on the draw!

[jerickson.mn]

Thank you all. Good Information. Sadly, I believe I'm tied to all the table formats, as the data is imported through csv files. This data all originally comes from other sources, not related to access. I am very new, and appreciate all of your input.

Primary Key's - I'm struggling to set one in the production table, as the there are multiple entries for each EEID.

AccessBlaster -- can I change Access reserved fields like "Date" to "DateUsed" or can I not use the word "Date" at all.

AccessBlaster & ConneXion Lost, if I post a revised layout, would it be possible for you to review them?

Thank you very much for your help.

[Access_Blaster]

jerickson.mn,

My short answer would be to Export the table out to the desktop in Excel format I use 97-2000. Then break the relationships empty the table and retool. Then I would open the Excel file on your desktop and match the columns to the new table fields and import. That is a short answer the reality may be different. Also you would have to match the .CSV column heads.

[ConneXionLost]

If the csv format is always the same, then you can take Access_Blaster's route to clean the data, or you may even be able to do the same thing within Access. In either case, it isn't necessary to return the results to the same table. You could treat your existing tables as "csvdatadump" tables, then import and convert to some proper "production" & "Macro_Usage" tables.

Help with reviewing database layouts? Well yes, it's why we're here!