Help with this SQL...

[hunterfan48]

SELECT tblInventory.ID, buy.[Transaction ID], sell.[Transaction ID]
FROM ((tblInventory INNER JOIN tblTransactionID ON tblInventory.ID = tblTransactionID.[Card ID]) INNER JOIN buy ON tblTransactionID.ID = buy.[Transaction ID]) INNER JOIN sell ON tblTransactionID.ID = sell.[Transaction ID];

That above folks is what I'm working with right now on a query. I would like this query to return ALL VALUES FROM "tblInventory.ID"

Can someone please help me with this language and show me where in here I can tell it to return all values for "tblIventory.ID" and keep everything else the same??

Thank you!

 

[John Big Booty]

One way to do what you want, is to create an Access Query that returns the data you are after. Once you are happy that it is doing what you want, select SQL View from the View menu. you can then copy and paste the SQL as required.

 

[Jeanette Cunningham]

1. Create a separate query to use as a table in the next query.
Call this query 'qryInventoryPart1'.
Here is the sql:
SELECT tblInventory.ID, tblTransactionID.[Card ID]
FROM tblInventory LEFT JOIN tblTransactionID ON tblInventory.ID = tblTransactionID.[Card ID];

2. Create the next query:
SELECT qryInventoryPart1.ID, qryInventoryPart1.[Card ID]
FROM (qryInventoryPart1 INNER JOIN buy ON qryInventoryPart1.ID = buy.ID) INNER JOIN sell ON qryInventoryPart1.ID = sell.ID;

 

[hunterfan48]

Hey thanks for the tips.

Jeanette,
After posting that SQL from instruction #2 in your post above, it wants me to put in a parameter for buy and sell ID??

 

[hunterfan48]

Ok....I'm getting real close I think.

Here's my latest SQL for my most recent query.

SELECT qryInventorypart1.tblInventory.ID, [buy/sell].buy.[Transaction ID], [buy/sell].sell.[Transaction ID]
FROM qryInventorypart1 LEFT JOIN [buy/sell] ON qryInventorypart1.tblInventory.ID = [buy/sell].[Card ID];


It has everything I want in there. Now, I just want it to be able to pull the correct transaction number into the appropriate buy/sell column.

 

[Jeanette Cunningham]

I can see from the sql just posted that there is something called buy/sell.
Now a name with a backslash in it is just not acceptable to ms access.
When you are naming table and queries, stick to using the 26 letters of the alphabet, the digits 0 to 9 and the underscore character. Any other characters will lead to problems.
From the original post it looked like you had a table called buy with a primary key called buyID, also a table called sell with a primary key called sellID.
About the parameter message - Access can't find a field called buyID, neither can it find a field called sellID - hence the parameter message.

The syntax where you have:
SELECT qryInventorypart1.tblInventory.ID, [buy/sell].buy.[Transaction ID], [buy/sell].sell.[Transaction ID]
- is not acceptable to access because [buy/sell].buy.[Transaction ID] doesn’t make sense to access.

Please post back with the actual names of the tables and the fields that you are using for this query.

 

[nanscombe]

After a bit of head scratching the nearest scenario I can get, from your original SQL, is that you probably have two tables (tblnventory and tblTransactionID) and two queries based on tblTransactionID (buy - for purchases and sell - for sales).

Were you trying to return all values from tblInventory and any matching values from buy and sell?

If so, give this a try.

SELECT tblInventory.ID, buy.[Transaction ID], sell.[Transaction ID]
FROM ((tblInventory
LEFT JOIN tblTransactionID ON tblInventory.ID = tblTransactionID.[Card ID])
LEFT JOIN buy ON tblTransactionID.ID = buy.[Transaction ID])
LEFT JOIN sell ON tblTransactionID.ID = sell.[Transaction ID];

 

[hunterfan48]

YES YES YES! I believe this is almost perfect. Tell me how we fix this last bug though...

For ANY ONE Card ID, there can only be one BUY TRANSACTION and one SELL TRANSACTION. A Card ID doesn't necessarily have to have a value for either one, but a single card can have at the MOST one transaction ID for buy and one transaction ID for sell.

When I run this SQL for my query though, it has the Card Inventory listed more than one time. It looks like this.

Card ID Buy ID Sell ID
34 44
34 44
34 44
34 44
34 44

See, the unique card ID (34) has a sell ID of 44. I don't need it repeated four more times. I think the reason it does this is because transaction 44 has 5 cards marked as sold within transaction 44 thus having 34 repeated 5 times.


Any tips to clear up this last issue??

Thanks for the suggestion...I feel like this is getting close to what I want!

After a bit of head scratching the nearest scenario I can get, from your original SQL, is that you probably have two tables (tblnventory and tblTransactionID) and two queries based on tblTransactionID (buy - for purchases and sell - for sales).

Were you trying to return all values from tblInventory and any matching values from buy and sell?

If so, give this a try.

SELECT tblInventory.ID, buy.[Transaction ID], sell.[Transaction ID]
FROM ((tblInventory
LEFT JOIN tblTransactionID ON tblInventory.ID = tblTransactionID.[Card ID])
LEFT JOIN buy ON tblTransactionID.ID = buy.[Transaction ID])
LEFT JOIN sell ON tblTransactionID.ID = sell.[Transaction ID];

 

[hunterfan48]

It appears I figured it out now, but how I did it confuses me.

I went into my query design view and clicked the 'Totals' button in my top menu.

It appears to have fixed the problem of the CARD ID's being duplicated.

However, one thing I did notice is when a specific card ID has both a value in the buy.ID transaction column as well as the sell.ID transaction column, it duplicates the card ID as shown below.

Card ID Buy ID Sell ID
3010 105
3010 45

 

[hunterfan48]

anybody have any suggestions for my last post???

Here are some screen shots showing what's going on.

 

[vbaInet]

One for Buy and one for Sell. You shouldn't have split them into two tables. Those tables aren't properly normalized at the moment. Perhaps you have a reason why you have it the way it is?

 

[hunterfan48]

Buy and Sell were just two queries I created based off of my table Transactions. I did this that way I could enter in a specific transaction number for the buy transaction and one for the sell transaction.

I was advised when I started my database to only have one table for transactions and have a field to mark it as a buy or sell instead of creating two tables (one for buying and one for selling)

I'm starting to think that I should have two tables instead of one table for my transactions.

 

[vbaInet]

What you were advised is correct. You only need a marker indicating whether it's buy or a sell transaction in one transaction table. Not two.

You will need to combine those two queries into one so that Buy and Sell sit side-by-side to each other before joining it to your query.

 

[hunterfan48]

Ok...alright so take my buy query and my sell query and put those together into one??

 

[vbaInet]

Yep! Paste the two queries here let me have a look. In the meantime you should have a go too.

 

[hunterfan48]

I got the query to return the exact same records as card inventory table! I think this is what I need. Thank you sir!

Btw...how would I use this query to create a form where I'll be able to enter a new card and also enter it's proper transaction value for both buy and sell?

 

[vbaInet]

No problemo!

You've got quite a few joins already so if you base your form on that entire query it will not be updateable. You will need to think of breaking it down into subforms.

 

[hunterfan48]

LOL bummer...this was the whole reason for trying to come up with this query so I could you use these two fields as part of a form.

Hmmm...

 

[nanscombe]

How about this ...

One Form based on tblInventory.

Two Subforms:
One based on Sell query, in BeforeInsert event set flag to sell
One based on Buy query, in BeforeInsert event set flag to buy

Link Forms to Subforms using card ID.



ETA: No, hang on. I've got this one wrong. Have to rethink....

 

[nanscombe]

I get the feeling that life would be easier if you split the Card out of the Transaction table into it's own table.

tblInventory -> tblCard -> tblTransactionID

You could then have a Form for the Card details and a Subform for the transactions which could have a dropdown for Buy/Sell.

It won't be side by side but it would be one above the other.

Have a look at the attached and see what you think.